Changes between Version 4 and Version 5 of QueryOptimization


Ignore:
Timestamp:
05/21/26 02:41:13 (5 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v4 v5  
    7171Од 116765.182 ms , до сега 297.480 ms.
    7272
    73 == 2. stock_history_detail_view
     73== 2. trade_transaction_detail_view
    7474
    7575ЧЕКОР 1: Анализа ПРЕД оптимизација
    7676
    7777{{{
    78 EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view;
    79 }}}
    80 
    81 {{{
    82 QUERY PLAN                                                                                                                          |
    83 ------------------------------------------------------------------------------------------------------------------------------------+
    84 Hash Join  (cost=12.70..100366.25 rows=5000000 width=602) (actual time=48.972..61018.409 rows=5000000 loops=1)                      |
    85   Hash Cond: (sh.stock_id = s.id)                                                                                                   |
    86   ->  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)|
    87   ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=8.930..8.932 rows=8 loops=1)                                        |
    88         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                |
    89         ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=8.906..8.910 rows=8 loops=1)                    |
    90 Planning Time: 11.988 ms                                                                                                            |
    91 JIT:                                                                                                                                |
    92   Functions: 11                                                                                                                     |
    93   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                     |
    94   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        |
    95 Execution Time: 61188.242 ms                                                                                                        |
    96 }}}
    97 
     78EXPLAIN ANALYZE
     79SELECT *
     80FROM trade_transaction_detail_view
     81WHERE user_id = 5
     82  AND timestamp >= NOW() - INTERVAL '30 days';
     83}}}
     84
     85{{{
     86QUERY PLAN                                                                                                                                      |
     87------------------------------------------------------------------------------------------------------------------------------------------------+
     88Gather  (cost=1000.15..36665.94 rows=200 width=631) (actual time=19426.122..41473.240 rows=193 loops=1)                                         |
     89  Workers Planned: 3                                                                                                                            |
     90  Workers Launched: 3                                                                                                                           |
     91  ->  Nested Loop  (cost=0.15..35645.94 rows=65 width=631) (actual time=19563.006..41397.006 rows=48 loops=4)                                   |
     92        ->  Parallel Seq Scan on trade_transaction tt  (cost=0.00..35640.82 rows=65 width=57) (actual time=19535.576..41368.453 rows=48 loops=4)|
     93              Filter: ((user_id = 5) AND ("timestamp" >= (now() - '30 days'::interval)))                                                        |
     94              Rows Removed by Filter: 500091                                                                                                    |
     95        ->  Memoize  (cost=0.15..0.41 rows=1 width=582) (actual time=0.581..0.581 rows=1 loops=193)                                             |
     96              Cache Key: tt.stock_id                                                                                                            |
     97              Cache Mode: logical                                                                                                               |
     98              Hits: 38  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                                |
     99              Worker 0:  Hits: 26  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                     |
     100              Worker 1:  Hits: 55  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                     |
     101              Worker 2:  Hits: 42  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                     |
     102              ->  Index Scan using stock_pkey on stock s  (cost=0.14..0.40 rows=1 width=582) (actual time=3.448..3.448 rows=1 loops=32)         |
     103                    Index Cond: (id = tt.stock_id)                                                                                              |
     104Planning Time: 84.447 ms                                                                                                                        |
     105Execution Time: 41473.375 ms                                                                                                                    |
     106}}}
     107
     108Parallel Seq Scan на trade_transaction
     109join со stock
     110филтер се применува на крај (after scan)
     111читање на ~2M редови
    98112
    99113ЧЕКОР 2: Додавање индекси
     114{{{
     115CREATE INDEX idx_trade_transaction_user_id
     116ON trade_transaction(user_id);
     117
     118
     119CREATE INDEX idx_trade_transaction_user_time
     120ON trade_transaction(user_id, timestamp);
     121
     122
     123EXPLAIN ANALYZE
     124SELECT *
     125FROM trade_transaction_detail_view
     126WHERE user_id = 5
     127  AND timestamp >= NOW() - INTERVAL '30 days';
     128}}}
     129
     130idx_trade_transaction_user_id, директно го намалува scan на 2M редови.
     131
     132idx_trade_transaction_user_time ова е најважниот индекс за овој view бидејќи:
     133прво филтрира user
     134потоа временски range (30 days)
     135
     136{{{
     137QUERY PLAN                                                                                                                                    |
     138----------------------------------------------------------------------------------------------------------------------------------------------+
     139Nested Loop  (cost=6.13..762.23 rows=200 width=631) (actual time=0.372..1.219 rows=193 loops=1)                                               |
     140  ->  Bitmap Heap Scan on trade_transaction tt  (cost=5.98..753.70 rows=200 width=57) (actual time=0.348..1.061 rows=193 loops=1)             |
     141        Recheck Cond: (user_id = 5)                                                                                                           |
     142        Filter: ("timestamp" >= (now() - '30 days'::interval))                                                                                |
     143        Heap Blocks: exact=191                                                                                                                |
     144        ->  Bitmap Index Scan on idx_trade_transaction_user_id  (cost=0.00..5.93 rows=200 width=0) (actual time=0.307..0.308 rows=193 loops=1)|
     145              Index Cond: (user_id = 5)                                                                                                       |
     146  ->  Memoize  (cost=0.15..0.41 rows=1 width=582) (actual time=0.000..0.000 rows=1 loops=193)                                                 |
     147        Cache Key: tt.stock_id                                                                                                                |
     148        Cache Mode: logical                                                                                                                   |
     149        Hits: 185  Misses: 8  Evictions: 0  Overflows: 0  Memory Usage: 2kB                                                                   |
     150        ->  Index Scan using stock_pkey on stock s  (cost=0.14..0.40 rows=1 width=582) (actual time=0.003..0.003 rows=1 loops=8)              |
     151              Index Cond: (id = tt.stock_id)                                                                                                  |
     152Planning Time: 0.605 ms                                                                                                                       |
     153Execution Time: 1.272 ms                                                                                                                      |
     154}}}
     155
     156РЕЗУЛТАТ:
     15741473.375 ms  ->1.272 ms  .
     158
     159За оптимизација на trade_transaction_detail_view се користи реален филтриран query по user_id и временски интервал. Без индекси, PostgreSQL извршува sequential scan над голем број записи, што резултира со високо време на извршување. Со додавање на индекс над user_id и composite индекс (user_id, timestamp) се овозможува значително намалување на бројот на скенирани редови и подобрување на перформансите за user-specific queries.
     160
     161== 3. stock_history_detail_view
     162
     163ЧЕКОР 1: Анализа ПРЕД оптимизација
     164
     165{{{
     166EXPLAIN ANALYZE
     167SELECT *
     168FROM stock_history_detail_view
     169WHERE stock_id = 3
     170  AND timestamp >= NOW() - INTERVAL '7 days';
     171}}}
     172
     173{{{
     174QUERY PLAN                                                                                                                                 |
     175-------------------------------------------------------------------------------------------------------------------------------------------+
     176Nested Loop  (cost=1000.14..62780.97 rows=71 width=602) (actual time=21251.698..21257.102 rows=0 loops=1)                                  |
     177  ->  Index Scan using stock_pkey on stock s  (cost=0.14..8.16 rows=1 width=582) (actual time=0.034..0.041 rows=1 loops=1)                 |
     178        Index Cond: (id = 3)                                                                                                               |
     179  ->  Gather  (cost=1000.00..62772.10 rows=71 width=28) (actual time=21251.654..21257.057 rows=0 loops=1)                                  |
     180        Workers Planned: 4                                                                                                                 |
     181        Workers Launched: 4                                                                                                                |
     182        ->  Parallel Seq Scan on stock_history sh  (cost=0.00..61765.00 rows=18 width=28) (actual time=21211.054..21211.055 rows=0 loops=5)|
     183              Filter: ((stock_id = 3) AND ("timestamp" >= (now() - '7 days'::interval)))                                                   |
     184              Rows Removed by Filter: 1000000                                                                                              |
     185Planning Time: 0.231 ms                                                                                                                    |
     186Execution Time: 21257.143 ms                                                                                                               |
     187}}}
     188
     189Seq Scan на 5M rows (stock_history)
     190join со stock
     191филтер по време се прави после scan
     192
     193
     194
    100195{{{
    101196CREATE INDEX idx_stock_history_stock_id
    102197ON stock_history(stock_id);
    103198
    104 CREATE INDEX idx_stock_history_timestamp
    105 ON stock_history(timestamp);
    106199
    107200CREATE INDEX idx_stock_history_stock_time
    108201ON stock_history(stock_id, timestamp);
    109202
    110 EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view;
    111 }}}
    112 
    113 stock_id - клучен за JOIN со stock, без него се скенираат ~5M редови
    114 
    115 timestamp - овозможува побрзи временски филтри (price history queries)
    116 
    117 (stock_id, timestamp) - најважен индекс за time-series податоци
    118 
    119 {{{
    120 QUERY PLAN                                                                                                                       |
    121 ---------------------------------------------------------------------------------------------------------------------------------+
    122 Hash Join  (cost=12.70..100366.25 rows=5000000 width=602) (actual time=9.110..1708.148 rows=5000000 loops=1)                     |
    123   Hash Cond: (sh.stock_id = s.id)                                                                                                |
    124   ->  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)|
    125   ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=9.039..9.041 rows=8 loops=1)                                     |
    126         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                             |
    127         ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=9.014..9.017 rows=8 loops=1)                 |
    128 Planning Time: 0.676 ms                                                                                                          |
    129 JIT:                                                                                                                             |
    130   Functions: 11                                                                                                                  |
    131   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                  |
    132   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     |
    133 Execution Time: 1877.318 ms                                                                                                      |
     203
     204EXPLAIN ANALYZE
     205SELECT *
     206FROM stock_history_detail_view
     207WHERE stock_id = 3
     208  AND timestamp >= NOW() - INTERVAL '7 days';
     209}}}
     210
     211
     212{{{
     213 QUERY PLAN                                                                                                                                |
     214------------------------------------------------------------------------------------------------------------------------------------------+
     215Nested Loop  (cost=5.31..290.09 rows=71 width=602) (actual time=0.096..0.097 rows=0 loops=1)                                              |
     216  ->  Index Scan using stock_pkey on stock s  (cost=0.14..8.16 rows=1 width=582) (actual time=0.014..0.015 rows=1 loops=1)                |
     217        Index Cond: (id = 3)                                                                                                              |
     218  ->  Bitmap Heap Scan on stock_history sh  (cost=5.17..281.22 rows=71 width=28) (actual time=0.076..0.077 rows=0 loops=1)                |
     219        Recheck Cond: ((stock_id = 3) AND ("timestamp" >= (now() - '7 days'::interval)))                                                  |
     220        ->  Bitmap Index Scan on idx_stock_history_stock_time  (cost=0.00..5.15 rows=71 width=0) (actual time=0.071..0.071 rows=0 loops=1)|
     221              Index Cond: ((stock_id = 3) AND ("timestamp" >= (now() - '7 days'::interval)))                                              |
     222Planning Time: 0.642 ms                                                                                                                   |
     223Execution Time: 0.133 ms                                                                                                                  |
    134224}}}
    135225
    136226РЕЗУЛТАТ:
    137 
    138 побрз JOIN со stock табела
    139 значително намалено време за читање на историски податоци
    140 подобра перформанса за филтрирање по акција + време
    141 ~ 96-97% подобрување
    142 
    143 
    144 == 3. trade_transaction_detail_view
    145 
    146 ЧЕКОР 1: Анализа ПРЕД оптимизација
    147 
    148 {{{
    149 EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view;
    150 }}}
    151 
    152 {{{
    153 QUERY PLAN                                                                                                                           |
    154 -------------------------------------------------------------------------------------------------------------------------------------+
    155 Hash Join  (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.057..771.499 rows=2000557 loops=1)                           |
    156   Hash Cond: (tt.stock_id = s.id)                                                                                                    |
    157   ->  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)|
    158   ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=0.018..0.019 rows=8 loops=1)                                         |
    159         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                 |
    160         ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.012 rows=8 loops=1)                     |
    161 Planning Time: 0.220 ms                                                                                                              |
    162 Execution Time: 842.067 ms                                                                                                           |
    163 }}}
    164 
    165 
    166 
    167 
    168 {{{
    169 CREATE INDEX idx_trade_transaction_stock_id
    170 ON trade_transaction(stock_id);
    171 
    172 CREATE INDEX idx_trade_transaction_user_id
    173 ON trade_transaction(user_id);
    174 
    175 CREATE INDEX idx_trade_transaction_timestamp
    176 ON trade_transaction(timestamp);
    177 
    178 CREATE INDEX idx_trade_transaction_stock_time
    179 ON trade_transaction(stock_id, timestamp);
    180 
    181 
    182 EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view;
    183 }}}
    184 
    185 stock_id - JOIN со stock, ја избегнува целосна проверка на 2M редови
    186 
    187 user_id - овозможува брзи user-based query филтри
    188 
    189 timestamp - подобрува временски опсег на пребарување
    190 
    191 (stock_id, timestamp) - најдобар за комбинирани филтри (акција + време)
    192 
    193 {{{
    194 QUERY PLAN                                                                                                                           |
    195 -------------------------------------------------------------------------------------------------------------------------------------+
    196 Hash Join  (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.046..766.811 rows=2000557 loops=1)                           |
    197   Hash Cond: (tt.stock_id = s.id)                                                                                                    |
    198   ->  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)|
    199   ->  Hash  (cost=11.20..11.20 rows=120 width=582) (actual time=0.017..0.018 rows=8 loops=1)                                         |
    200         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                 |
    201         ->  Seq Scan on stock s  (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.011 rows=8 loops=1)                     |
    202 Planning Time: 0.645 ms                                                                                                              |
    203 Execution Time: 837.658 ms                                                                                                           |
    204      
    205 }}}
    206 
    207 РЕЗУЛТАТ:
    208 многу мала разлика во EXPLAIN
     22721257.143 ms  -> 0.133 ms   
     228
     229При анализа на stock_history_detail_view се користи филтрирање по stock_id и временски опсег. Првичниот план покажува sequential scan над голема количина податоци. Со додавање на индекс над stock_id и composite индекс (stock_id, timestamp) се оптимизира пристапот за time-series податоци, што е критично за приказ на графикони и историски цени.
     230
     231