Changes between Version 1 and Version 2 of QueryOptimization


Ignore:
Timestamp:
05/19/26 13:02:05 (7 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v1 v2  
    11= Оптимизација на прашалници
    22
    3 == 1. stock_daily_returns
     3== 1. trade_request_full_view
    44
    55ЧЕКОР 1: Анализа ПРЕД оптимизација
    66
    77{{{
    8 EXPLAIN ANALYZE SELECT * FROM stock_daily_returns;
    9 }}}
    10 
    11 
    12 {{{
    13 QUERY PLAN                                                                                                                                             |
    14 -------------------------------------------------------------------------------------------------------------------------------------------------------+
    15 WindowAgg  (cost=202511.56..926167.09 rows=5000040 width=28) (actual time=63656.607..67318.714 rows=5000000 loops=1)                                   |
    16   ->  Gather Merge  (cost=202488.10..801166.09 rows=5000040 width=20) (actual time=63656.484..64906.161 rows=5000000 loops=1)                          |
    17         Workers Planned: 4                                                                                                                             |
    18         Workers Launched: 4                                                                                                                            |
    19         ->  Sort  (cost=201488.04..204613.06 rows=1250010 width=20) (actual time=63158.623..63323.156 rows=1000000 loops=5)                            |
    20               Sort Key: stock_history.stock_id, stock_history."timestamp"                                                                              |
    21               Sort Method: external merge  Disk: 33104kB                                                                                               |
    22               Worker 0:  Sort Method: external merge  Disk: 33256kB                                                                                    |
    23               Worker 1:  Sort Method: external merge  Disk: 33752kB                                                                                    |
    24               Worker 2:  Sort Method: external merge  Disk: 33328kB                                                                                    |
    25               Worker 3:  Sort Method: external merge  Disk: 33056kB                                                                                    |
    26               ->  Parallel Seq Scan on stock_history  (cost=0.00..49265.10 rows=1250010 width=20) (actual time=232.105..61726.223 rows=1000000 loops=5)|
    27 Planning Time: 762.047 ms                                                                                                                              |
    28 JIT:                                                                                                                                                   |
    29   Functions: 18                                                                                                                                        |
    30   Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                          |
    31   Timing: Generation 1.937 ms (Deform 0.855 ms), Inlining 572.569 ms, Optimization 80.151 ms, Emission 83.480 ms, Total 738.137 ms                     |
    32 Execution Time: 67562.694 ms                                                                                                                           |
    33 }}}
    34 
    35 Ова е многу бавно бидејќи нема индекс по (stock_id, timestamp)
     8EXPLAIN ANALYZE SELECT * FROM trade_request_full_view;
     9}}}
     10
     11
     12{{{
     13QUERY PLAN                                                                                                                           |
     14-------------------------------------------------------------------------------------------------------------------------------------+
     15Hash Join  (cost=298.00..140027.46 rows=5999904 width=59) (actual time=332.430..69577.929 rows=6000001 loops=1)                      |
     16  Hash Cond: (tr.portfolio_id = p.id)                                                                                                |
     17  ->  Seq Scan on trade_request tr  (cost=0.00..123973.04 rows=5999904 width=51) (actual time=34.685..67292.987 rows=6000001 loops=1)|
     18  ->  Hash  (cost=173.00..173.00 rows=10000 width=16) (actual time=297.573..297.574 rows=10000 loops=1)                              |
     19        Buckets: 16384  Batches: 1  Memory Usage: 597kB                                                                              |
     20        ->  Seq Scan on portfolios p  (cost=0.00..173.00 rows=10000 width=16) (actual time=10.805..295.360 rows=10000 loops=1)       |
     21Planning Time: 109.157 ms                                                                                                            |
     22JIT:                                                                                                                                 |
     23  Functions: 11                                                                                                                      |
     24  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                      |
     25  Timing: Generation 0.928 ms (Deform 0.553 ms), Inlining 0.000 ms, Optimization 0.593 ms, Emission 10.231 ms, Total 11.753 ms       |
     26Execution Time: 69817.469 ms                                                                                                         |
     27}}}
     28
     29
    3630
    3731ЧЕКОР 2: Додавање индекс
    3832{{{
    39 CREATE INDEX idx_stock_history_stock_timestamp
    40     ON stock_history(stock_id, timestamp);
    41 
    42 EXPLAIN ANALYZE SELECT * FROM stock_daily_returns;
    43 }}}
    44 
    45 {{{
    46 QUERY PLAN                                                                                                                                                                 |
    47 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    48 WindowAgg  (cost=11.58..363447.57 rows=5000000 width=28) (actual time=9.989..29380.492 rows=5000000 loops=1)                                                               |
    49   ->  Index Scan using idx_stock_history_stock_timestamp on stock_history  (cost=0.43..238447.57 rows=5000000 width=20) (actual time=8.605..26054.915 rows=5000000 loops=1)|
    50 Planning Time: 0.381 ms                                                                                                                                                    |
    51 JIT:                                                                                                                                                                       |
    52   Functions: 10                                                                                                                                                            |
    53   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                            |
    54   Timing: Generation 0.946 ms (Deform 0.375 ms), Inlining 0.000 ms, Optimization 0.426 ms, Emission 8.128 ms, Total 9.499 ms                                               |
    55 Execution Time: 29566.095 ms                                                                                                                                               |
    56 }}}
    57 Составен индекс на (stock_id, timestamp) му овозможува на планерот
    58 -- да ги чита редовите веќе сортирани по партиција — без disk sort.
    59 
    60 
    61 подобрување од ~56%.
    62 
    63 
    64 == 2. user_portfolio_value
     33CREATE INDEX idx_trade_request_portfolio_id
     34ON trade_request(portfolio_id);
     35
     36CREATE INDEX idx_trade_request_status
     37ON trade_request(status);
     38
     39CREATE INDEX idx_trade_request_stock_symbol
     40ON trade_request(stock_symbol);
     41
     42CREATE INDEX idx_trade_request_timestamp
     43ON trade_request(timestamp);
     44
     45CREATE INDEX idx_trade_request_portfolio_time
     46ON trade_request(portfolio_id, timestamp);
     47
     48EXPLAIN ANALYZE SELECT * FROM trade_request_full_view;
     49}}}
     50portfolio_id -> се користи во JOIN со portfolios, и без индекс се прави full table scan на 6M редови
     51
     52status, stock_symbol, timestamp -> често се користат за филтрирање на барања
     53
     54(portfolio_id, timestamp) -> ја забрзува комбинацијата JOIN + временски опсег (најчест реален случај)
     55{{{
     56QUERY PLAN                                                                                                                        |
     57----------------------------------------------------------------------------------------------------------------------------------+
     58Hash Join  (cost=298.00..140028.69 rows=6000001 width=59) (actual time=11.636..2551.777 rows=6000001 loops=1)                     |
     59  Hash Cond: (tr.portfolio_id = p.id)                                                                                             |
     60  ->  Seq Scan on trade_request tr  (cost=0.00..123974.01 rows=6000001 width=51) (actual time=0.046..647.179 rows=6000001 loops=1)|
     61  ->  Hash  (cost=173.00..173.00 rows=10000 width=16) (actual time=11.535..11.537 rows=10000 loops=1)                             |
     62        Buckets: 16384  Batches: 1  Memory Usage: 597kB                                                                           |
     63        ->  Seq Scan on portfolios p  (cost=0.00..173.00 rows=10000 width=16) (actual time=8.205..9.753 rows=10000 loops=1)       |
     64Planning Time: 1.095 ms                                                                                                           |
     65JIT:                                                                                                                              |
     66  Functions: 11                                                                                                                   |
     67  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                   |
     68  Timing: Generation 0.720 ms (Deform 0.403 ms), Inlining 0.000 ms, Optimization 0.464 ms, Emission 7.756 ms, Total 8.940 ms      |
     69Execution Time: 2757.489 ms                                                                                                       |
     70}}}
     71
     72
     73
     74
     75побрз JOIN со portfolios
     76помал број на читања од табела (намален I/O)
     77побрзо филтрирање по портфолио и време
     78намалено време на извршување ~ 95% подобрување
     79
     80
     81== 2. stock_history_detail_view
    6582
    6683ЧЕКОР 1: Анализа ПРЕД оптимизација
    6784
    6885{{{
    69 EXPLAIN ANALYZE SELECT * FROM user_portfolio_value;
    70 }}}
    71 
    72 {{{
    73 QUERY PLAN                                                                                                                                                                  |
    74 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    75 Finalize HashAggregate  (cost=56986.39..57086.39 rows=10000 width=16) (actual time=2601.634..2606.630 rows=10000 loops=1)                                                   |
    76   Group Key: u.id                                                                                                                                                           |
    77   Batches: 1  Memory Usage: 1169kB                                                                                                                                          |
    78   ->  Gather  (cost=53736.39..56836.39 rows=30000 width=16) (actual time=2584.041..2593.744 rows=40000 loops=1)                                                             |
    79         Workers Planned: 3                                                                                                                                                  |
    80         Workers Launched: 3                                                                                                                                                 |
    81         ->  Partial HashAggregate  (cost=52736.39..52836.39 rows=10000 width=16) (actual time=2549.798..2552.181 rows=10000 loops=4)                                        |
    82               Group Key: u.id                                                                                                                                               |
    83               Batches: 1  Memory Usage: 1169kB                                                                                                                              |
    84               Worker 0:  Batches: 1  Memory Usage: 1169kB                                                                                                                   |
    85               Worker 1:  Batches: 1  Memory Usage: 1169kB                                                                                                                   |
    86               Worker 2:  Batches: 1  Memory Usage: 1169kB                                                                                                                   |
    87               ->  Hash Join  (cost=668.70..43058.97 rows=967742 width=20) (actual time=9.098..2334.168 rows=750000 loops=4)                                                 |
    88                     Hash Cond: (ph.stock_id = s.id)                                                                                                                         |
    89                     ->  Hash Join  (cost=656.00..40416.22 rows=967742 width=20) (actual time=9.021..2183.366 rows=750000 loops=4)                                           |
    90                           Hash Cond: (p.user_id = u.id)                                                                                                                     |
    91                           ->  Hash Join  (cost=298.00..37516.82 rows=967742 width=20) (actual time=4.529..1983.000 rows=750000 loops=4)                                     |
    92                                 Hash Cond: (ph.portfolio_id = p.id)                                                                                                         |
    93                                 ->  Parallel Seq Scan on portfolio_holdings ph  (cost=0.00..34677.42 rows=967742 width=20) (actual time=0.053..1725.869 rows=750000 loops=4)|
    94                                 ->  Hash  (cost=173.00..173.00 rows=10000 width=16) (actual time=4.350..4.352 rows=10000 loops=4)                                           |
    95                                       Buckets: 16384  Batches: 1  Memory Usage: 597kB                                                                                       |
    96                                       ->  Seq Scan on portfolios p  (cost=0.00..173.00 rows=10000 width=16) (actual time=0.033..2.055 rows=10000 loops=4)                   |
    97                           ->  Hash  (cost=233.00..233.00 rows=10000 width=8) (actual time=4.367..4.368 rows=10000 loops=4)                                                  |
    98                                 Buckets: 16384  Batches: 1  Memory Usage: 519kB                                                                                             |
    99                                 ->  Seq Scan on users u  (cost=0.00..233.00 rows=10000 width=8) (actual time=0.041..2.268 rows=10000 loops=4)                               |
    100                     ->  Hash  (cost=11.20..11.20 rows=120 width=16) (actual time=0.047..0.048 rows=8 loops=4)                                                               |
    101                           Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                      |
    102                           ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=16) (actual time=0.036..0.039 rows=8 loops=4)                                           |
    103 Planning Time: 0.782 ms                                                                                                                                                     |
    104 Execution Time: 2607.235 ms                                                                                                                                                 |
    105 }}}
    106 
    107 Главното тесно грло е Seq Scan на portfolio_holdings без индекс
    108 
    109 idx_portfolio_holdings_portfolio_id е најважен —
    110 -- го забрзува Hash Join помеѓу portfolio_holdings и portfolios.
    111 
    112 {{{
    113 CREATE INDEX idx_portfolios_user_id ON portfolios(user_id);
    114 CREATE INDEX idx_portfolio_holdings_portfolio_id ON portfolio_holdings(portfolio_id);
    115 CREATE INDEX idx_portfolio_holdings_stock_id ON portfolio_holdings(stock_id);
    116 
    117 EXPLAIN ANALYZE SELECT * FROM user_portfolio_value;
    118 
    119 }}}
    120 
    121 
    122 {{{
    123 QUERY PLAN                                                                                                                                                                 |
    124 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    125 Finalize HashAggregate  (cost=56986.39..57086.39 rows=10000 width=16) (actual time=1055.435..1062.245 rows=10000 loops=1)                                                  |
    126   Group Key: u.id                                                                                                                                                          |
    127   Batches: 1  Memory Usage: 1169kB                                                                                                                                         |
    128   ->  Gather  (cost=53736.39..56836.39 rows=30000 width=16) (actual time=1037.811..1049.560 rows=40000 loops=1)                                                            |
    129         Workers Planned: 3                                                                                                                                                 |
    130         Workers Launched: 3                                                                                                                                                |
    131         ->  Partial HashAggregate  (cost=52736.39..52836.39 rows=10000 width=16) (actual time=996.756..999.287 rows=10000 loops=4)                                         |
    132               Group Key: u.id                                                                                                                                              |
    133               Batches: 1  Memory Usage: 1169kB                                                                                                                             |
    134               Worker 0:  Batches: 1  Memory Usage: 1169kB                                                                                                                  |
    135               Worker 1:  Batches: 1  Memory Usage: 1169kB                                                                                                                  |
    136               Worker 2:  Batches: 1  Memory Usage: 1169kB                                                                                                                  |
    137               ->  Hash Join  (cost=668.70..43058.97 rows=967742 width=20) (actual time=9.464..771.257 rows=750000 loops=4)                                                 |
    138                     Hash Cond: (ph.stock_id = s.id)                                                                                                                        |
    139                     ->  Hash Join  (cost=656.00..40416.22 rows=967742 width=20) (actual time=9.384..619.960 rows=750000 loops=4)                                           |
    140                           Hash Cond: (p.user_id = u.id)                                                                                                                    |
    141                           ->  Hash Join  (cost=298.00..37516.82 rows=967742 width=20) (actual time=4.641..408.247 rows=750000 loops=4)                                     |
    142                                 Hash Cond: (ph.portfolio_id = p.id)                                                                                                        |
    143                                 ->  Parallel Seq Scan on portfolio_holdings ph  (cost=0.00..34677.42 rows=967742 width=20) (actual time=0.068..141.061 rows=750000 loops=4)|
    144                                 ->  Hash  (cost=173.00..173.00 rows=10000 width=16) (actual time=4.430..4.431 rows=10000 loops=4)                                          |
    145                                       Buckets: 16384  Batches: 1  Memory Usage: 597kB                                                                                      |
    146                                       ->  Seq Scan on portfolios p  (cost=0.00..173.00 rows=10000 width=16) (actual time=0.036..2.060 rows=10000 loops=4)                  |
    147                           ->  Hash  (cost=233.00..233.00 rows=10000 width=8) (actual time=4.601..4.602 rows=10000 loops=4)                                                 |
    148                                 Buckets: 16384  Batches: 1  Memory Usage: 519kB                                                                                            |
    149                                 ->  Seq Scan on users u  (cost=0.00..233.00 rows=10000 width=8) (actual time=0.044..2.466 rows=10000 loops=4)                              |
    150                     ->  Hash  (cost=11.20..11.20 rows=120 width=16) (actual time=0.053..0.054 rows=8 loops=4)                                                              |
    151                           Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                     |
    152                           ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=16) (actual time=0.041..0.044 rows=8 loops=4)                                          |
    153 Planning Time: 1.564 ms                                                                                                                                                    |
    154 Execution Time: 1062.862 ms                                                                                                                                                |
     86EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view;
     87}}}
     88
     89{{{
     90QUERY PLAN                                                                                                                          |
     91------------------------------------------------------------------------------------------------------------------------------------+
     92Hash Join  (cost=12.70..100366.25 rows=5000000 width=602) (actual time=48.972..61018.409 rows=5000000 loops=1)                      |
     93  Hash Cond: (sh.stock_id = s.id)                                                                                                   |
     94  ->  Seq Scan on stock_history sh  (cost=0.00..86765.00 rows=5000000 width=28) (actual time=40.008..59831.608 rows=5000000 loops=1)|
     95  ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=8.930..8.932 rows=8 loops=1)                                        |
     96        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                |
     97        ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=8.906..8.910 rows=8 loops=1)                    |
     98Planning Time: 11.988 ms                                                                                                            |
     99JIT:                                                                                                                                |
     100  Functions: 11                                                                                                                     |
     101  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                     |
     102  Timing: Generation 0.748 ms (Deform 0.432 ms), Inlining 0.000 ms, Optimization 0.454 ms, Emission 8.463 ms, Total 9.666 ms        |
     103Execution Time: 61188.242 ms                                                                                                        |
     104}}}
     105
     106
     107ЧЕКОР 2: Додавање индекси
     108{{{
     109CREATE INDEX idx_stock_history_stock_id
     110ON stock_history(stock_id);
     111
     112CREATE INDEX idx_stock_history_timestamp
     113ON stock_history(timestamp);
     114
     115CREATE INDEX idx_stock_history_stock_time
     116ON stock_history(stock_id, timestamp);
     117
     118EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view;
     119}}}
     120
     121stock_id - клучен за JOIN со stock, без него се скенираат ~5M редови
     122
     123timestamp - овозможува побрзи временски филтри (price history queries)
     124
     125(stock_id, timestamp) - најважен индекс за time-series податоци
     126
     127{{{
     128QUERY PLAN                                                                                                                       |
     129---------------------------------------------------------------------------------------------------------------------------------+
     130Hash Join  (cost=12.70..100366.25 rows=5000000 width=602) (actual time=9.110..1708.148 rows=5000000 loops=1)                     |
     131  Hash Cond: (sh.stock_id = s.id)                                                                                                |
     132  ->  Seq Scan on stock_history sh  (cost=0.00..86765.00 rows=5000000 width=28) (actual time=0.043..550.016 rows=5000000 loops=1)|
     133  ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=9.039..9.041 rows=8 loops=1)                                     |
     134        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                             |
     135        ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=9.014..9.017 rows=8 loops=1)                 |
     136Planning Time: 0.676 ms                                                                                                          |
     137JIT:                                                                                                                             |
     138  Functions: 11                                                                                                                  |
     139  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                  |
     140  Timing: Generation 0.753 ms (Deform 0.421 ms), Inlining 0.000 ms, Optimization 0.481 ms, Emission 8.545 ms, Total 9.779 ms     |
     141Execution Time: 1877.318 ms                                                                                                      |
    155142}}}
    156143
    157144РЕЗУЛТАТ:
    158 Execution Time: 1062.862 ms (~1.06s) — подобрување од ~59%
    159 Hash Join структурата е иста но Seq Scan на portfolio_holdings
    160 е значително побрз (141ms наместо 1725ms по worker)
    161 users и portfolios се мали табели, Seq Scan е оптимален за нив
    162 stock табелата има само 8 редови — индекс не е потребен
    163 
    164 
    165 == 3. most_traded_stocks
     145
     146побрз JOIN со stock табела
     147значително намалено време за читање на историски податоци
     148подобра перформанса за филтрирање по акција + време
     149~ 96-97% подобрување
     150
     151
     152== 3. trade_transaction_detail_view
    166153
    167154ЧЕКОР 1: Анализа ПРЕД оптимизација
    168155
    169156{{{
    170 EXPLAIN ANALYZE SELECT * FROM most_traded_stocks;
    171 }}}
    172 
    173 {{{
    174 QUERY PLAN                                                                                                                                                         |
    175 -------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    176 Sort  (cost=35030.93..35030.95 rows=8 width=24) (actual time=46549.599..46564.487 rows=8 loops=1)                                                                  |
    177   Sort Key: (count(*)) DESC                                                                                                                                        |
    178   Sort Method: quicksort  Memory: 25kB                                                                                                                             |
    179   ->  Finalize GroupAggregate  (cost=35027.71..35030.81 rows=8 width=24) (actual time=46549.582..46564.479 rows=8 loops=1)                                         |
    180         Group Key: trade_transaction.stock_id                                                                                                                      |
    181         ->  Gather Merge  (cost=35027.71..35030.55 rows=24 width=24) (actual time=46549.571..46564.462 rows=32 loops=1)                                            |
    182               Workers Planned: 3                                                                                                                                   |
    183               Workers Launched: 3                                                                                                                                  |
    184               ->  Sort  (cost=34027.67..34027.69 rows=8 width=24) (actual time=46512.772..46512.774 rows=8 loops=4)                                                |
    185                     Sort Key: trade_transaction.stock_id                                                                                                           |
    186                     Sort Method: quicksort  Memory: 25kB                                                                                                           |
    187                     Worker 0:  Sort Method: quicksort  Memory: 25kB                                                                                                |
    188                     Worker 1:  Sort Method: quicksort  Memory: 25kB                                                                                                |
    189                     Worker 2:  Sort Method: quicksort  Memory: 25kB                                                                                                |
    190                     ->  Partial HashAggregate  (cost=34027.47..34027.55 rows=8 width=24) (actual time=46512.711..46512.715 rows=8 loops=4)                         |
    191                           Group Key: trade_transaction.stock_id                                                                                                    |
    192                           Batches: 1  Memory Usage: 24kB                                                                                                           |
    193                           Worker 0:  Batches: 1  Memory Usage: 24kB                                                                                                |
    194                           Worker 1:  Batches: 1  Memory Usage: 24kB                                                                                                |
    195                           Worker 2:  Batches: 1  Memory Usage: 24kB                                                                                                |
    196                           ->  Parallel Seq Scan on trade_transaction  (cost=0.00..29187.41 rows=645341 width=12) (actual time=0.060..46388.890 rows=500139 loops=4)|
    197 Planning Time: 1.646 ms                                                                                                                                            |
    198 Execution Time: 46564.560 ms                                                                                                                                       |
    199 }}}
    200 
    201 
    202 Составниот индекс (stock_id, quantity) овозможува планерот да ги чита вредностите директно од индексот
    203 
    204 {{{
    205 CREATE INDEX idx_trade_transaction_stock_id ON trade_transaction(stock_id);
    206 CREATE INDEX idx_trade_transaction_stock_qty ON trade_transaction(stock_id, quantity);
    207 
    208 
    209 EXPLAIN ANALYZE SELECT * FROM most_traded_stocks;
    210 }}}
    211 
    212 
    213 {{{
    214 QUERY PLAN                                                                                                                                                      |
    215 ----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    216 Sort  (cost=35030.93..35030.95 rows=8 width=24) (actual time=231.585..255.074 rows=8 loops=1)                                                                   |
    217   Sort Key: (count(*)) DESC                                                                                                                                     |
    218   Sort Method: quicksort  Memory: 25kB                                                                                                                          |
    219   ->  Finalize GroupAggregate  (cost=35027.71..35030.81 rows=8 width=24) (actual time=231.566..255.064 rows=8 loops=1)                                          |
    220         Group Key: trade_transaction.stock_id                                                                                                                   |
    221         ->  Gather Merge  (cost=35027.71..35030.55 rows=24 width=24) (actual time=231.555..255.048 rows=32 loops=1)                                             |
    222               Workers Planned: 3                                                                                                                                |
    223               Workers Launched: 3                                                                                                                               |
    224               ->  Sort  (cost=34027.67..34027.69 rows=8 width=24) (actual time=197.246..197.248 rows=8 loops=4)                                                 |
    225                     Sort Key: trade_transaction.stock_id                                                                                                        |
    226                     Sort Method: quicksort  Memory: 25kB                                                                                                        |
    227                     Worker 0:  Sort Method: quicksort  Memory: 25kB                                                                                             |
    228                     Worker 1:  Sort Method: quicksort  Memory: 25kB                                                                                             |
    229                     Worker 2:  Sort Method: quicksort  Memory: 25kB                                                                                             |
    230                     ->  Partial HashAggregate  (cost=34027.47..34027.55 rows=8 width=24) (actual time=197.191..197.194 rows=8 loops=4)                          |
    231                           Group Key: trade_transaction.stock_id                                                                                                 |
    232                           Batches: 1  Memory Usage: 24kB                                                                                                        |
    233                           Worker 0:  Batches: 1  Memory Usage: 24kB                                                                                             |
    234                           Worker 1:  Batches: 1  Memory Usage: 24kB                                                                                             |
    235                           Worker 2:  Batches: 1  Memory Usage: 24kB                                                                                             |
    236                           ->  Parallel Seq Scan on trade_transaction  (cost=0.00..29187.41 rows=645341 width=12) (actual time=0.031..78.217 rows=500139 loops=4)|
    237 Planning Time: 0.538 ms                                                                                                                                         |
    238 Execution Time: 255.150 ms                                                                                                                                      |
     157EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view;
     158}}}
     159
     160{{{
     161QUERY PLAN                                                                                                                           |
     162-------------------------------------------------------------------------------------------------------------------------------------+
     163Hash Join  (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.057..771.499 rows=2000557 loops=1)                           |
     164  Hash Cond: (tt.stock_id = s.id)                                                                                                    |
     165  ->  Seq Scan on trade_transaction tt  (cost=0.00..42739.57 rows=2000557 width=57) (actual time=0.026..221.291 rows=2000557 loops=1)|
     166  ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=0.018..0.019 rows=8 loops=1)                                         |
     167        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                 |
     168        ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.012 rows=8 loops=1)                     |
     169Planning Time: 0.220 ms                                                                                                              |
     170Execution Time: 842.067 ms                                                                                                           |
     171}}}
     172
     173
     174
     175
     176{{{
     177CREATE INDEX idx_trade_transaction_stock_id
     178ON trade_transaction(stock_id);
     179
     180CREATE INDEX idx_trade_transaction_user_id
     181ON trade_transaction(user_id);
     182
     183CREATE INDEX idx_trade_transaction_timestamp
     184ON trade_transaction(timestamp);
     185
     186CREATE INDEX idx_trade_transaction_stock_time
     187ON trade_transaction(stock_id, timestamp);
     188
     189
     190EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view;
     191}}}
     192
     193stock_id - JOIN со stock, ја избегнува целосна проверка на 2M редови
     194
     195user_id - овозможува брзи user-based query филтри
     196
     197timestamp - подобрува временски опсег на пребарување
     198
     199(stock_id, timestamp) - најдобар за комбинирани филтри (акција + време)
     200
     201{{{
     202QUERY PLAN                                                                                                                           |
     203-------------------------------------------------------------------------------------------------------------------------------------+
     204Hash Join  (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.046..766.811 rows=2000557 loops=1)                           |
     205  Hash Cond: (tt.stock_id = s.id)                                                                                                    |
     206  ->  Seq Scan on trade_transaction tt  (cost=0.00..42739.57 rows=2000557 width=57) (actual time=0.018..214.890 rows=2000557 loops=1)|
     207  ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=0.017..0.018 rows=8 loops=1)                                         |
     208        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                 |
     209        ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.011 rows=8 loops=1)                     |
     210Planning Time: 0.645 ms                                                                                                              |
     211Execution Time: 837.658 ms                                                                                                           |
     212     
    239213}}}
    240214
    241215РЕЗУЛТАТ:
    242 Execution Time: 255.150 ms (~0.25s) — подобрување од ~99%
    243 Seq Scan на trade_transaction сега трае само 78ms (наместо 46s)
    244 Планерот сè уште користи Parallel Seq Scan но многу побрзо
    245 бидејќи индексот го подобрува пристапот до податоците
    246 
    247 === 4. user_realized_pnl
    248 
    249 ЧЕКОР 1: Анализа ПРЕД оптимизација
    250 
    251 {{{
    252 EXPLAIN ANALYZE SELECT * FROM user_realized_pnl;
    253 }}}
    254 
    255 
    256 {{{
    257 QUERY PLAN                                                                                                                                           |
    258 -----------------------------------------------------------------------------------------------------------------------------------------------------+
    259 Finalize HashAggregate  (cost=47954.01..48053.90 rows=9989 width=16) (actual time=377.812..384.892 rows=10000 loops=1)                               |
    260   Group Key: trade_transaction.user_id                                                                                                               |
    261   Batches: 1  Memory Usage: 1169kB                                                                                                                   |
    262   ->  Gather  (cost=44707.58..47804.17 rows=29967 width=16) (actual time=351.042..365.703 rows=40000 loops=1)                                        |
    263         Workers Planned: 3                                                                                                                           |
    264         Workers Launched: 3                                                                                                                          |
    265         ->  Partial HashAggregate  (cost=43707.58..43807.47 rows=9989 width=16) (actual time=307.706..310.296 rows=10000 loops=4)                    |
    266               Group Key: trade_transaction.user_id                                                                                                   |
    267               Batches: 1  Memory Usage: 1169kB                                                                                                       |
    268               Worker 0:  Batches: 1  Memory Usage: 1169kB                                                                                            |
    269               Worker 1:  Batches: 1  Memory Usage: 1169kB                                                                                            |
    270               Worker 2:  Batches: 1  Memory Usage: 1169kB                                                                                            |
    271               ->  Parallel Seq Scan on trade_transaction  (cost=0.00..29187.41 rows=645341 width=24) (actual time=0.057..130.515 rows=500139 loops=4)|
    272 Planning Time: 0.441 ms                                                                                                                              |
    273 Execution Time: 385.469 ms                                                                                                                           |
    274 }}}
    275 Релативно прифатливо но може да се подобри
    276 
    277 {{{
    278 CREATE INDEX idx_trade_transaction_user_id ON trade_transaction(user_id);
    279 CREATE INDEX idx_trade_transaction_user_pnl ON trade_transaction(user_id, type, price, quantity);
    280 
    281 
    282 
    283 EXPLAIN ANALYZE SELECT * FROM user_realized_pnl;
    284 }}}
    285 
    286 {{{
    287 QUERY PLAN                                                                                                                                           |
    288 -----------------------------------------------------------------------------------------------------------------------------------------------------+
    289 Finalize HashAggregate  (cost=47954.01..48053.90 rows=9989 width=16) (actual time=342.192..348.262 rows=10000 loops=1)                               |
    290   Group Key: trade_transaction.user_id                                                                                                               |
    291   Batches: 1  Memory Usage: 1169kB                                                                                                                   |
    292   ->  Gather  (cost=44707.58..47804.17 rows=29967 width=16) (actual time=324.379..335.174 rows=40000 loops=1)                                        |
    293         Workers Planned: 3                                                                                                                           |
    294         Workers Launched: 3                                                                                                                          |
    295         ->  Partial HashAggregate  (cost=43707.58..43807.47 rows=9989 width=16) (actual time=284.929..287.364 rows=10000 loops=4)                    |
    296               Group Key: trade_transaction.user_id                                                                                                   |
    297               Batches: 1  Memory Usage: 1169kB                                                                                                       |
    298               Worker 0:  Batches: 1  Memory Usage: 1169kB                                                                                            |
    299               Worker 1:  Batches: 1  Memory Usage: 1169kB                                                                                            |
    300               Worker 2:  Batches: 1  Memory Usage: 1169kB                                                                                            |
    301               ->  Parallel Seq Scan on trade_transaction  (cost=0.00..29187.41 rows=645341 width=24) (actual time=0.070..107.283 rows=500139 loops=4)|
    302 Planning Time: 0.465 ms                                                                                                                              |
    303 Execution Time: 113.838 ms                                                                                                                           |
    304 }}}
    305 
    306 Индексите помагаат при филтрирање по конкретен user_id, подобрување од ~70%.
     216многу мала разлика во EXPLAIN