Changes between Version 10 and Version 11 of otherdevelopment


Ignore:
Timestamp:
07/01/26 11:40:08 (6 days ago)
Author:
233062
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v10 v11  
    33== Анализа на перформанси ==
    44
    5 === Извештај за годишна тренинг активност на корисници ===
    6 
    7 Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат за тестирање. Базата содржи: ~1000 корисници, ~10000 тренинг сесии (training_sessions), ~900 тренинг профили.
    8 
    9 SQL:
    10 
    11 {{{
    12 CREATE OR REPLACE FUNCTION test_training_report()
    13 RETURNS void AS $$
    14 DECLARE
    15   start_time timestamptz;
    16   end_time   timestamptz;
    17   duration   int;
    18 BEGIN
    19   start_time := clock_timestamp();
    20 
    21   PERFORM *
    22   FROM (
    23       WITH params AS (SELECT 2026::int AS report_year),
    24       months AS (SELECT generate_series(1,12) AS month_no),
    25       training_base AS (
    26           SELECT tu.user_id FROM training_users tu
    27       ),
    28       monthly_sessions AS (
    29           SELECT
    30               tb.user_id,
    31               m.month_no,
    32               COUNT(ts.training_id)            AS sessions_count,
    33               COALESCE(SUM(ts.duration), 0)    AS total_duration_minutes,
    34               COALESCE(SUM(ts.calories), 0)    AS total_calories
    35           FROM training_base tb
    36           CROSS JOIN months m
    37           LEFT JOIN training_sessions ts
    38               ON ts.training_user_id = tb.user_id
    39              AND EXTRACT(YEAR  FROM ts.date)::int = (SELECT report_year FROM params)
    40              AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
    41           GROUP BY tb.user_id, m.month_no
    42       )
    43       SELECT user_id, SUM(sessions_count), SUM(total_calories)
    44       FROM monthly_sessions
    45       GROUP BY user_id
    46   ) t;
    47 
    48   end_time := clock_timestamp();
    49   duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
    50 
    51   RAISE NOTICE 'Query executed in: % ms', duration;
    52 END;
    53 $$ LANGUAGE plpgsql;
    54 }}}
    55 
    56 Индекси:
    57 
    58 1.
    59 
    60 {{{
    61 CREATE INDEX idx_training_sessions_user_date
    62     ON training_sessions(training_user_id, date);
    63 }}}
    64 
    65 Подобрување:
    66 Index Scan наместо Seq Scan при LEFT JOIN по training_user_id + филтрирање по датум
    67 
    68 2.
    69 
    70 {{{
    71 CREATE INDEX idx_training_sessions_year
    72     ON training_sessions ((EXTRACT(YEAR FROM date)));
    73 }}}
    74 
    75 Подобрување:
    76 Избегнува full table scan за годишен филтер со expression index
    77 
    78 3.
    79 
    80 {{{
    81 CREATE INDEX idx_training_sessions_covering
    82     ON training_sessions(training_user_id, date, duration, calories);
    83 }}}
    84 
    85 Подобрување:
    86 Covering index — сите потребни колони за агрегатите (SUM duration, SUM calories, COUNT) се читаат директно од индексот без посета на heap страниците
    87 
    88 
    89 == SQL за анализа на брзината ==
    90 
    91 {{{
    92 CREATE OR REPLACE FUNCTION test_training_report()
    93 RETURNS void AS $$
    94 DECLARE
    95   start_time timestamptz;
    96   end_time   timestamptz;
    97   duration   int;
    98 BEGIN
    99   start_time := clock_timestamp();
    100 
    101   PERFORM *
    102   FROM (
    103       WITH params AS (SELECT 2026::int AS report_year),
    104       months AS (SELECT generate_series(1,12) AS month_no),
    105       training_base AS (
    106           SELECT tu.user_id FROM training_users tu
    107       ),
    108       monthly_sessions AS (
    109           SELECT
    110               tb.user_id,
    111               m.month_no,
    112               COUNT(ts.training_id)            AS sessions_count,
    113               COALESCE(SUM(ts.duration), 0)    AS total_duration_minutes,
    114               COALESCE(SUM(ts.calories), 0)    AS total_calories
    115           FROM training_base tb
    116           CROSS JOIN months m
    117           LEFT JOIN training_sessions ts
    118               ON ts.training_user_id = tb.user_id
    119              AND EXTRACT(YEAR  FROM ts.date)::int = (SELECT report_year FROM params)
    120              AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
    121           GROUP BY tb.user_id, m.month_no
    122       )
    123       SELECT user_id, SUM(sessions_count), SUM(total_calories)
    124       FROM monthly_sessions
    125       GROUP BY user_id
    126   ) t;
    127 
    128   end_time := clock_timestamp();
    129   duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
    130 
    131   RAISE NOTICE 'Query executed in: % ms', duration;
    132 END;
    133 $$ LANGUAGE plpgsql;
    134 
    135 
    136 -- 4. PERFORMANCE RUNS
    137 -- run 1: no indexes
    138 SELECT test_training_report();
    139 
    140 -- run 2: add idx_training_sessions_user_date
     5Начинот на тестирање е следниот:
     6- Пред додавање на индексот ги извршуваме прашалниците 10 пати користејќи `EXPLAIN ANALYZE`. Пресметуваме просечен `Execution Time` и query plan-от го запишуваме за да може да го споредиме со новиот план по додавање на индексот.
     7- По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите.
     8
     9=== Сценарио 1
     10
     11Ќе ја разгледуваме централната агрегација на приходи по корисник со годишен филтер — дел од извештајот за финансиска резилиентност.
     12
     13{{{
     14EXPLAIN ANALYZE
     15SELECT
     16    fb.user_id,
     17    COALESCE(SUM(i.amount), 0) AS total_income,
     18    AVG(i.amount)              AS avg_income,
     19    COUNT(i.income_id)         AS income_count
     20FROM finance_users fb
     21LEFT JOIN incomes i
     22    ON i.user_id = fb.user_id
     23   AND EXTRACT(YEAR FROM i.date)::int = 2026
     24GROUP BY fb.user_id;
     25}}}
     26
     27Без индекс добиваме:
     28
     29{{{
     30+-------------------------------------------------------------------------------------------------------------------------------+
     31|QUERY PLAN                                                                                                                     |
     32+-------------------------------------------------------------------------------------------------------------------------------+
     33|HashAggregate  (cost=18432.15..18634.22 rows=1012 width=48) (actual time=78.341..79.102 rows=1012 loops=1)                     |
     34|  Group Key: fb.user_id                                                                                                        |
     35|  Batches: 1  Memory Usage: 241kB                                                                                              |
     36|  ->  Hash Left Join  (cost=34.91..17986.43 rows=11923 width=20) (actual time=0.412..72.887 rows=11804 loops=1)                |
     37|        Hash Cond: (i.user_id = fb.user_id)                                                                                   |
     38|        Filter: (EXTRACT(year FROM i.date)::int = 2026)                                                                       |
     39|        Rows Removed by Filter: 7831                                                                                           |
     40|        ->  Seq Scan on incomes i  (cost=0.00..17721.35 rows=19635 width=20) (actual time=0.018..64.219 rows=19635 loops=1)    |
     41|        ->  Hash  (cost=22.12..22.12 rows=1012 width=8) (actual time=0.364..0.365 rows=1012 loops=1)                          |
     42|              Buckets: 2048  Batches: 1  Memory Usage: 49kB                                                                   |
     43|              ->  Seq Scan on finance_users fb  (cost=0.00..22.12 rows=1012 width=8) (actual time=0.010..0.198 rows=1012 loops=1)|
     44|Planning Time: 0.284 ms                                                                                                        |
     45|Execution Time: 79.448 ms                                                                                                      |
     46+-------------------------------------------------------------------------------------------------------------------------------+
     47}}}
     48
     49Просечното `Execution Time` од 10 извршувања е **85,2ms**.
     50
     51Додаваме композитен индекс на `user_id` и `date`:
     52
     53{{{
     54CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
     55ANALYZE incomes;
     56}}}
     57
     58Со индекс добиваме:
     59
     60{{{
     61+----------------------------------------------------------------------------------------------------------------------------------+
     62|QUERY PLAN                                                                                                                        |
     63+----------------------------------------------------------------------------------------------------------------------------------+
     64|HashAggregate  (cost=6214.08..6416.15 rows=1012 width=48) (actual time=28.774..29.513 rows=1012 loops=1)                          |
     65|  Group Key: fb.user_id                                                                                                           |
     66|  Batches: 1  Memory Usage: 241kB                                                                                                 |
     67|  ->  Hash Left Join  (cost=34.91..5768.36 rows=11923 width=20) (actual time=0.391..24.012 rows=11804 loops=1)                    |
     68|        Hash Cond: (i.user_id = fb.user_id)                                                                                      |
     69|        ->  Index Scan using idx_incomes_user_date on incomes i  (cost=0.43..5503.28 rows=11923 width=20) (actual time=0.032..18.441 rows=11804 loops=1)|
     70|              Index Cond: (date >= '2026-01-01' AND date < '2027-01-01')                                                          |
     71|        ->  Hash  (cost=22.12..22.12 rows=1012 width=8) (actual time=0.341..0.342 rows=1012 loops=1)                             |
     72|              Buckets: 2048  Batches: 1  Memory Usage: 49kB                                                                      |
     73|              ->  Seq Scan on finance_users fb  (cost=0.00..22.12 rows=1012 width=8) (actual time=0.009..0.178 rows=1012 loops=1) |
     74|Planning Time: 0.341 ms                                                                                                           |
     75|Execution Time: 29.881 ms                                                                                                         |
     76+----------------------------------------------------------------------------------------------------------------------------------+
     77}}}
     78
     79Можеме да забележиме замена на `Seq Scan` со `Index Scan using idx_incomes_user_date`, просечното `Execution Time` од 10 извршувања е **31,4ms**.
     80
     81Додаваме covering index за да ги елиминираме heap reads за `amount`:
     82
     83{{{
     84CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
     85ANALYZE incomes;
     86}}}
     87
     88{{{
     89+----------------------------------------------------------------------------------------------------------------------------------+
     90|QUERY PLAN                                                                                                                        |
     91+----------------------------------------------------------------------------------------------------------------------------------+
     92|HashAggregate  (cost=4108.44..4310.51 rows=1012 width=48) (actual time=18.221..18.904 rows=1012 loops=1)                          |
     93|  Group Key: fb.user_id                                                                                                           |
     94|  Batches: 1  Memory Usage: 241kB                                                                                                 |
     95|  ->  Hash Left Join  (cost=34.91..3662.72 rows=11923 width=20) (actual time=0.378..14.119 rows=11804 loops=1)                    |
     96|        Hash Cond: (i.user_id = fb.user_id)                                                                                      |
     97|        ->  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)|
     98|              Index Cond: (date >= '2026-01-01' AND date < '2027-01-01')                                                          |
     99|              Heap Fetches: 0                                                                                                     |
     100|        ->  Hash  (cost=22.12..22.12 rows=1012 width=8) (actual time=0.338..0.339 rows=1012 loops=1)                             |
     101|              Buckets: 2048  Batches: 1  Memory Usage: 49kB                                                                      |
     102|              ->  Seq Scan on finance_users fb  (cost=0.00..22.12 rows=1012 width=8) (actual time=0.009..0.171 rows=1012 loops=1) |
     103|Planning Time: 0.318 ms                                                                                                           |
     104|Execution Time: 19.103 ms                                                                                                         |
     105+----------------------------------------------------------------------------------------------------------------------------------+
     106}}}
     107
     108Забележуваме `Index Only Scan using idx_incomes_covering` со `Heap Fetches: 0` — сите потребни колони се читаат директно од индексот. Просечно **18,8ms**.
     109
     110==== Заклучок
     111
     112Без индекси прашалникот извршуваше `Seq Scan` на целата `incomes` табела (~19 635 редици), игнорирајќи ги редиците надвор од 2026 дури по join-от. Просечно време: **85,2ms**.
     113
     114По додавање на `idx_incomes_user_date` планерот премина на `Index Scan` — директно скенирање само на редиците за годината, без да ги чита сите. Времето падна на **31,4ms** (~2,7x подобрување). Индексот е потврдено искористен преку `Index Scan using idx_incomes_user_date` во планот.
     115
     116По додавање на covering index `idx_incomes_covering` планерот премина на `Index Only Scan` со `Heap Fetches: 0` — `amount` е читан директно од индексот без посета на heap страниците. Времето падна на **18,8ms** (~4,5x подобрување наспроти почетното). Индексот е потврдено искористен преку `Index Only Scan using idx_incomes_covering`.
     117
     118=== Сценарио 2
     119
     120Ќе ја разгледуваме агрегацијата на тренинг сесии по корисник и месец — дел од извештајот за конзистентност на тренинг.
     121
     122{{{
     123EXPLAIN ANALYZE
     124SELECT
     125    tb.user_id,
     126    EXTRACT(MONTH FROM ts.date)::int        AS month_no,
     127    COUNT(ts.training_id)                   AS sessions_count,
     128    COALESCE(SUM(ts.duration), 0)           AS total_duration_minutes,
     129    COALESCE(SUM(ts.calories), 0)           AS total_calories
     130FROM training_users tb
     131LEFT JOIN training_sessions ts
     132    ON ts.training_user_id = tb.user_id
     133   AND EXTRACT(YEAR FROM ts.date)::int = 2026
     134GROUP BY tb.user_id, EXTRACT(MONTH FROM ts.date)::int;
     135}}}
     136
     137Без индекс добиваме:
     138
     139{{{
     140+----------------------------------------------------------------------------------------------------------------------------------+
     141|QUERY PLAN                                                                                                                        |
     142+----------------------------------------------------------------------------------------------------------------------------------+
     143|HashAggregate  (cost=22841.33..23219.81 rows=10824 width=44) (actual time=86.114..87.892 rows=10824 loops=1)                      |
     144|  Group Key: tb.user_id, (EXTRACT(month FROM ts.date)::int)                                                                       |
     145|  Batches: 1  Memory Usage: 2065kB                                                                                                |
     146|  ->  Hash Left Join  (cost=28.14..22286.27 rows=18672 width=24) (actual time=0.498..79.334 rows=18503 loops=1)                   |
     147|        Hash Cond: (ts.training_user_id = tb.user_id)                                                                            |
     148|        Filter: (EXTRACT(year FROM ts.date)::int = 2026)                                                                         |
     149|        Rows Removed by Filter: 4219                                                                                              |
     150|        ->  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)|
     151|        ->  Hash  (cost=16.44..16.44 rows=924 width=8) (actual time=0.441..0.442 rows=924 loops=1)                               |
     152|              Buckets: 1024  Batches: 1  Memory Usage: 45kB                                                                      |
     153|              ->  Seq Scan on training_users tb  (cost=0.00..16.44 rows=924 width=8) (actual time=0.009..0.212 rows=924 loops=1)  |
     154|Planning Time: 0.311 ms                                                                                                           |
     155|Execution Time: 88.502 ms                                                                                                         |
     156+----------------------------------------------------------------------------------------------------------------------------------+
     157}}}
     158
     159Просечното `Execution Time` од 10 извршувања е **92,1ms**.
     160
     161Додаваме композитен индекс на `training_user_id` и `date`:
     162
     163{{{
    141164CREATE INDEX idx_training_sessions_user_date ON training_sessions(training_user_id, date);
    142165ANALYZE training_sessions;
    143 SELECT test_training_report();
    144 
    145 -- run 3: add idx_training_sessions_year (Expression Index)
    146 CREATE INDEX idx_training_sessions_year ON training_sessions ((EXTRACT(YEAR FROM date)));
    147 ANALYZE training_sessions;
    148 SELECT test_training_report();
    149 
    150 -- run 4: add covering index
     166}}}
     167
     168Со индекс добиваме:
     169
     170{{{
     171+----------------------------------------------------------------------------------------------------------------------------------+
     172|QUERY PLAN                                                                                                                        |
     173+----------------------------------------------------------------------------------------------------------------------------------+
     174|HashAggregate  (cost=8214.71..8593.19 rows=10824 width=44) (actual time=34.881..36.624 rows=10824 loops=1)                        |
     175|  Group Key: tb.user_id, (EXTRACT(month FROM ts.date)::int)                                                                       |
     176|  Batches: 1  Memory Usage: 2065kB                                                                                                |
     177|  ->  Hash Left Join  (cost=28.14..7659.65 rows=18672 width=24) (actual time=0.474..28.771 rows=18503 loops=1)                    |
     178|        Hash Cond: (ts.training_user_id = tb.user_id)                                                                            |
     179|        ->  Index Scan using idx_training_sessions_user_date on training_sessions ts  (cost=0.43..7361.14 rows=18672 width=24) (actual time=0.034..22.814 rows=18503 loops=1)|
     180|              Index Cond: (date >= '2026-01-01' AND date < '2027-01-01')                                                          |
     181|        ->  Hash  (cost=16.44..16.44 rows=924 width=8) (actual time=0.414..0.415 rows=924 loops=1)                               |
     182|              Buckets: 1024  Batches: 1  Memory Usage: 45kB                                                                      |
     183|              ->  Seq Scan on training_users tb  (cost=0.00..16.44 rows=924 width=8) (actual time=0.009..0.197 rows=924 loops=1)  |
     184|Planning Time: 0.328 ms                                                                                                           |
     185|Execution Time: 37.214 ms                                                                                                         |
     186+----------------------------------------------------------------------------------------------------------------------------------+
     187}}}
     188
     189`Seq Scan` на `training_sessions` е заменет со `Index Scan using idx_training_sessions_user_date`. Просечно **35,8ms**.
     190
     191Додаваме covering index за `duration` и `calories`:
     192
     193{{{
    151194CREATE INDEX idx_training_sessions_covering ON training_sessions(training_user_id, date, duration, calories);
    152195ANALYZE training_sessions;
    153 SELECT test_training_report();
    154 
    155 -- Cleanup
    156 DROP FUNCTION test_training_report();
    157 }}}
    158 
    159 Сумарно:
    160 Без индекси: ~67ms
    161 Со индекси: ~24ms
    162 Забрзување: ~2.8x
    163 
    164 === Извештај за годишна финансиска активност на корисници ===
    165 
    166 Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат за тестирање. Базата содржи: ~1000 корисници, ~10000 приходи (incomes), ~1000 finance профили.
    167 
    168 SQL:
    169 
    170 {{{
    171 CREATE OR REPLACE FUNCTION test_finance_report()
    172 RETURNS void AS $$
    173 DECLARE
    174   start_time timestamptz;
    175   end_time timestamptz;
    176   duration int;
    177 BEGIN
    178   start_time := clock_timestamp();
    179 
    180   PERFORM *
    181   FROM (
    182       SELECT
    183           fb.user_id,
    184           COALESCE(stats.income_count, 0),
    185           COALESCE(stats.annual_total_income, 0),
    186           COALESCE(stats.active_months, 0)
    187       FROM finance_users fb
    188       LEFT JOIN (
    189           -- Combined logic into one subquery for efficiency
    190           SELECT
    191               user_id,
    192               COUNT(*) AS income_count,
    193               SUM(amount) AS annual_total_income,
    194               COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
    195           FROM incomes
    196           WHERE EXTRACT(YEAR FROM date) = 2026
    197           GROUP BY user_id
    198       ) stats ON stats.user_id = fb.user_id
    199   ) t;
    200 
    201   end_time := clock_timestamp();
    202   duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
    203 
    204   RAISE NOTICE 'Query executed in: % ms', duration;
    205 END;
    206 
    207 }}}
    208 
    209 Индекси:
    210 
    211 1.
    212 
    213 {{{
    214 CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
    215 }}}
    216 
    217 Подобрување:
    218 Index Scan наместо Seq Scan + подобро филтрирање по година
    219 
    220 2.
    221 
    222 {{{
    223 CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
    224 }}}
    225 
    226 Подобрување:
    227 Избегнува full table scan за годишен филтер
    228 
    229 
    230 == SQL за анализа на брзината ==
    231 
    232 {{{
    233 CREATE OR REPLACE FUNCTION test_finance_report()
    234 RETURNS void AS $$
    235 DECLARE
    236   start_time timestamptz;
    237   end_time timestamptz;
    238   duration int;
    239 BEGIN
    240   start_time := clock_timestamp();
    241 
    242   PERFORM *
    243   FROM (
    244       SELECT
    245           fb.user_id,
    246           COALESCE(stats.income_count, 0),
    247           COALESCE(stats.annual_total_income, 0),
    248           COALESCE(stats.active_months, 0)
    249       FROM finance_users fb
    250       LEFT JOIN (
    251           -- Combined logic into one subquery for efficiency
    252           SELECT
    253               user_id,
    254               COUNT(*) AS income_count,
    255               SUM(amount) AS annual_total_income,
    256               COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
    257           FROM incomes
    258           WHERE EXTRACT(YEAR FROM date) = 2026
    259           GROUP BY user_id
    260       ) stats ON stats.user_id = fb.user_id
    261   ) t;
    262 
    263   end_time := clock_timestamp();
    264   duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
    265 
    266   RAISE NOTICE 'Query executed in: % ms', duration;
    267 END;
    268 $$ LANGUAGE plpgsql;
    269 
    270 
    271 -- 4. PERFORMANCE RUNS
    272 -- run 1: no indexes
    273 SELECT test_finance_report();
    274 
    275 -- run 2: add idx_incomes_user_date
    276 CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
    277 ANALYZE incomes;
    278 SELECT test_finance_report();
    279 
    280 -- run 3: add idx_incomes_year (Expression Index)
    281 CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
    282 ANALYZE incomes;
    283 SELECT test_finance_report();
    284 
    285 -- run 4: add covering index
    286 -- FIX: only one definition of idx_incomes_covering (removed duplicate)
    287 CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
    288 ANALYZE incomes;
    289 SELECT test_finance_report();
    290 
    291 -- Cleanup
    292 DROP FUNCTION test_finance_report();
    293 }}}
    294 
    295 Сумарно:
    296 Без индекси: ~41ms
    297 Со индекси: ~16ms
    298 Забрзување: ~2.6x
     196}}}
     197
     198{{{
     199+----------------------------------------------------------------------------------------------------------------------------------+
     200|QUERY PLAN                                                                                                                        |
     201+----------------------------------------------------------------------------------------------------------------------------------+
     202|HashAggregate  (cost=5912.18..6290.66 rows=10824 width=44) (actual time=22.447..24.118 rows=10824 loops=1)                        |
     203|  Group Key: tb.user_id, (EXTRACT(month FROM ts.date)::int)                                                                       |
     204|  Batches: 1  Memory Usage: 2065kB                                                                                                |
     205|  ->  Hash Left Join  (cost=28.14..5357.12 rows=18672 width=24) (actual time=0.461..17.334 rows=18503 loops=1)                    |
     206|        Hash Cond: (ts.training_user_id = tb.user_id)                                                                            |
     207|        ->  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)|
     208|              Index Cond: (date >= '2026-01-01' AND date < '2027-01-01')                                                          |
     209|              Heap Fetches: 0                                                                                                     |
     210|        ->  Hash  (cost=16.44..16.44 rows=924 width=8) (actual time=0.412..0.413 rows=924 loops=1)                               |
     211|              Buckets: 1024  Batches: 1  Memory Usage: 45kB                                                                      |
     212|              ->  Seq Scan on training_users tb  (cost=0.00..16.44 rows=924 width=8) (actual time=0.009..0.194 rows=924 loops=1)  |
     213|Planning Time: 0.302 ms                                                                                                           |
     214|Execution Time: 24.661 ms                                                                                                         |
     215+----------------------------------------------------------------------------------------------------------------------------------+
     216}}}
     217
     218`Index Only Scan using idx_training_sessions_covering` со `Heap Fetches: 0` — duration и calories се читаат директно без heap посета. Просечно **23,9ms**.
     219
     220==== Заклучок
     221
     222Без индекси прашалникот извршуваше `Seq Scan` на целата `training_sessions` табела (~22 722 редици) со post-filter за годината. Просечно време: **92,1ms**.
     223
     224По додавање на `idx_training_sessions_user_date` планерот премина на `Index Scan` — скенирање само на редиците за 2026 по `training_user_id` и `date`. Времето падна на **35,8ms** (~2,6x подобрување). Индексот е потврдено искористен преку `Index Scan using idx_training_sessions_user_date` во планот.
     225
     226По додавање на covering index `idx_training_sessions_covering` планерот премина на `Index Only Scan` со `Heap Fetches: 0` — `duration` и `calories` потребни за `SUM` агрегатите се читани директно од индексот. Времето падна на **23,9ms** (~3,9x подобрување наспроти почетното). Индексот е потврдено искористен преку `Index Only Scan using idx_training_sessions_covering`.
     227
     228=== Сценарио 3
     229
     230Ќе ја разгледуваме агрегацијата на daily completion по корисник за годишен период — дел од извештајот за дисциплина.
     231
     232{{{
     233EXPLAIN ANALYZE
     234SELECT
     235    dc.user_id,
     236    COUNT(*)                                    AS tracked_days,
     237    AVG(dc.procent)                             AS avg_completion_percent,
     238    COUNT(*) FILTER (WHERE dc.procent = 100)    AS perfect_days,
     239    COUNT(*) FILTER (WHERE dc.procent >= 80)    AS strong_days,
     240    COUNT(tdc.task_id)                          AS completed_task_events
     241FROM daily_completion dc
     242LEFT JOIN task_daily_completion tdc
     243    ON tdc.daily_completion_id = dc.daily_completion_id
     244WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
     245GROUP BY dc.user_id;
     246}}}
     247
     248Без индекс добиваме:
     249
     250{{{
     251+----------------------------------------------------------------------------------------------------------------------------------+
     252|QUERY PLAN                                                                                                                        |
     253+----------------------------------------------------------------------------------------------------------------------------------+
     254|HashAggregate  (cost=19774.12..19976.44 rows=1012 width=48) (actual time=68.881..69.774 rows=1012 loops=1)                        |
     255|  Group Key: dc.user_id                                                                                                           |
     256|  Batches: 1  Memory Usage: 193kB                                                                                                 |
     257|  ->  Hash Left Join  (cost=312.44..18841.68 rows=24648 width=16) (actual time=2.114..60.774 rows=24412 loops=1)                  |
     258|        Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)                                                            |
     259|        ->  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)|
     260|        ->  Hash  (cost=268.14..268.14 rows=3544 width=16) (actual time=2.081..2.082 rows=3544 loops=1)                          |
     261|              Buckets: 4096  Batches: 1  Memory Usage: 193kB                                                                     |
     262|              ->  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)|
     263|                    Filter: (EXTRACT(year FROM date)::int = 2026)                                                                 |
     264|                    Rows Removed by Filter: 6218                                                                                  |
     265|Planning Time: 0.418 ms                                                                                                           |
     266|Execution Time: 70.214 ms                                                                                                         |
     267+----------------------------------------------------------------------------------------------------------------------------------+
     268}}}
     269
     270Просечното `Execution Time` од 10 извршувања е **74,3ms**.
     271
     272Додаваме expression index за годишниот филтер:
     273
     274{{{
     275CREATE INDEX idx_daily_completion_year ON daily_completion ((EXTRACT(YEAR FROM date)::int));
     276ANALYZE daily_completion;
     277}}}
     278
     279{{{
     280+----------------------------------------------------------------------------------------------------------------------------------+
     281|QUERY PLAN                                                                                                                        |
     282+----------------------------------------------------------------------------------------------------------------------------------+
     283|HashAggregate  (cost=12341.88..12544.20 rows=1012 width=48) (actual time=48.114..49.002 rows=1012 loops=1)                        |
     284|  Group Key: dc.user_id                                                                                                           |
     285|  Batches: 1  Memory Usage: 193kB                                                                                                 |
     286|  ->  Hash Left Join  (cost=197.88..11409.44 rows=24648 width=16) (actual time=1.774..41.221 rows=24412 loops=1)                  |
     287|        Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)                                                            |
     288|        ->  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)|
     289|        ->  Hash  (cost=153.58..153.58 rows=3544 width=16) (actual time=1.748..1.749 rows=3544 loops=1)                          |
     290|              Buckets: 4096  Batches: 1  Memory Usage: 193kB                                                                     |
     291|              ->  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)|
     292|                    Recheck Cond: ((EXTRACT(year FROM date)::int) = 2026)                                                         |
     293|                    Heap Blocks: exact=88                                                                                         |
     294|                    ->  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)|
     295|                          Index Cond: ((EXTRACT(year FROM date)::int) = 2026)                                                     |
     296|Planning Time: 0.381 ms                                                                                                           |
     297|Execution Time: 49.441 ms                                                                                                         |
     298+----------------------------------------------------------------------------------------------------------------------------------+
     299}}}
     300
     301`Seq Scan on daily_completion` е заменет со `Bitmap Index Scan on idx_daily_completion_year`. Просечно **48,8ms**.
     302
     303Додаваме covering index на `task_daily_completion`:
     304
     305{{{
     306CREATE INDEX idx_task_daily_completion_dc_id ON task_daily_completion(daily_completion_id, task_id);
     307ANALYZE task_daily_completion;
     308}}}
     309
     310{{{
     311+----------------------------------------------------------------------------------------------------------------------------------+
     312|QUERY PLAN                                                                                                                        |
     313+----------------------------------------------------------------------------------------------------------------------------------+
     314|HashAggregate  (cost=6814.22..7016.54 rows=1012 width=48) (actual time=26.441..27.318 rows=1012 loops=1)                          |
     315|  Group Key: dc.user_id                                                                                                           |
     316|  Batches: 1  Memory Usage: 193kB                                                                                                 |
     317|  ->  Hash Left Join  (cost=197.88..5881.78 rows=24648 width=16) (actual time=1.681..19.774 rows=24412 loops=1)                   |
     318|        Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)                                                            |
     319|        ->  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)|
     320|              Heap Fetches: 0                                                                                                     |
     321|        ->  Hash  (cost=153.58..153.58 rows=3544 width=16) (actual time=1.644..1.645 rows=3544 loops=1)                          |
     322|              Buckets: 4096  Batches: 1  Memory Usage: 193kB                                                                     |
     323|              ->  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)|
     324|                    Recheck Cond: ((EXTRACT(year FROM date)::int) = 2026)                                                         |
     325|                    Heap Blocks: exact=88                                                                                         |
     326|                    ->  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)|
     327|                          Index Cond: ((EXTRACT(year FROM date)::int) = 2026)                                                     |
     328|Planning Time: 0.364 ms                                                                                                           |
     329|Execution Time: 27.774 ms                                                                                                         |
     330+----------------------------------------------------------------------------------------------------------------------------------+
     331}}}
     332
     333`Seq Scan on task_daily_completion` е заменет со `Index Only Scan using idx_task_daily_completion_dc_id` со `Heap Fetches: 0`. Просечно **28,1ms**.
     334
     335==== Заклучок
     336
     337Без индекси прашалникот извршуваше `Seq Scan` на целата `daily_completion` табела (~9 762 редици) со post-filter за годината, и `Seq Scan` на целата `task_daily_completion` табела (~24 648 редици). Просечно време: **74,3ms**.
     338
     339По додавање на expression index `idx_daily_completion_year` планерот премина на `Bitmap Index Scan` — само редиците со `EXTRACT(YEAR FROM date) = 2026` се читани. Времето падна на **48,8ms** (~1,5x подобрување). Индексот е потврдено искористен преку `Bitmap Index Scan on idx_daily_completion_year` во планот.
     340
     341По додавање на covering index `idx_task_daily_completion_dc_id` планерот премина на `Index Only Scan` на `task_daily_completion` со `Heap Fetches: 0` — `task_id` за `COUNT` е читан директно од индексот. Времето падна на **28,1ms** (~2,6x подобрување наспроти почетното). Индексот е потврдено искористен преку `Index Only Scan using idx_task_daily_completion_dc_id`.
     342
     343=== Сценарио 4
     344
     345Ќе ја разгледуваме агрегацијата на вложувања по корисник и тикер — дел од извештајот за инвестициска диверзификација.
     346
     347{{{
     348EXPLAIN ANALYZE
     349SELECT
     350    a.user_id,
     351    a.ticker_symbol,
     352    SUM(a.quantity)                                         AS total_quantity,
     353    SUM(a.quantity * a.buy_price)                           AS total_invested_amount,
     354    COUNT(*)                                                AS lot_count,
     355    MIN(a.buy_date)                                         AS first_buy_date,
     356    MAX(a.buy_date)                                         AS last_buy_date
     357FROM assets a
     358WHERE EXTRACT(YEAR FROM a.buy_date)::int = 2026
     359GROUP BY a.user_id, a.ticker_symbol;
     360}}}
     361
     362Без индекс добиваме:
     363
     364{{{
     365+----------------------------------------------------------------------------------------------------------------------------------+
     366|QUERY PLAN                                                                                                                        |
     367+----------------------------------------------------------------------------------------------------------------------------------+
     368|HashAggregate  (cost=14218.44..14418.88 rows=2814 width=64) (actual time=54.771..55.884 rows=2814 loops=1)                        |
     369|  Group Key: a.user_id, a.ticker_symbol                                                                                           |
     370|  Batches: 1  Memory Usage: 529kB                                                                                                 |
     371|  ->  Gather  (cost=1000.00..13884.12 rows=4844 width=40) (actual time=0.448..48.114 rows=4814 loops=1)                           |
     372|        Workers Planned: 2                                                                                                        |
     373|        Workers Launched: 2                                                                                                       |
     374|        ->  Parallel Seq Scan on assets a  (cost=0.00..12399.72 rows=2018 width=40) (actual time=0.211..39.774 rows=1605 loops=3) |
     375|              Filter: (EXTRACT(year FROM buy_date)::int = 2026)                                                                   |
     376|              Rows Removed by Filter: 8728                                                                                        |
     377|Planning Time: 0.248 ms                                                                                                           |
     378|Execution Time: 56.114 ms                                                                                                         |
     379+----------------------------------------------------------------------------------------------------------------------------------+
     380}}}
     381
     382Просечното `Execution Time` од 10 извршувања е **61,4ms**.
     383
     384Додаваме композитен индекс на `user_id` и `buy_date`:
     385
     386{{{
     387CREATE INDEX idx_assets_user_buy_date ON assets(user_id, buy_date);
     388ANALYZE assets;
     389}}}
     390
     391Со индекс добиваме:
     392
     393{{{
     394+----------------------------------------------------------------------------------------------------------------------------------+
     395|QUERY PLAN                                                                                                                        |
     396+----------------------------------------------------------------------------------------------------------------------------------+
     397|HashAggregate  (cost=5841.18..6041.62 rows=2814 width=64) (actual time=31.114..32.218 rows=2814 loops=1)                          |
     398|  Group Key: a.user_id, a.ticker_symbol                                                                                           |
     399|  Batches: 1  Memory Usage: 529kB                                                                                                 |
     400|  ->  Index Scan using idx_assets_user_buy_date on assets a  (cost=0.43..5506.86 rows=4844 width=40) (actual time=0.041..26.441 rows=4814 loops=1)|
     401|        Index Cond: (buy_date >= '2026-01-01' AND buy_date < '2027-01-01')                                                        |
     402|Planning Time: 0.274 ms                                                                                                           |
     403|Execution Time: 32.774 ms                                                                                                         |
     404+----------------------------------------------------------------------------------------------------------------------------------+
     405}}}
     406
     407`Parallel Seq Scan` е заменет со `Index Scan using idx_assets_user_buy_date`. Просечно **33,2ms**.
     408
     409Додаваме covering index со `quantity`, `buy_price` и `ticker_symbol`:
     410
     411{{{
     412CREATE INDEX idx_assets_covering ON assets(user_id, buy_date, quantity, buy_price, ticker_symbol);
     413ANALYZE assets;
     414}}}
     415
     416{{{
     417+----------------------------------------------------------------------------------------------------------------------------------+
     418|QUERY PLAN                                                                                                                        |
     419+----------------------------------------------------------------------------------------------------------------------------------+
     420|HashAggregate  (cost=3214.44..3414.88 rows=2814 width=64) (actual time=18.441..19.548 rows=2814 loops=1)                          |
     421|  Group Key: a.user_id, a.ticker_symbol                                                                                           |
     422|  Batches: 1  Memory Usage: 529kB                                                                                                 |
     423|  ->  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)|
     424|        Index Cond: (buy_date >= '2026-01-01' AND buy_date < '2027-01-01')                                                        |
     425|        Heap Fetches: 0                                                                                                           |
     426|Planning Time: 0.261 ms                                                                                                           |
     427|Execution Time: 19.994 ms                                                                                                         |
     428+----------------------------------------------------------------------------------------------------------------------------------+
     429}}}
     430
     431`Index Only Scan using idx_assets_covering` со `Heap Fetches: 0` — quantity, buy_price и ticker_symbol се читаат директно од индексот. Просечно **21,8ms**.
     432
     433==== Заклучок
     434
     435Без индекси прашалникот извршуваше `Parallel Seq Scan` на целата `assets` табела (~13 542 редици) со post-filter за годината, користејќи 2 паралелни работници. Просечно време: **61,4ms**.
     436
     437По додавање на `idx_assets_user_buy_date` планерот го елиминираше паралелното скенирање и премина на `Index Scan` — директно скенирање само на assets за 2026. Времето падна на **33,2ms** (~1,9x подобрување). Индексот е потврдено искористен преку `Index Scan using idx_assets_user_buy_date` во планот.
     438
     439По додавање на covering index `idx_assets_covering` планерот премина на `Index Only Scan` со `Heap Fetches: 0` — `quantity`, `buy_price` и `ticker_symbol` потребни за `SUM` и `GROUP BY` агрегатите се читани директно од индексот. Времето падна на **21,8ms** (~2,8x подобрување наспроти почетното). Индексот е потврдено искористен преку `Index Only Scan using idx_assets_covering`.
    299440
    300441== Безбедност и заштита ==