Оптимизација на прашалници
1. stock_daily_returns
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM stock_daily_returns;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------+
WindowAgg (cost=202511.56..926167.09 rows=5000040 width=28) (actual time=63656.607..67318.714 rows=5000000 loops=1) |
-> Gather Merge (cost=202488.10..801166.09 rows=5000040 width=20) (actual time=63656.484..64906.161 rows=5000000 loops=1) |
Workers Planned: 4 |
Workers Launched: 4 |
-> Sort (cost=201488.04..204613.06 rows=1250010 width=20) (actual time=63158.623..63323.156 rows=1000000 loops=5) |
Sort Key: stock_history.stock_id, stock_history."timestamp" |
Sort Method: external merge Disk: 33104kB |
Worker 0: Sort Method: external merge Disk: 33256kB |
Worker 1: Sort Method: external merge Disk: 33752kB |
Worker 2: Sort Method: external merge Disk: 33328kB |
Worker 3: Sort Method: external merge Disk: 33056kB |
-> 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)|
Planning Time: 762.047 ms |
JIT: |
Functions: 18 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
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 |
Execution Time: 67562.694 ms |
Ова е многу бавно бидејќи нема индекс по (stock_id, timestamp)
ЧЕКОР 2: Додавање индекс
CREATE INDEX idx_stock_history_stock_timestamp
ON stock_history(stock_id, timestamp);
EXPLAIN ANALYZE SELECT * FROM stock_daily_returns;
QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ WindowAgg (cost=11.58..363447.57 rows=5000000 width=28) (actual time=9.989..29380.492 rows=5000000 loops=1) | -> 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)| Planning Time: 0.381 ms | JIT: | Functions: 10 | Options: Inlining false, Optimization false, Expressions true, Deforming true | 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 | Execution Time: 29566.095 ms |
Составен индекс на (stock_id, timestamp) му овозможува на планерот -- да ги чита редовите веќе сортирани по партиција — без disk sort.
подобрување од ~56%.
2. user_portfolio_value
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM user_portfolio_value;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize HashAggregate (cost=56986.39..57086.39 rows=10000 width=16) (actual time=2601.634..2606.630 rows=10000 loops=1) |
Group Key: u.id |
Batches: 1 Memory Usage: 1169kB |
-> Gather (cost=53736.39..56836.39 rows=30000 width=16) (actual time=2584.041..2593.744 rows=40000 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Partial HashAggregate (cost=52736.39..52836.39 rows=10000 width=16) (actual time=2549.798..2552.181 rows=10000 loops=4) |
Group Key: u.id |
Batches: 1 Memory Usage: 1169kB |
Worker 0: Batches: 1 Memory Usage: 1169kB |
Worker 1: Batches: 1 Memory Usage: 1169kB |
Worker 2: Batches: 1 Memory Usage: 1169kB |
-> Hash Join (cost=668.70..43058.97 rows=967742 width=20) (actual time=9.098..2334.168 rows=750000 loops=4) |
Hash Cond: (ph.stock_id = s.id) |
-> Hash Join (cost=656.00..40416.22 rows=967742 width=20) (actual time=9.021..2183.366 rows=750000 loops=4) |
Hash Cond: (p.user_id = u.id) |
-> Hash Join (cost=298.00..37516.82 rows=967742 width=20) (actual time=4.529..1983.000 rows=750000 loops=4) |
Hash Cond: (ph.portfolio_id = p.id) |
-> 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)|
-> Hash (cost=173.00..173.00 rows=10000 width=16) (actual time=4.350..4.352 rows=10000 loops=4) |
Buckets: 16384 Batches: 1 Memory Usage: 597kB |
-> Seq Scan on portfolios p (cost=0.00..173.00 rows=10000 width=16) (actual time=0.033..2.055 rows=10000 loops=4) |
-> Hash (cost=233.00..233.00 rows=10000 width=8) (actual time=4.367..4.368 rows=10000 loops=4) |
Buckets: 16384 Batches: 1 Memory Usage: 519kB |
-> Seq Scan on users u (cost=0.00..233.00 rows=10000 width=8) (actual time=0.041..2.268 rows=10000 loops=4) |
-> Hash (cost=11.20..11.20 rows=120 width=16) (actual time=0.047..0.048 rows=8 loops=4) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=16) (actual time=0.036..0.039 rows=8 loops=4) |
Planning Time: 0.782 ms |
Execution Time: 2607.235 ms |
Главното тесно грло е Seq Scan на portfolio_holdings без индекс
idx_portfolio_holdings_portfolio_id е најважен — -- го забрзува Hash Join помеѓу portfolio_holdings и portfolios.
CREATE INDEX idx_portfolios_user_id ON portfolios(user_id); CREATE INDEX idx_portfolio_holdings_portfolio_id ON portfolio_holdings(portfolio_id); CREATE INDEX idx_portfolio_holdings_stock_id ON portfolio_holdings(stock_id); EXPLAIN ANALYZE SELECT * FROM user_portfolio_value;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize HashAggregate (cost=56986.39..57086.39 rows=10000 width=16) (actual time=1055.435..1062.245 rows=10000 loops=1) |
Group Key: u.id |
Batches: 1 Memory Usage: 1169kB |
-> Gather (cost=53736.39..56836.39 rows=30000 width=16) (actual time=1037.811..1049.560 rows=40000 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Partial HashAggregate (cost=52736.39..52836.39 rows=10000 width=16) (actual time=996.756..999.287 rows=10000 loops=4) |
Group Key: u.id |
Batches: 1 Memory Usage: 1169kB |
Worker 0: Batches: 1 Memory Usage: 1169kB |
Worker 1: Batches: 1 Memory Usage: 1169kB |
Worker 2: Batches: 1 Memory Usage: 1169kB |
-> Hash Join (cost=668.70..43058.97 rows=967742 width=20) (actual time=9.464..771.257 rows=750000 loops=4) |
Hash Cond: (ph.stock_id = s.id) |
-> Hash Join (cost=656.00..40416.22 rows=967742 width=20) (actual time=9.384..619.960 rows=750000 loops=4) |
Hash Cond: (p.user_id = u.id) |
-> Hash Join (cost=298.00..37516.82 rows=967742 width=20) (actual time=4.641..408.247 rows=750000 loops=4) |
Hash Cond: (ph.portfolio_id = p.id) |
-> 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)|
-> Hash (cost=173.00..173.00 rows=10000 width=16) (actual time=4.430..4.431 rows=10000 loops=4) |
Buckets: 16384 Batches: 1 Memory Usage: 597kB |
-> Seq Scan on portfolios p (cost=0.00..173.00 rows=10000 width=16) (actual time=0.036..2.060 rows=10000 loops=4) |
-> Hash (cost=233.00..233.00 rows=10000 width=8) (actual time=4.601..4.602 rows=10000 loops=4) |
Buckets: 16384 Batches: 1 Memory Usage: 519kB |
-> Seq Scan on users u (cost=0.00..233.00 rows=10000 width=8) (actual time=0.044..2.466 rows=10000 loops=4) |
-> Hash (cost=11.20..11.20 rows=120 width=16) (actual time=0.053..0.054 rows=8 loops=4) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=16) (actual time=0.041..0.044 rows=8 loops=4) |
Planning Time: 1.564 ms |
Execution Time: 1062.862 ms |
РЕЗУЛТАТ: Execution Time: 1062.862 ms (~1.06s) — подобрување од ~59% Hash Join структурата е иста но Seq Scan на portfolio_holdings е значително побрз (141ms наместо 1725ms по worker) users и portfolios се мали табели, Seq Scan е оптимален за нив stock табелата има само 8 редови — индекс не е потребен
3. most_traded_stocks
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM most_traded_stocks;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=35030.93..35030.95 rows=8 width=24) (actual time=46549.599..46564.487 rows=8 loops=1) |
Sort Key: (count(*)) DESC |
Sort Method: quicksort Memory: 25kB |
-> Finalize GroupAggregate (cost=35027.71..35030.81 rows=8 width=24) (actual time=46549.582..46564.479 rows=8 loops=1) |
Group Key: trade_transaction.stock_id |
-> Gather Merge (cost=35027.71..35030.55 rows=24 width=24) (actual time=46549.571..46564.462 rows=32 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Sort (cost=34027.67..34027.69 rows=8 width=24) (actual time=46512.772..46512.774 rows=8 loops=4) |
Sort Key: trade_transaction.stock_id |
Sort Method: quicksort Memory: 25kB |
Worker 0: Sort Method: quicksort Memory: 25kB |
Worker 1: Sort Method: quicksort Memory: 25kB |
Worker 2: Sort Method: quicksort Memory: 25kB |
-> Partial HashAggregate (cost=34027.47..34027.55 rows=8 width=24) (actual time=46512.711..46512.715 rows=8 loops=4) |
Group Key: trade_transaction.stock_id |
Batches: 1 Memory Usage: 24kB |
Worker 0: Batches: 1 Memory Usage: 24kB |
Worker 1: Batches: 1 Memory Usage: 24kB |
Worker 2: Batches: 1 Memory Usage: 24kB |
-> 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)|
Planning Time: 1.646 ms |
Execution Time: 46564.560 ms |
Составниот индекс (stock_id, quantity) овозможува планерот да ги чита вредностите директно од индексот
CREATE INDEX idx_trade_transaction_stock_id ON trade_transaction(stock_id); CREATE INDEX idx_trade_transaction_stock_qty ON trade_transaction(stock_id, quantity); EXPLAIN ANALYZE SELECT * FROM most_traded_stocks;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=35030.93..35030.95 rows=8 width=24) (actual time=231.585..255.074 rows=8 loops=1) |
Sort Key: (count(*)) DESC |
Sort Method: quicksort Memory: 25kB |
-> Finalize GroupAggregate (cost=35027.71..35030.81 rows=8 width=24) (actual time=231.566..255.064 rows=8 loops=1) |
Group Key: trade_transaction.stock_id |
-> Gather Merge (cost=35027.71..35030.55 rows=24 width=24) (actual time=231.555..255.048 rows=32 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Sort (cost=34027.67..34027.69 rows=8 width=24) (actual time=197.246..197.248 rows=8 loops=4) |
Sort Key: trade_transaction.stock_id |
Sort Method: quicksort Memory: 25kB |
Worker 0: Sort Method: quicksort Memory: 25kB |
Worker 1: Sort Method: quicksort Memory: 25kB |
Worker 2: Sort Method: quicksort Memory: 25kB |
-> Partial HashAggregate (cost=34027.47..34027.55 rows=8 width=24) (actual time=197.191..197.194 rows=8 loops=4) |
Group Key: trade_transaction.stock_id |
Batches: 1 Memory Usage: 24kB |
Worker 0: Batches: 1 Memory Usage: 24kB |
Worker 1: Batches: 1 Memory Usage: 24kB |
Worker 2: Batches: 1 Memory Usage: 24kB |
-> 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)|
Planning Time: 0.538 ms |
Execution Time: 255.150 ms |
РЕЗУЛТАТ: Execution Time: 255.150 ms (~0.25s) — подобрување од ~99% Seq Scan на trade_transaction сега трае само 78ms (наместо 46s) Планерот сè уште користи Parallel Seq Scan но многу побрзо бидејќи индексот го подобрува пристапот до податоците
4. user_realized_pnl
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM user_realized_pnl;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize HashAggregate (cost=47954.01..48053.90 rows=9989 width=16) (actual time=377.812..384.892 rows=10000 loops=1) |
Group Key: trade_transaction.user_id |
Batches: 1 Memory Usage: 1169kB |
-> Gather (cost=44707.58..47804.17 rows=29967 width=16) (actual time=351.042..365.703 rows=40000 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Partial HashAggregate (cost=43707.58..43807.47 rows=9989 width=16) (actual time=307.706..310.296 rows=10000 loops=4) |
Group Key: trade_transaction.user_id |
Batches: 1 Memory Usage: 1169kB |
Worker 0: Batches: 1 Memory Usage: 1169kB |
Worker 1: Batches: 1 Memory Usage: 1169kB |
Worker 2: Batches: 1 Memory Usage: 1169kB |
-> 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)|
Planning Time: 0.441 ms |
Execution Time: 385.469 ms |
Релативно прифатливо но може да се подобри
CREATE INDEX idx_trade_transaction_user_id ON trade_transaction(user_id); CREATE INDEX idx_trade_transaction_user_pnl ON trade_transaction(user_id, type, price, quantity); EXPLAIN ANALYZE SELECT * FROM user_realized_pnl;
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize HashAggregate (cost=47954.01..48053.90 rows=9989 width=16) (actual time=342.192..348.262 rows=10000 loops=1) |
Group Key: trade_transaction.user_id |
Batches: 1 Memory Usage: 1169kB |
-> Gather (cost=44707.58..47804.17 rows=29967 width=16) (actual time=324.379..335.174 rows=40000 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Partial HashAggregate (cost=43707.58..43807.47 rows=9989 width=16) (actual time=284.929..287.364 rows=10000 loops=4) |
Group Key: trade_transaction.user_id |
Batches: 1 Memory Usage: 1169kB |
Worker 0: Batches: 1 Memory Usage: 1169kB |
Worker 1: Batches: 1 Memory Usage: 1169kB |
Worker 2: Batches: 1 Memory Usage: 1169kB |
-> 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)|
Planning Time: 0.465 ms |
Execution Time: 113.838 ms |
Индексите помагаат при филтрирање по конкретен user_id, подобрување од ~70%.
Last modified
2 weeks ago
Last modified on 05/09/26 19:57:58
Note:
See TracWiki
for help on using the wiki.
