Changes between Version 2 and Version 3 of QueryOptimization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v2 v3  
    77{{{
    88EXPLAIN ANALYZE SELECT * FROM trade_request_full_view;
    9 }}}
    10 
    11 
    12 {{{
    13 QUERY PLAN                                                                                                                           |
    14 -------------------------------------------------------------------------------------------------------------------------------------+
    15 Hash 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)       |
    21 Planning Time: 109.157 ms                                                                                                            |
    22 JIT:                                                                                                                                 |
    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       |
    26 Execution Time: 69817.469 ms                                                                                                         |
     9
     10
     11EXPLAIN ANALYZE
     12SELECT *
     13FROM trade_request_full_view
     14WHERE user_id = 5;
     15}}}
     16
     17
     18{{{
     19QUERY PLAN                                                                                                                                          |
     20----------------------------------------------------------------------------------------------------------------------------------------------------+
     21Gather  (cost=1198.01..84171.18 rows=600 width=59) (actual time=2.369..116765.024 rows=592 loops=1)                                                 |
     22  Workers Planned: 4                                                                                                                                |
     23  Workers Launched: 4                                                                                                                               |
     24  ->  Hash Join  (cost=198.01..83111.18 rows=150 width=59) (actual time=1109.166..116211.985 rows=118 loops=5)                                      |
     25        Hash Cond: (tr.portfolio_id = p.id)                                                                                                         |
     26        ->  Parallel Seq Scan on trade_request tr  (cost=0.00..78974.00 rows=1500000 width=51) (actual time=19.681..116089.214 rows=1200000 loops=5)|
     27        ->  Hash  (cost=198.00..198.00 rows=1 width=16) (actual time=1.379..1.381 rows=1 loops=5)                                                   |
     28              Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                          |
     29              ->  Seq Scan on portfolios p  (cost=0.00..198.00 rows=1 width=16) (actual time=0.047..1.373 rows=1 loops=5)                           |
     30                    Filter: (user_id = 5)                                                                                                           |
     31                    Rows Removed by Filter: 9999                                                                                                    |
     32Planning Time: 0.348 ms                                                                                                                             |
     33Execution Time: 116765.182 ms                                                                                                                       |
     34
    2735}}}
    2836
    … …  
    3139ЧЕКОР 2: Додавање индекс
    3240{{{
    33 CREATE INDEX idx_trade_request_portfolio_id
    34 ON trade_request(portfolio_id);
    35 
    36 CREATE INDEX idx_trade_request_status
    37 ON trade_request(status);
    38 
    39 CREATE INDEX idx_trade_request_stock_symbol
    40 ON trade_request(stock_symbol);
    41 
    42 CREATE INDEX idx_trade_request_timestamp
    43 ON trade_request(timestamp);
    44 
    45 CREATE INDEX idx_trade_request_portfolio_time
    46 ON trade_request(portfolio_id, timestamp);
    47 
    48 EXPLAIN ANALYZE SELECT * FROM trade_request_full_view;
    49 }}}
    50 portfolio_id -> се користи во JOIN со portfolios, и без индекс се прави full table scan на 6M редови
    51 
    52 status, stock_symbol, timestamp -> често се користат за филтрирање на барања
    53 
    54 (portfolio_id, timestamp) -> ја забрзува комбинацијата JOIN + временски опсег (најчест реален случај)
    55 {{{
    56 QUERY PLAN                                                                                                                        |
    57 ----------------------------------------------------------------------------------------------------------------------------------+
    58 Hash 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)       |
    64 Planning Time: 1.095 ms                                                                                                           |
    65 JIT:                                                                                                                              |
    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      |
    69 Execution Time: 2757.489 ms                                                                                                       |
    70 }}}
    71 
    72 
    73 
    74 
    75 побрз JOIN со portfolios
    76 помал број на читања од табела (намален I/O)
    77 побрзо филтрирање по портфолио и време
    78 намалено време на извршување ~ 95% подобрување
    79 
     41CREATE INDEX idx_portfolios_user_id
     42ON portfolios(user_id);
     43
     44
     45EXPLAIN ANALYZE
     46SELECT *
     47FROM trade_request_full_view
     48WHERE user_id = 5;
     49}}}
     50
     51Seq Scan on portfolios p
     52Filter: (user_id = 5), idx_portfolios_user_id го решава ова, го заменува со Index Scan.
     53
     54
     55{{{
     56QUERY PLAN                                                                                                                                                    |
     57--------------------------------------------------------------------------------------------------------------------------------------------------------------+
     58Gather  (cost=1008.32..83981.49 rows=600 width=59) (actual time=1.062..297.378 rows=592 loops=1)                                                              |
     59  Workers Planned: 4                                                                                                                                          |
     60  Workers Launched: 4                                                                                                                                         |
     61  ->  Hash Join  (cost=8.31..82921.49 rows=150 width=59) (actual time=3.240..259.409 rows=118 loops=5)                                                        |
     62        Hash Cond: (tr.portfolio_id = p.id)                                                                                                                   |
     63        ->  Parallel Seq Scan on trade_request tr  (cost=0.00..78974.00 rows=1500000 width=51) (actual time=0.065..140.441 rows=1200000 loops=5)              |
     64        ->  Hash  (cost=8.30..8.30 rows=1 width=16) (actual time=0.106..0.107 rows=1 loops=5)                                                                 |
     65              Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                    |
     66              ->  Index Only Scan using idx_portfolios_user_id_id on portfolios p  (cost=0.29..8.30 rows=1 width=16) (actual time=0.098..0.099 rows=1 loops=5)|
     67                    Index Cond: (user_id = 5)                                                                                                                 |
     68                    Heap Fetches: 5                                                                                                                           |
     69Planning Time: 0.555 ms                                                                                                                                       |
     70Execution Time: 297.480 ms                                                                                                                                    |
     71}}}
     72
     73
     74Од 116765.182 ms , до сега 297.480 ms.
    8075
    8176== 2. stock_history_detail_view