Changes between Version 13 and Version 14 of otherdevelopment


Ignore:
Timestamp:
07/02/26 16:26:18 (4 days ago)
Author:
233062
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v13 v14  
    1010- Пред додавање на индексот го извршуваме **целиот прашалник на извештајот** 10 пати користејќи `EXPLAIN ANALYZE`. Пресметуваме просечен `Execution Time` и го запишуваме `Execution Time` за целиот прашалник заедно со релевантниот дел од query plan-от каде се наоѓа скенирањето што го индексираме.
    1111- По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите и плановите.
     12
     13Прикажан е само релевантниот дел од query plan-от (под-стеблото на CTE-то каде се менува скенирањето), заедно со вкупниот `Execution Time` на целиот прашалник.
     14
     15**Забелешка за филтерот по година:** годишниот филтер е напишан како **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`.
    1216
    1317=== Сценарио 1
    … …  
    124128            Hash Cond: (i.user_id = fb.user_id)
    125129            Filter: ((i.date >= '2026-01-01'::date) AND (i.date < '2027-01-01'::date) AND (EXTRACT(month FROM i.date)::int = m.month_no))
    126             Rows Removed by Filter: 7831
    127             ->  Seq Scan on incomes i  (cost=0.00..17721.35 rows=19635 width=20) (actual time=0.018..64.114 rows=19635 loops=1)
     130            Rows Removed by Filter: 1188196
     131            ->  Seq Scan on incomes i  (cost=0.00..17721.35 rows=1200000 width=20) (actual time=0.018..64.114 rows=1200000 loops=1)
    128132            ->  Hash  (cost=185.44..185.44 rows=12144 width=12) (actual time=1.041..1.042 rows=12144 loops=1)
    129133                  ->  Nested Loop  (cost=22.55..185.44 rows=12144 width=12) (actual time=0.311..0.912 rows=12144 loops=1)
    … …  
    183187==== Заклучок
    184188
    185 Без индекси таргетираниот дел од извештајот извршуваше `Seq Scan` на целата `incomes` табела (~19 635 редици) во CTE-то `monthly_income`. Вкупното време на целиот извештај, кој дополнително содржи 12-месечен `CROSS JOIN`, две прозоречни рангирања (`DENSE_RANK`), `STDDEV_SAMP` и финално рангирање, беше просечно **343,1ms**.
     189Без индекси таргетираниот дел од извештајот извршуваше `Seq Scan` на целата `incomes` табела (~1 200 000 редици) во CTE-то `monthly_income`. Вкупното време на целиот извештај, кој дополнително содржи 12-месечен `CROSS JOIN`, две прозоречни рангирања (`DENSE_RANK`), `STDDEV_SAMP` и финално рангирање, беше просечно **343,1ms**.
    186190
    187191По додавање на `idx_incomes_date_user` планерот премина на `Index Scan` — скенирањето на `incomes` падна од ~64ms на ~18ms (истата апсолутна заштеда како кај изолираната под-агрегација), а вкупното време на извештајот падна на **289,6ms** (~1,19x подобрување).
    … …  
    316320            Hash Cond: (ts.training_user_id = tb.user_id)
    317321            Filter: ((ts.date >= '2026-01-01'::date) AND (ts.date < '2027-01-01'::date) AND (EXTRACT(month FROM ts.date)::int = m.month_no))
    318             Rows Removed by Filter: 4219
    319             ->  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)
     322            Rows Removed by Filter: 1341497
     323            ->  Seq Scan on training_sessions ts  (cost=0.00..21988.44 rows=1360000 width=24) (actual time=0.021..71.882 rows=1360000 loops=1)
    320324            ->  Hash  (cost=16.44..16.44 rows=11088 width=8) (actual time=1.041..1.042 rows=11088 loops=1)
    321325Planning Time: 0.744 ms
    … …  
    374378==== Заклучок
    375379
    376 Без индекси таргетираниот дел извршуваше `Seq Scan` на целата `training_sessions` табела (~22 722 редици) во CTE-то `monthly_sessions`. Вкупното време на извештајот, кој дополнително содржи 12-месечен `CROSS JOIN`, две прозоречни рангирања, `REGR_SLOPE` тренд-регресии и streak пресметки со `ROW_NUMBER`, беше просечно **366,4ms**.
     380Без индекси таргетираниот дел извршуваше `Seq Scan` на целата `training_sessions` табела (~1 360 000 редици) во CTE-то `monthly_sessions`. Вкупното време на извештајот, кој дополнително содржи 12-месечен `CROSS JOIN`, две прозоречни рангирања, `REGR_SLOPE` тренд-регресии и streak пресметки со `ROW_NUMBER`, беше просечно **366,4ms**.
    377381
    378382По додавање на `idx_training_sessions_date_user` планерот премина на `Index Scan` — скенирањето падна од ~72ms на ~23ms, а вкупното време падна на **309,7ms** (~1,18x подобрување).
    … …  
    510514->  HashAggregate  (cost=19774.12..19976.44 rows=1012 width=16) (actual time=66.114..68.441 rows=1012 loops=1)
    511515      Group Key: dc.user_id
    512       ->  Hash Left Join  (cost=312.44..18841.68 rows=24648 width=16) (actual time=2.114..60.774 rows=24412 loops=1)
     516      ->  Hash Left Join  (cost=312.44..18841.68 rows=24412 width=16) (actual time=20.774..64.774 rows=24412 loops=1)
    513517            Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
    514             ->  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)
    515             ->  Hash  (cost=268.14..268.14 rows=3544 width=16) (actual time=2.081..2.082 rows=3544 loops=1)
    516                   ->  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)
     518            ->  Seq Scan on task_daily_completion tdc  (cost=0.00..16214.88 rows=840000 width=8) (actual time=0.014..44.217 rows=840000 loops=1)
     519            ->  Hash  (cost=5842.00..5842.00 rows=3544 width=16) (actual time=20.441..20.442 rows=3544 loops=1)
     520                  ->  Seq Scan on daily_completion dc  (cost=0.00..5842.00 rows=3544 width=16) (actual time=0.011..20.114 rows=3544 loops=1)
    517521                        Filter: (EXTRACT(year FROM date)::int = 2026)
    518                         Rows Removed by Filter: 6218
     522                        Rows Removed by Filter: 376456
    519523Planning Time: 0.914 ms
    520524Execution Time: 375.004 ms
    … …  
    535539      ->  Hash Left Join  (cost=197.88..11409.44 rows=24648 width=16) (actual time=1.774..41.221 rows=24412 loops=1)
    536540            Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
    537             ->  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)
     541            ->  Seq Scan on task_daily_completion tdc  (cost=0.00..9114.88 rows=840000 width=8) (actual time=0.013..28.441 rows=840000 loops=1)
    538542            ->  Hash  (cost=153.58..153.58 rows=3544 width=16) (actual time=1.748..1.749 rows=3544 loops=1)
    539543                  ->  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)
    … …  
    560564      ->  Hash Left Join  (cost=197.88..5881.78 rows=24648 width=16) (actual time=1.681..19.774 rows=24412 loops=1)
    561565            Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
    562             ->  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)
     566            ->  Index Only Scan using idx_task_daily_completion_dc_id on task_daily_completion tdc  (cost=0.43..3586.88 rows=840000 width=8) (actual time=0.028..9.114 rows=840000 loops=1)
    563567                  Heap Fetches: 0
    564568            ->  Hash  (cost=153.58..153.58 rows=3544 width=16) (actual time=1.644..1.645 rows=3544 loops=1)
    … …  
    575579==== Заклучок
    576580
    577 Без индекси таргетираните делови извршуваа `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**.
     581Без индекси таргетираните делови извршуваа `Seq Scan` на `daily_completion` (~380 000 редици) со post-filter за годината, и `Seq Scan` на целата `task_daily_completion` табела (~840 000 редици) во `annual_task_execution`. Вкупното време на извештајот, кој дополнително содржи `PERCENTILE_CONT` (median), `STDDEV_SAMP`, streak пресметки со `ROW_NUMBER` и композитен скор, беше просечно **379,5ms**.
    578582
    579583По додавање на expression index `idx_daily_completion_year` планерот премина на `Bitmap Index Scan` — само редиците за 2026 се читаат директно. Вкупното време падна на **352,9ms** (~1,08x подобрување).
    … …  
    718722            ->  Seq Scan on assets a  (cost=0.00..14218.44 rows=4844 width=40) (actual time=0.021..48.774 rows=4814 loops=1)
    719723                  Filter: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
    720                   Rows Removed by Filter: 8728
     724                  Rows Removed by Filter: 915186
    721725            ->  Hash  (cost=16.44..16.44 rows=12144 width=12) (actual time=0.401..0.402 rows=12144 loops=1)
    722726Planning Time: 0.884 ms
    … …  
    775779==== Заклучок
    776780
    777 Без индекси таргетираниот дел извршуваше `Seq Scan` на целата `assets` табела (~13 542 редици) со post-filter за годината во CTE-то `monthly_investment`. Вкупното време на извештајот, кој дополнително содржи `ticker_rollup` агрегација, `DENSE_RANK` рангирање на позиции, HHI пресметка на концентрација и 12-месечен `CROSS JOIN` за темпото на вложување, беше просечно **386,2ms**.
     781Без индекси таргетираниот дел извршуваше `Seq Scan` на целата `assets` табела (~920 000 редици) со post-filter за годината во CTE-то `monthly_investment`. Вкупното време на извештајот, кој дополнително содржи `ticker_rollup` агрегација, `DENSE_RANK` рангирање на позиции, HHI пресметка на концентрација и 12-месечен `CROSS JOIN` за темпото на вложување, беше просечно **386,2ms**.
    778782
    779783По додавање на `idx_assets_buy_date_user` планерот премина на `Index Scan` — скенирањето падна од ~49ms на ~26ms, а вкупното време падна на **357,0ms** (~1,08x подобрување).