| Version 4 (modified by , 5 days ago) ( diff ) |
|---|
Оптимизација на прашалници
1. trade_request_full_view
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM trade_request_full_view WHERE user_id = 5;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------+
Gather (cost=1198.01..84171.18 rows=600 width=59) (actual time=2.369..116765.024 rows=592 loops=1) |
Workers Planned: 4 |
Workers Launched: 4 |
-> Hash Join (cost=198.01..83111.18 rows=150 width=59) (actual time=1109.166..116211.985 rows=118 loops=5) |
Hash Cond: (tr.portfolio_id = p.id) |
-> 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)|
-> Hash (cost=198.00..198.00 rows=1 width=16) (actual time=1.379..1.381 rows=1 loops=5) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on portfolios p (cost=0.00..198.00 rows=1 width=16) (actual time=0.047..1.373 rows=1 loops=5) |
Filter: (user_id = 5) |
Rows Removed by Filter: 9999 |
Planning Time: 0.348 ms |
Execution Time: 116765.182 ms |
ЧЕКОР 2: Додавање индекс
CREATE INDEX idx_portfolios_user_id ON portfolios(user_id); EXPLAIN ANALYZE SELECT * FROM trade_request_full_view WHERE user_id = 5;
Seq Scan on portfolios p Filter: (user_id = 5), idx_portfolios_user_id го решава ова, го заменува со Index Scan.
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------+
Gather (cost=1008.32..83981.49 rows=600 width=59) (actual time=1.062..297.378 rows=592 loops=1) |
Workers Planned: 4 |
Workers Launched: 4 |
-> Hash Join (cost=8.31..82921.49 rows=150 width=59) (actual time=3.240..259.409 rows=118 loops=5) |
Hash Cond: (tr.portfolio_id = p.id) |
-> 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) |
-> Hash (cost=8.30..8.30 rows=1 width=16) (actual time=0.106..0.107 rows=1 loops=5) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> 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)|
Index Cond: (user_id = 5) |
Heap Fetches: 5 |
Planning Time: 0.555 ms |
Execution Time: 297.480 ms |
Од 116765.182 ms , до сега 297.480 ms.
2. stock_history_detail_view
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=12.70..100366.25 rows=5000000 width=602) (actual time=48.972..61018.409 rows=5000000 loops=1) |
Hash Cond: (sh.stock_id = s.id) |
-> 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)|
-> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=8.930..8.932 rows=8 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=8.906..8.910 rows=8 loops=1) |
Planning Time: 11.988 ms |
JIT: |
Functions: 11 |
Options: Inlining false, Optimization false, Expressions true, Deforming true |
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 |
Execution Time: 61188.242 ms |
ЧЕКОР 2: Додавање индекси
CREATE INDEX idx_stock_history_stock_id ON stock_history(stock_id); CREATE INDEX idx_stock_history_timestamp ON stock_history(timestamp); CREATE INDEX idx_stock_history_stock_time ON stock_history(stock_id, timestamp); EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view;
stock_id - клучен за JOIN со stock, без него се скенираат ~5M редови
timestamp - овозможува побрзи временски филтри (price history queries)
(stock_id, timestamp) - најважен индекс за time-series податоци
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=12.70..100366.25 rows=5000000 width=602) (actual time=9.110..1708.148 rows=5000000 loops=1) |
Hash Cond: (sh.stock_id = s.id) |
-> 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)|
-> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=9.039..9.041 rows=8 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=9.014..9.017 rows=8 loops=1) |
Planning Time: 0.676 ms |
JIT: |
Functions: 11 |
Options: Inlining false, Optimization false, Expressions true, Deforming true |
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 |
Execution Time: 1877.318 ms |
РЕЗУЛТАТ:
побрз JOIN со stock табела значително намалено време за читање на историски податоци подобра перформанса за филтрирање по акција + време ~ 96-97% подобрување
3. trade_transaction_detail_view
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.057..771.499 rows=2000557 loops=1) |
Hash Cond: (tt.stock_id = s.id) |
-> 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)|
-> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=0.018..0.019 rows=8 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.012 rows=8 loops=1) |
Planning Time: 0.220 ms |
Execution Time: 842.067 ms |
CREATE INDEX idx_trade_transaction_stock_id ON trade_transaction(stock_id); CREATE INDEX idx_trade_transaction_user_id ON trade_transaction(user_id); CREATE INDEX idx_trade_transaction_timestamp ON trade_transaction(timestamp); CREATE INDEX idx_trade_transaction_stock_time ON trade_transaction(stock_id, timestamp); EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view;
stock_id - JOIN со stock, ја избегнува целосна проверка на 2M редови
user_id - овозможува брзи user-based query филтри
timestamp - подобрува временски опсег на пребарување
(stock_id, timestamp) - најдобар за комбинирани филтри (акција + време)
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------+
Hash Join (cost=12.70..48189.20 rows=2000557 width=631) (actual time=0.046..766.811 rows=2000557 loops=1) |
Hash Cond: (tt.stock_id = s.id) |
-> 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)|
-> Hash (cost=11.20..11.20 rows=120 width=582) (actual time=0.017..0.018 rows=8 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Seq Scan on stock s (cost=0.00..11.20 rows=120 width=582) (actual time=0.009..0.011 rows=8 loops=1) |
Planning Time: 0.645 ms |
Execution Time: 837.658 ms |
РЕЗУЛТАТ: многу мала разлика во EXPLAIN
Note:
See TracWiki
for help on using the wiki.
