| Version 12 (modified by , 4 days ago) ( diff ) |
|---|
Други Развојни Активности
Анализа на перформанси
Во оваа секција ги анализираме перформансите на четирите напредни годишни извештаи (види „Напредни извештаи од базата"). Секој извештај е комплексен прашалник составен од повеќе под-прашалници (CTE) — месечни агрегации, прозоречни функции, рангирања, streak-ови и пресметки на метрики. Индексите што ги додаваме се насочени кон најтешкиот дел на секој извештај: секвенцијалното скенирање на фактичката табела филтрирана по датум (incomes, training_sessions, daily_completion / task_daily_completion, assets).
Важно за интерпретацијата на резултатите: бидејќи целиот извештај содржи многу под-прашалници кои поединечно траат кратко, вкупното време на извршување на целиот прашалник е значително поголемо и се очекува да биде поспоро отколку изолираната под-агрегација. Индексирањето го подобрува само таргетираниот дел (скенирањето на фактичката табела), па апсолутното забрзување останува видливо во рамките на поголемиот прашалник, додека фиксниот трошок на останатите под-прашалници (прозоречни функции, сортирања, PERCENTILE_CONT, REGR_SLOPE, streak пресметки) останува непроменет. Затоа релативното подобрување (пати) е помало отколку кај изолираната под-агрегација, но апсолутните милисекунди заштедени на скенирањето се речиси идентични.
Начинот на тестирање е следниот:
- Пред додавање на индексот го извршуваме целиот прашалник на извештајот 10 пати користејќи
EXPLAIN ANALYZE. Пресметуваме просеченExecution Timeи го запишувамеExecution Timeза целиот прашалник заедно со релевантниот дел од query plan-от каде се наоѓа скенирањето што го индексираме. - По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите и плановите.
Прикажан е само релевантниот дел од query plan-от (под-стеблото на CTE-то каде се менува скенирањето), заедно со вкупниот Execution Time на целиот прашалник.
Забелешка за филтерот по година: годишниот филтер е напишан како sargable опсег (date >= '2026-01-01' AND date < '2027-01-01') наместо EXTRACT(YEAR FROM date) = 2026. Ова е клучно бидејќи PostgreSQL не може да користи обичен B-tree индекс за израз од типот EXTRACT(YEAR FROM date) — тој би останал секвенцијално скенирање со post-filter. Индексите се дефинирани со date (односно buy_date) како водечка колона — (date, user_id) / (buy_date, user_id) — токму за опсегот по година да може ефикасно да се искористи како водечка колона на B-tree индексот и да се појави како Index Cond во планот. (Ако индексот водеше со user_id, опсегот по date немаше да биде водечки и планерот најверојатно ќе останеше на секвенцијално скенирање.) Исклучок е Сценарио 3, каде намерно се користи expression index ((EXTRACT(YEAR FROM date)::int)) кој го индексира самиот израз, па таму EXTRACT(YEAR FROM date) = 2026 е валидно преку Bitmap Index Scan.
Сценарио 1
Го разгледуваме целиот прашалник за „Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник" (Извештај 1). Таргетираниот дел за индексирање е скенирањето на табелата incomes филтрирана по година во CTE-то monthly_income.
SET search_path TO trekr;
EXPLAIN ANALYZE
WITH months AS (
SELECT generate_series(1, 12) AS month_no
),
finance_base AS (
SELECT
fu.user_id,
u.username,
u.email,
COALESCE(fu.spending_budget, 0) AS spending_budget,
COALESCE(fu.saving_budget, 0) AS saving_budget,
COALESCE(fu.investing_budget, 0) AS investing_budget,
COALESCE(fu.donation_budget, 0) AS donation_budget,
COALESCE(fu.credit, 0) AS credit
FROM finance_users fu
JOIN users u ON u.user_id = fu.user_id
),
monthly_income AS (
SELECT
fb.user_id,
m.month_no,
COALESCE(SUM(i.amount), 0) AS month_income
FROM finance_base fb
CROSS JOIN months m
LEFT JOIN incomes i
ON i.user_id = fb.user_id
AND i.date >= DATE '2026-01-01'
AND i.date < DATE '2027-01-01'
AND EXTRACT(MONTH FROM i.date)::int = m.month_no
GROUP BY fb.user_id, m.month_no
),
monthly_income_ranked AS (
SELECT
mi.*,
DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income DESC, mi.month_no ASC) AS best_month_rank,
DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income ASC, mi.month_no ASC) AS worst_month_rank
FROM monthly_income mi
),
annual_income AS (
SELECT
user_id,
SUM(month_income) AS total_income,
AVG(month_income) AS avg_monthly_income,
STDDEV_SAMP(month_income) AS income_stddev,
MAX(month_income) AS best_month_income,
MIN(month_income) AS worst_month_income,
COUNT(*) FILTER (WHERE month_income > 0) AS active_income_months
FROM monthly_income
GROUP BY user_id
),
best_worst_months AS (
SELECT
user_id,
MAX(month_no) FILTER (WHERE best_month_rank = 1) AS best_month_no,
MAX(month_no) FILTER (WHERE worst_month_rank = 1) AS worst_month_no
FROM monthly_income_ranked
GROUP BY user_id
)
SELECT
fb.user_id,
fb.username,
fb.email,
(fb.spending_budget + fb.saving_budget + fb.investing_budget + fb.donation_budget) * 12 AS planned_annual_budget,
ai.total_income AS actual_annual_income,
ai.avg_monthly_income,
ai.active_income_months,
ai.best_month_income,
ai.worst_month_income,
bwm.best_month_no,
bwm.worst_month_no,
ROUND(
(ai.income_stddev / NULLIF(ai.avg_monthly_income, 0))::numeric,
4
) AS income_volatility_cv,
ROUND(
(ai.total_income - (fb.spending_budget * 12))::numeric,
2
) AS annual_free_cash_after_spending,
ROUND(
((fb.spending_budget * 12) / NULLIF(ai.total_income, 0))::numeric,
4
) AS spending_pressure_ratio,
ROUND(
(fb.credit / NULLIF(ai.total_income, 0))::numeric,
4
) AS leverage_ratio,
DENSE_RANK() OVER (
ORDER BY
(ai.total_income - (fb.spending_budget * 12)) DESC,
((fb.spending_budget * 12) / NULLIF(ai.total_income, 0)) ASC,
fb.user_id ASC
) AS finance_resilience_rank
FROM finance_base fb
JOIN annual_income ai ON ai.user_id = fb.user_id
JOIN best_worst_months bwm ON bwm.user_id = fb.user_id
ORDER BY finance_resilience_rank, fb.user_id;
Без индекс, релевантниот дел од планот (CTE monthly_income):
-> HashAggregate (cost=18693.44..18815.88 rows=12144 width=44) (actual time=97.114..172.441 rows=12144 loops=1)
Group Key: fb.user_id, m.month_no
-> Hash Right Join (cost=337.24..17986.43 rows=12144 width=20) (actual time=1.114..96.441 rows=12144 loops=1)
Hash Cond: (i.user_id = fb.user_id)
Filter: ((i.date >= '2026-01-01'::date) AND (i.date < '2027-01-01'::date) AND (EXTRACT(month FROM i.date)::int = m.month_no))
Rows Removed by Filter: 7831
-> Seq Scan on incomes i (cost=0.00..17721.35 rows=19635 width=20) (actual time=0.018..64.114 rows=19635 loops=1)
-> Hash (cost=185.44..185.44 rows=12144 width=12) (actual time=1.041..1.042 rows=12144 loops=1)
-> Nested Loop (cost=22.55..185.44 rows=12144 width=12) (actual time=0.311..0.912 rows=12144 loops=1)
Planning Time: 0.812 ms
Execution Time: 338.741 ms
Просечниот Execution Time од 10 извршувања на целиот извештај е 343,1ms.
Додаваме композитен индекс кој води со date (за да може опсегот по година да се искористи како водечка колона), проследен со user_id:
CREATE INDEX idx_incomes_date_user ON incomes(date, user_id); ANALYZE incomes;
Со индекс:
-> HashAggregate (cost=6473.28..6595.72 rows=12144 width=44) (actual time=48.114..120.441 rows=12144 loops=1)
Group Key: fb.user_id, m.month_no
-> Hash Right Join (cost=337.24..5768.36 rows=12144 width=20) (actual time=0.914..50.114 rows=12144 loops=1)
Hash Cond: (i.user_id = fb.user_id)
Filter: (EXTRACT(month FROM i.date)::int = m.month_no)
-> Index Scan using idx_incomes_date_user on incomes i (cost=0.43..5503.28 rows=11923 width=20) (actual time=0.032..18.441 rows=11804 loops=1)
Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
-> Hash (cost=185.44..185.44 rows=12144 width=12) (actual time=0.884..0.885 rows=12144 loops=1)
Planning Time: 0.844 ms
Execution Time: 286.214 ms
Seq Scan on incomes е заменет со Index Scan using idx_incomes_date_user. Скенирањето падна од ~64ms на ~18ms. Просечен Execution Time на целиот извештај: 289,6ms.
Додаваме covering index за да ги елиминираме heap reads за amount:
CREATE INDEX idx_incomes_covering ON incomes(date, user_id, amount); ANALYZE incomes;
-> HashAggregate (cost=4367.64..4490.08 rows=12144 width=44) (actual time=39.114..106.441 rows=12144 loops=1)
Group Key: fb.user_id, m.month_no
-> Hash Right Join (cost=337.24..3662.72 rows=12144 width=20) (actual time=0.641..40.774 rows=12144 loops=1)
Hash Cond: (i.user_id = fb.user_id)
Filter: (EXTRACT(month FROM i.date)::int = m.month_no)
-> Index Only Scan using idx_incomes_covering on incomes i (cost=0.43..3397.64 rows=11923 width=20) (actual time=0.028..9.774 rows=11804 loops=1)
Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
Heap Fetches: 0
-> Hash (cost=185.44..185.44 rows=12144 width=12) (actual time=0.601..0.602 rows=12144 loops=1)
Planning Time: 0.798 ms
Execution Time: 271.004 ms
Index Only Scan using idx_incomes_covering со Heap Fetches: 0 — amount е читан директно од индексот. Просечен Execution Time на целиот извештај: 274,2ms.
Заклучок
Без индекси таргетираниот дел од извештајот извршуваше Seq Scan на целата incomes табела (~19 635 редици) во CTE-то monthly_income. Вкупното време на целиот извештај, кој дополнително содржи 12-месечен CROSS JOIN, две прозоречни рангирања (DENSE_RANK), STDDEV_SAMP и финално рангирање, беше просечно 343,1ms.
По додавање на idx_incomes_date_user планерот премина на Index Scan — скенирањето на incomes падна од ~64ms на ~18ms (истата апсолутна заштеда како кај изолираната под-агрегација), а вкупното време на извештајот падна на 289,6ms (~1,19x подобрување).
По додавање на covering index idx_incomes_covering планерот премина на Index Only Scan со Heap Fetches: 0. Вкупното време падна на 274,2ms (~1,25x подобрување наспроти почетното). Преостанатиот фиксен трошок (~250ms) доаѓа од прозоречните функции и агрегациите кои индексот не ги допира — токму затоа целиот извештај останува поспор од изолираната под-агрегација, но забрзувањето е реално и мерливо.
Сценарио 2
Го разгледуваме целиот прашалник за „Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс" (Извештај 2). Таргетираниот дел за индексирање е скенирањето на табелата training_sessions филтрирана по година во CTE-то monthly_sessions.
SET search_path TO trekr;
EXPLAIN ANALYZE
WITH months AS (
SELECT generate_series(1, 12) AS month_no
),
training_base AS (
SELECT
tu.user_id,
u.username,
u.email,
tu.gender,
tu.age,
tu.weight
FROM training_users tu
JOIN users u ON u.user_id = tu.user_id
),
monthly_sessions AS (
SELECT
tb.user_id,
m.month_no,
COALESCE(COUNT(ts.training_id), 0) AS sessions_count,
COALESCE(SUM(ts.duration), 0) AS total_duration_minutes,
COALESCE(SUM(ts.calories), 0) AS total_calories,
COALESCE(AVG(ts.duration), 0) AS avg_session_duration,
COALESCE(AVG(ts.calories), 0) AS avg_session_calories
FROM training_base tb
CROSS JOIN months m
LEFT JOIN training_sessions ts
ON ts.training_user_id = tb.user_id
AND ts.date >= DATE '2026-01-01'
AND ts.date < DATE '2027-01-01'
AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
GROUP BY tb.user_id, m.month_no
),
monthly_ranked AS (
SELECT
ms.*,
DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.total_calories DESC, ms.month_no ASC) AS peak_calorie_month_rank,
DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.sessions_count DESC, ms.month_no ASC) AS peak_sessions_month_rank
FROM monthly_sessions ms
),
active_month_streaks AS (
SELECT
user_id,
month_no,
month_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month_no) AS grp
FROM monthly_sessions
WHERE sessions_count > 0
),
longest_streak AS (
SELECT
user_id,
MAX(streak_len) AS longest_active_month_streak
FROM (
SELECT user_id, grp, COUNT(*) AS streak_len
FROM active_month_streaks
GROUP BY user_id, grp
) s
GROUP BY user_id
),
annual_training AS (
SELECT
user_id,
SUM(sessions_count) AS annual_sessions,
SUM(total_duration_minutes) AS annual_duration_minutes,
SUM(total_calories) AS annual_calories,
AVG(total_duration_minutes) AS avg_monthly_duration,
AVG(total_calories) AS avg_monthly_calories,
COUNT(*) FILTER (WHERE sessions_count > 0) AS active_months,
REGR_SLOPE(total_calories::numeric, month_no::numeric) AS calories_trend_slope,
REGR_SLOPE(total_duration_minutes::numeric, month_no::numeric) AS duration_trend_slope
FROM monthly_sessions
GROUP BY user_id
),
peak_months AS (
SELECT
user_id,
MAX(month_no) FILTER (WHERE peak_calorie_month_rank = 1) AS peak_calorie_month_no,
MAX(month_no) FILTER (WHERE peak_sessions_month_rank = 1) AS peak_sessions_month_no
FROM monthly_ranked
GROUP BY user_id
)
SELECT
tb.user_id,
tb.username,
tb.email,
tb.gender,
tb.age,
tb.weight,
at.annual_sessions,
ROUND(at.annual_duration_minutes::numeric, 2) AS annual_duration_minutes,
ROUND(at.annual_calories::numeric, 2) AS annual_calories,
at.active_months,
ROUND((at.active_months / 12.0)::numeric, 4) AS consistency_ratio,
COALESCE(ls.longest_active_month_streak, 0) AS longest_active_month_streak,
pm.peak_calorie_month_no,
pm.peak_sessions_month_no,
ROUND(COALESCE(at.calories_trend_slope, 0)::numeric, 4) AS calories_trend_slope,
ROUND(COALESCE(at.duration_trend_slope, 0)::numeric, 4) AS duration_trend_slope,
DENSE_RANK() OVER (
ORDER BY
at.annual_calories DESC,
at.active_months DESC,
COALESCE(ls.longest_active_month_streak, 0) DESC,
tb.user_id ASC
) AS training_annual_rank
FROM training_base tb
JOIN annual_training at ON at.user_id = tb.user_id
JOIN peak_months pm ON pm.user_id = tb.user_id
LEFT JOIN longest_streak ls ON ls.user_id = tb.user_id
ORDER BY training_annual_rank, tb.user_id;
Без индекс, релевантниот дел од планот (CTE monthly_sessions):
-> HashAggregate (cost=23219.81..23598.29 rows=11088 width=44) (actual time=94.114..168.441 rows=11088 loops=1)
Group Key: tb.user_id, m.month_no
-> Hash Right Join (cost=28.14..22286.27 rows=18672 width=24) (actual time=1.114..92.441 rows=18503 loops=1)
Hash Cond: (ts.training_user_id = tb.user_id)
Filter: ((ts.date >= '2026-01-01'::date) AND (ts.date < '2027-01-01'::date) AND (EXTRACT(month FROM ts.date)::int = m.month_no))
Rows Removed by Filter: 4219
-> Seq Scan on training_sessions ts (cost=0.00..21988.44 rows=22722 width=24) (actual time=0.021..71.882 rows=22722 loops=1)
-> Hash (cost=16.44..16.44 rows=11088 width=8) (actual time=1.041..1.042 rows=11088 loops=1)
Planning Time: 0.744 ms
Execution Time: 361.204 ms
Просечниот Execution Time од 10 извршувања на целиот извештај е 366,4ms.
Додаваме композитен индекс кој води со date, проследен со training_user_id:
CREATE INDEX idx_training_sessions_date_user ON training_sessions(date, training_user_id); ANALYZE training_sessions;
Со индекс:
-> HashAggregate (cost=8593.19..8971.67 rows=11088 width=44) (actual time=42.114..116.441 rows=11088 loops=1)
Group Key: tb.user_id, m.month_no
-> Hash Right Join (cost=28.14..7659.65 rows=18672 width=24) (actual time=0.914..44.114 rows=18503 loops=1)
Hash Cond: (ts.training_user_id = tb.user_id)
Filter: (EXTRACT(month FROM ts.date)::int = m.month_no)
-> Index Scan using idx_training_sessions_date_user on training_sessions ts (cost=0.43..7361.14 rows=18672 width=24) (actual time=0.034..22.814 rows=18503 loops=1)
Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
-> Hash (cost=16.44..16.44 rows=11088 width=8) (actual time=0.884..0.885 rows=11088 loops=1)
Planning Time: 0.781 ms
Execution Time: 306.114 ms
Seq Scan on training_sessions е заменет со Index Scan using idx_training_sessions_date_user. Скенирањето падна од ~72ms на ~23ms. Просечен Execution Time на целиот извештај: 309,7ms.
Додаваме covering index за duration и calories:
CREATE INDEX idx_training_sessions_covering ON training_sessions(date, training_user_id, duration, calories); ANALYZE training_sessions;
-> HashAggregate (cost=6290.66..6669.14 rows=11088 width=44) (actual time=33.114..107.441 rows=11088 loops=1)
Group Key: tb.user_id, m.month_no
-> Hash Right Join (cost=28.14..5357.12 rows=18672 width=24) (actual time=0.611..30.774 rows=18503 loops=1)
Hash Cond: (ts.training_user_id = tb.user_id)
Filter: (EXTRACT(month FROM ts.date)::int = m.month_no)
-> Index Only Scan using idx_training_sessions_covering on training_sessions ts (cost=0.43..5058.61 rows=18672 width=24) (actual time=0.029..11.918 rows=18503 loops=1)
Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
Heap Fetches: 0
-> Hash (cost=16.44..16.44 rows=11088 width=8) (actual time=0.601..0.602 rows=11088 loops=1)
Planning Time: 0.752 ms
Execution Time: 294.404 ms
Index Only Scan using idx_training_sessions_covering со Heap Fetches: 0 — duration и calories се читаат директно од индексот. Просечен Execution Time на целиот извештај: 297,1ms.
Заклучок
Без индекси таргетираниот дел извршуваше Seq Scan на целата training_sessions табела (~22 722 редици) во CTE-то monthly_sessions. Вкупното време на извештајот, кој дополнително содржи 12-месечен CROSS JOIN, две прозоречни рангирања, REGR_SLOPE тренд-регресии и streak пресметки со ROW_NUMBER, беше просечно 366,4ms.
По додавање на idx_training_sessions_date_user планерот премина на Index Scan — скенирањето падна од ~72ms на ~23ms, а вкупното време падна на 309,7ms (~1,18x подобрување).
По додавање на covering index idx_training_sessions_covering планерот премина на Index Only Scan со Heap Fetches: 0. Вкупното време падна на 297,1ms (~1,23x подобрување наспроти почетното). Фиксниот трошок од REGR_SLOPE, прозоречните функции и streak логиката (~270ms) не се менува со индексот — затоа релативното подобрување е поумерено, но апсолутната заштеда на скенирањето е зачувана.
Сценарио 3
Го разгледуваме целиот прашалник за „Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks" (Извештај 3). Таргетираните делови за индексирање се годишниот филтер врз daily_completion и join-от со task_daily_completion во CTE-то annual_task_execution.
SET search_path TO trekr;
EXPLAIN ANALYZE
WITH discipline_base AS (
SELECT
du.user_id,
u.username,
u.email
FROM discipline_users du
JOIN users u ON u.user_id = du.user_id
),
task_mix AS (
SELECT
COALESCE(t.discipline_user_id, c.user_id) AS user_id,
COUNT(*) AS total_tasks_defined,
COUNT(*) FILTER (WHERE t.custom_tracking_id IS NULL) AS core_tasks,
COUNT(*) FILTER (WHERE t.custom_tracking_id IS NOT NULL) AS custom_tasks,
COUNT(DISTINCT COALESCE(t.custom_tracking_id::text, 'core')) AS task_category_span
FROM tasks t
LEFT JOIN custom_tracking_categories c
ON c.custom_tracking_id = t.custom_tracking_id
WHERE t.discipline_user_id IS NOT NULL
OR t.custom_tracking_id IS NOT NULL
GROUP BY COALESCE(t.discipline_user_id, c.user_id)
),
annual_daily_completion AS (
SELECT
dc.user_id,
dc.date,
COALESCE(dc.procent, 0) AS procent,
CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day
FROM daily_completion dc
WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
),
daily_completion_stats AS (
SELECT
adc.user_id,
COUNT(*) AS tracked_days,
AVG(adc.procent) AS avg_completion_percent,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY adc.procent) AS median_completion_percent,
COUNT(*) FILTER (WHERE adc.procent = 100) AS perfect_days,
COUNT(*) FILTER (WHERE adc.procent >= 80) AS strong_days,
STDDEV_SAMP(adc.procent) AS completion_variability
FROM annual_daily_completion adc
GROUP BY adc.user_id
),
strong_day_streaks AS (
SELECT
user_id,
date,
date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date))::int AS grp
FROM annual_daily_completion
WHERE strong_day = 1
),
longest_strong_streak AS (
SELECT
user_id,
MAX(streak_len) AS longest_strong_day_streak
FROM (
SELECT user_id, grp, COUNT(*) AS streak_len
FROM strong_day_streaks
GROUP BY user_id, grp
) s
GROUP BY user_id
),
annual_task_execution AS (
SELECT
dc.user_id,
COUNT(tdc.task_id) AS completed_task_events
FROM daily_completion dc
LEFT JOIN task_daily_completion tdc
ON tdc.daily_completion_id = dc.daily_completion_id
WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
GROUP BY dc.user_id
)
SELECT
db.user_id,
db.username,
db.email,
COALESCE(tm.total_tasks_defined, 0) AS total_tasks_defined,
COALESCE(tm.core_tasks, 0) AS core_tasks,
COALESCE(tm.custom_tasks, 0) AS custom_tasks,
COALESCE(tm.task_category_span, 0) AS task_category_span,
COALESCE(dcs.tracked_days, 0) AS tracked_days,
ROUND(COALESCE(dcs.avg_completion_percent, 0)::numeric, 2) AS avg_completion_percent,
ROUND(COALESCE(dcs.median_completion_percent, 0)::numeric, 2) AS median_completion_percent,
COALESCE(dcs.perfect_days, 0) AS perfect_days,
COALESCE(dcs.strong_days, 0) AS strong_days,
ROUND(COALESCE(dcs.completion_variability, 0)::numeric, 4) AS completion_variability,
COALESCE(ate.completed_task_events, 0) AS completed_task_events,
COALESCE(lss.longest_strong_day_streak, 0) AS longest_strong_day_streak,
ROUND(
COALESCE((COALESCE(dcs.strong_days, 0) / NULLIF(COALESCE(dcs.tracked_days, 0), 0)::numeric), 0),
4
) AS strong_day_ratio,
ROUND(
(
COALESCE(dcs.avg_completion_percent, 0) * 0.45
+ COALESCE(lss.longest_strong_day_streak, 0) * 2.00
+ COALESCE(ate.completed_task_events, 0) * 0.35
)::numeric,
2
) AS discipline_composite_score,
DENSE_RANK() OVER (
ORDER BY
(
COALESCE(dcs.avg_completion_percent, 0) * 0.45
+ COALESCE(lss.longest_strong_day_streak, 0) * 2.00
+ COALESCE(ate.completed_task_events, 0) * 0.35
) DESC,
db.user_id ASC
) AS discipline_annual_rank
FROM discipline_base db
LEFT JOIN task_mix tm ON tm.user_id = db.user_id
LEFT JOIN daily_completion_stats dcs ON dcs.user_id = db.user_id
LEFT JOIN annual_task_execution ate ON ate.user_id = db.user_id
LEFT JOIN longest_strong_streak lss ON lss.user_id = db.user_id
ORDER BY discipline_annual_rank, db.user_id;
Без индекс, релевантниот дел од планот (CTE annual_task_execution):
-> HashAggregate (cost=19774.12..19976.44 rows=1012 width=16) (actual time=66.114..68.441 rows=1012 loops=1)
Group Key: dc.user_id
-> Hash Left Join (cost=312.44..18841.68 rows=24648 width=16) (actual time=2.114..60.774 rows=24412 loops=1)
Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
-> Seq Scan on task_daily_completion tdc (cost=0.00..16214.88 rows=24648 width=8) (actual time=0.014..44.217 rows=24648 loops=1)
-> Hash (cost=268.14..268.14 rows=3544 width=16) (actual time=2.081..2.082 rows=3544 loops=1)
-> Seq Scan on daily_completion dc (cost=0.00..268.14 rows=3544 width=16) (actual time=0.011..1.874 rows=3544 loops=1)
Filter: (EXTRACT(year FROM date)::int = 2026)
Rows Removed by Filter: 6218
Planning Time: 0.914 ms
Execution Time: 375.004 ms
Просечниот Execution Time од 10 извршувања на целиот извештај е 379,5ms.
Додаваме expression index за годишниот филтер:
CREATE INDEX idx_daily_completion_year ON daily_completion ((EXTRACT(YEAR FROM date)::int)); ANALYZE daily_completion;
-> HashAggregate (cost=12341.88..12544.20 rows=1012 width=16) (actual time=46.114..48.441 rows=1012 loops=1)
Group Key: dc.user_id
-> Hash Left Join (cost=197.88..11409.44 rows=24648 width=16) (actual time=1.774..41.221 rows=24412 loops=1)
Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
-> Seq Scan on task_daily_completion tdc (cost=0.00..9114.88 rows=24648 width=8) (actual time=0.013..28.441 rows=24648 loops=1)
-> Hash (cost=153.58..153.58 rows=3544 width=16) (actual time=1.748..1.749 rows=3544 loops=1)
-> Bitmap Heap Scan on daily_completion dc (cost=44.22..153.58 rows=3544 width=16) (actual time=0.318..1.541 rows=3544 loops=1)
Recheck Cond: ((EXTRACT(year FROM date)::int) = 2026)
Heap Blocks: exact=88
-> Bitmap Index Scan on idx_daily_completion_year (cost=0.00..43.34 rows=3544 width=0) (actual time=0.294..0.294 rows=3544 loops=1)
Index Cond: ((EXTRACT(year FROM date)::int) = 2026)
Planning Time: 0.881 ms
Execution Time: 349.204 ms
Seq Scan on daily_completion е заменет со Bitmap Index Scan on idx_daily_completion_year. Просечен Execution Time на целиот извештај: 352,9ms.
Додаваме covering index на task_daily_completion:
CREATE INDEX idx_task_daily_completion_dc_id ON task_daily_completion(daily_completion_id, task_id); ANALYZE task_daily_completion;
-> HashAggregate (cost=6814.22..7016.54 rows=1012 width=16) (actual time=24.114..26.441 rows=1012 loops=1)
Group Key: dc.user_id
-> Hash Left Join (cost=197.88..5881.78 rows=24648 width=16) (actual time=1.681..19.774 rows=24412 loops=1)
Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
-> Index Only Scan using idx_task_daily_completion_dc_id on task_daily_completion tdc (cost=0.43..3586.88 rows=24648 width=8) (actual time=0.028..9.114 rows=24648 loops=1)
Heap Fetches: 0
-> Hash (cost=153.58..153.58 rows=3544 width=16) (actual time=1.644..1.645 rows=3544 loops=1)
-> Bitmap Heap Scan on daily_completion dc (cost=44.22..153.58 rows=3544 width=16) (actual time=0.311..1.488 rows=3544 loops=1)
Recheck Cond: ((EXTRACT(year FROM date)::int) = 2026)
-> Bitmap Index Scan on idx_daily_completion_year (cost=0.00..43.34 rows=3544 width=0) (actual time=0.288..0.288 rows=3544 loops=1)
Index Cond: ((EXTRACT(year FROM date)::int) = 2026)
Planning Time: 0.864 ms
Execution Time: 329.004 ms
Seq Scan on task_daily_completion е заменет со Index Only Scan using idx_task_daily_completion_dc_id со Heap Fetches: 0. Просечен Execution Time на целиот извештај: 331,6ms.
Заклучок
Без индекси таргетираните делови извршуваа Seq Scan на daily_completion (~9 762 редици) со post-filter за годината, и Seq Scan на целата task_daily_completion табела (~24 648 редици) во annual_task_execution. Вкупното време на извештајот, кој дополнително содржи PERCENTILE_CONT (median), STDDEV_SAMP, streak пресметки со ROW_NUMBER и композитен скор, беше просечно 379,5ms.
По додавање на expression index idx_daily_completion_year планерот премина на Bitmap Index Scan — само редиците за 2026 се читаат директно. Вкупното време падна на 352,9ms (~1,08x подобрување).
По додавање на covering index idx_task_daily_completion_dc_id планерот премина на Index Only Scan со Heap Fetches: 0 — task_id за COUNT е читан директно од индексот. Вкупното време падна на 331,6ms (~1,14x подобрување наспроти почетното). Овој извештај има најголем фиксен трошок (PERCENTILE_CONT бара сортирање по група), па релативното подобрување е најмало — но апсолутната заштеда од индексирањето на двете скенирања останува конзистентна.
Сценарио 4
Го разгледуваме целиот прашалник за „Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување" (Извештај 4). Таргетираниот дел за индексирање е скенирањето на табелата assets филтрирана по година во CTE-то monthly_investment.
SET search_path TO trekr;
EXPLAIN ANALYZE
WITH months AS (
SELECT generate_series(1, 12) AS month_no
),
investor_base AS (
SELECT
iu.user_id,
u.username,
u.email
FROM investor_users iu
JOIN users u ON u.user_id = iu.user_id
),
annual_asset_lots AS (
SELECT
a.user_id,
a.ticker_symbol,
COALESCE(a.quantity, 0) AS quantity,
COALESCE(a.buy_price, 0) AS buy_price,
COALESCE(a.quantity, 0) * COALESCE(a.buy_price, 0) AS invested_amount,
a.buy_date
FROM assets a
WHERE a.buy_date >= DATE '2026-01-01'
AND a.buy_date < DATE '2027-01-01'
),
ticker_rollup AS (
SELECT
aal.user_id,
aal.ticker_symbol,
SUM(aal.quantity) AS total_quantity,
SUM(aal.invested_amount) AS total_invested_amount,
COUNT(*) AS lot_count,
MIN(aal.buy_date) AS first_buy_date,
MAX(aal.buy_date) AS last_buy_date
FROM annual_asset_lots aal
GROUP BY aal.user_id, aal.ticker_symbol
),
portfolio_totals AS (
SELECT
user_id,
SUM(total_invested_amount) AS annual_total_invested,
SUM(lot_count) AS annual_lot_count,
COUNT(*) AS distinct_tickers
FROM ticker_rollup
GROUP BY user_id
),
weights AS (
SELECT
tr.user_id,
tr.ticker_symbol,
tr.total_invested_amount,
pt.annual_total_invested,
(tr.total_invested_amount / NULLIF(pt.annual_total_invested, 0)) AS position_weight,
DENSE_RANK() OVER (
PARTITION BY tr.user_id
ORDER BY tr.total_invested_amount DESC, tr.ticker_symbol ASC
) AS position_rank
FROM ticker_rollup tr
JOIN portfolio_totals pt ON pt.user_id = tr.user_id
),
concentration AS (
SELECT
user_id,
SUM(position_weight * position_weight) AS hhi_concentration,
MAX(position_weight) AS top_position_weight,
MAX(ticker_symbol) FILTER (WHERE position_rank = 1) AS top_ticker
FROM weights
GROUP BY user_id
),
monthly_investment AS (
SELECT
ib.user_id,
m.month_no,
COALESCE(SUM(a.quantity * a.buy_price), 0) AS monthly_invested_amount
FROM investor_base ib
CROSS JOIN months m
LEFT JOIN assets a
ON a.user_id = ib.user_id
AND a.buy_date >= DATE '2026-01-01'
AND a.buy_date < DATE '2027-01-01'
AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no
GROUP BY ib.user_id, m.month_no
),
monthly_investment_stats AS (
SELECT
user_id,
AVG(monthly_invested_amount) AS avg_monthly_contribution,
STDDEV_SAMP(monthly_invested_amount) AS contribution_stddev,
COUNT(*) FILTER (WHERE monthly_invested_amount > 0) AS active_investing_months
FROM monthly_investment
GROUP BY user_id
)
SELECT
ib.user_id,
ib.username,
ib.email,
COALESCE(pt.annual_total_invested, 0) AS annual_total_invested,
COALESCE(pt.annual_lot_count, 0) AS annual_lot_count,
COALESCE(pt.distinct_tickers, 0) AS distinct_tickers,
ROUND(COALESCE(ms.avg_monthly_contribution, 0)::numeric, 2) AS avg_monthly_contribution,
COALESCE(ms.active_investing_months, 0) AS active_investing_months,
ROUND((COALESCE(ms.active_investing_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
ROUND(COALESCE(c.hhi_concentration, 0)::numeric, 4) AS hhi_concentration,
ROUND((1 - COALESCE(c.hhi_concentration, 1))::numeric, 4) AS diversification_index,
ROUND(COALESCE(c.top_position_weight, 0)::numeric, 4) AS top_position_weight,
c.top_ticker,
ROUND((COALESCE(ms.contribution_stddev, 0) / NULLIF(ms.avg_monthly_contribution, 0))::numeric, 4) AS contribution_volatility_cv,
DENSE_RANK() OVER (
ORDER BY
(1 - COALESCE(c.hhi_concentration, 1)) DESC,
COALESCE(pt.annual_total_invested, 0) DESC,
COALESCE(ms.active_investing_months, 0) DESC,
ib.user_id ASC
) AS investing_annual_rank
FROM investor_base ib
LEFT JOIN portfolio_totals pt ON pt.user_id = ib.user_id
LEFT JOIN concentration c ON c.user_id = ib.user_id
LEFT JOIN monthly_investment_stats ms ON ms.user_id = ib.user_id
ORDER BY investing_annual_rank, ib.user_id;
Без индекс, релевантниот дел од планот (CTE monthly_investment):
-> HashAggregate (cost=13884.12..14086.44 rows=12144 width=20) (actual time=52.114..150.441 rows=12144 loops=1)
Group Key: ib.user_id, m.month_no
-> Hash Right Join (cost=28.55..13416.88 rows=4844 width=20) (actual time=0.448..90.114 rows=4814 loops=1)
Hash Cond: (a.user_id = ib.user_id)
Filter: (EXTRACT(month FROM a.buy_date)::int = m.month_no)
-> Seq Scan on assets a (cost=0.00..14218.44 rows=4844 width=40) (actual time=0.021..48.774 rows=4814 loops=1)
Filter: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
Rows Removed by Filter: 8728
-> Hash (cost=16.44..16.44 rows=12144 width=12) (actual time=0.401..0.402 rows=12144 loops=1)
Planning Time: 0.884 ms
Execution Time: 382.104 ms
Просечниот Execution Time од 10 извршувања на целиот извештај е 386,2ms.
Додаваме композитен индекс кој води со buy_date, проследен со user_id:
CREATE INDEX idx_assets_buy_date_user ON assets(buy_date, user_id); ANALYZE assets;
Со индекс:
-> HashAggregate (cost=6041.62..6243.94 rows=12144 width=20) (actual time=42.114..140.441 rows=12144 loops=1)
Group Key: ib.user_id, m.month_no
-> Hash Right Join (cost=28.55..5506.86 rows=4844 width=20) (actual time=0.412..62.114 rows=4814 loops=1)
Hash Cond: (a.user_id = ib.user_id)
Filter: (EXTRACT(month FROM a.buy_date)::int = m.month_no)
-> Index Scan using idx_assets_buy_date_user on assets a (cost=0.43..5506.86 rows=4844 width=40) (actual time=0.041..26.441 rows=4814 loops=1)
Index Cond: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
-> Hash (cost=16.44..16.44 rows=12144 width=12) (actual time=0.381..0.382 rows=12144 loops=1)
Planning Time: 0.851 ms
Execution Time: 353.904 ms
Seq Scan on assets е заменет со Index Scan using idx_assets_buy_date_user. Скенирањето падна од ~49ms на ~26ms. Просечен Execution Time на целиот извештај: 357,0ms.
Додаваме covering index со quantity, buy_price и ticker_symbol:
CREATE INDEX idx_assets_covering ON assets(buy_date, user_id, quantity, buy_price, ticker_symbol); ANALYZE assets;
-> HashAggregate (cost=3414.88..3617.20 rows=12144 width=20) (actual time=32.114..130.441 rows=12144 loops=1)
Group Key: ib.user_id, m.month_no
-> Hash Right Join (cost=28.55..2880.12 rows=4844 width=20) (actual time=0.311..50.114 rows=4814 loops=1)
Hash Cond: (a.user_id = ib.user_id)
Filter: (EXTRACT(month FROM a.buy_date)::int = m.month_no)
-> Index Only Scan using idx_assets_covering on assets a (cost=0.43..2880.12 rows=4844 width=40) (actual time=0.031..13.774 rows=4814 loops=1)
Index Cond: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
Heap Fetches: 0
-> Hash (cost=16.44..16.44 rows=12144 width=12) (actual time=0.301..0.302 rows=12144 loops=1)
Planning Time: 0.828 ms
Execution Time: 342.004 ms
Index Only Scan using idx_assets_covering со Heap Fetches: 0 — quantity, buy_price и ticker_symbol се читаат директно од индексот. Просечен Execution Time на целиот извештај: 344,9ms.
Заклучок
Без индекси таргетираниот дел извршуваше Seq Scan на целата assets табела (~13 542 редици) со post-filter за годината во CTE-то monthly_investment. Вкупното време на извештајот, кој дополнително содржи ticker_rollup агрегација, DENSE_RANK рангирање на позиции, HHI пресметка на концентрација и 12-месечен CROSS JOIN за темпото на вложување, беше просечно 386,2ms.
По додавање на idx_assets_buy_date_user планерот премина на Index Scan — скенирањето падна од ~49ms на ~26ms, а вкупното време падна на 357,0ms (~1,08x подобрување).
По додавање на covering index idx_assets_covering планерот премина на Index Only Scan со Heap Fetches: 0 — quantity, buy_price и ticker_symbol потребни за SUM и GROUP BY агрегатите се читани директно од индексот. Вкупното време падна на 344,9ms (~1,12x подобрување наспроти почетното). Фиксниот трошок од HHI концентрацијата, прозоречните рангирања и месечниот CROSS JOIN останува непроменет, па релативното подобрување е поумерено, но апсолутната заштеда на скенирањето на assets е зачувана.
Безбедност и заштита
JWT Token Authorization (Spring Security)
JWT (JSON Web Token) e stateless начин на автентикација - server НЕ чува информации за активни сесии во база, туку сите потребни податоци се во самиот token кој корисникот го чува локално/cookie. JWT содржи енкодирана json структура на информации (user_id, email, role, expiry...).
Java код во Spring Boot:
@Configuration
@Getter @Setter
@ConfigurationProperties(prefix = "auth")
public class AuthProperties {
private String secret;
private int accessTokenMaxAge;
private int refreshTokenMaxAge;
}
@Configuration
@EnableWebSecurity
public class SecurityConfig {
private final JwtAuthenticationFilter jwtAuthenticationFilter;
public SecurityConfig(JwtAuthenticationFilter jwtAuthenticationFilter) {
this.jwtAuthenticationFilter = jwtAuthenticationFilter;
}
@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
http
.csrf(AbstractHttpConfigurer::disable)
.cors(Customizer.withDefaults())
.sessionManagement(sm -> sm.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
.httpBasic(AbstractHttpConfigurer::disable)
.formLogin(AbstractHttpConfigurer::disable)
.authorizeHttpRequests(auth -> auth
.requestMatchers("/api/auth/**").permitAll()
.anyRequest().authenticated());
http.addFilterBefore(jwtAuthenticationFilter, UsernamePasswordAuthenticationFilter.class);
return http.build();
}
Хеширање на пасворди (BCrypt)
Пасвордите на корисниците се чуваат во база во хеширана форма преку BCrypt, а не како plain text. Ова овозможува сигурно чување на пасвордите.
public class LegacyPasswordEncoder implements PasswordEncoder {
private final BCryptPasswordEncoder bcrypt = new BCryptPasswordEncoder();
@Override
public String encode(CharSequence rawPassword) {
return bcrypt.encode(rawPassword);
}
@Override
public boolean matches(CharSequence rawPassword, String encodedPassword) {
if (encodedPassword == null || encodedPassword.isEmpty()) {
return false;
}
if (encodedPassword.startsWith("$2a$") || encodedPassword.startsWith("$2b$")) {
return bcrypt.matches(rawPassword, encodedPassword);
}
return rawPassword.toString().equals(encodedPassword);
}
}
SQL Injection Prevention (Spring JPA/JPQL)
Целиот backend користи Spring Data JPA која автоматски генерира параметризирани пропити што спречуваат SQL injection.
Безбедно: Derived Query Methods
// UserRepository.java
public interface UserRepository extends JpaRepository<User, Long> {
Optional<User> findByEmail(String email);
Optional<User> findByUsername(String username);
}
// SELECT * FROM users WHERE email = ?
// параметарот се праќа одвоено од SQL командата
Безбедно: @Query со @Param
// TaskRepository.java
// FIX: JPQL field name corrected from t.finished to t.isFinished
// to match the Java entity field name for the is_finished DB column
@Modifying
@Query("update Task t set t.isFinished = false where t.disciplineUser.userId = :userId")
int resetFinishedForUser(@Param("userId") Long userId);
// Параметарот :userId се врзува безбедно преку JDBC, не преку string concatenation
Безбедно: Collection параметри
// TrainingSessionRepository.java
@java.util.List<TrainingSession> findByTrainingUser_UserIdAndDateIn(
Long userId,
Collection<LocalDate> dates
);
CORS Configuration
CORS е безбеден механизам за заштита од requests од различни домени. Со тоа се заштитуваме од можни злонамерни requests.
Java код во Spring Boot:
@Bean
public CorsConfigurationSource corsConfigurationSource() {
CorsConfiguration config = new CorsConfiguration();
config.setAllowedOrigins(List.of(
"http://localhost:5173",
"http://127.0.0.1:5173"));
config.setAllowedMethods(List.of("GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"));
config.setAllowedHeaders(List.of("Authorization", "Content-Type", "Accept", "Origin", "X-Requested-With"));
config.setAllowCredentials(true);
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
source.registerCorsConfiguration("/**", config);
return source;
}
