Оптимизација на прашалници
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. trade_transaction_detail_view
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view WHERE user_id = 5 AND timestamp >= NOW() - INTERVAL '30 days';
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------------------+
Gather (cost=1000.15..36665.94 rows=200 width=631) (actual time=19426.122..41473.240 rows=193 loops=1) |
Workers Planned: 3 |
Workers Launched: 3 |
-> Nested Loop (cost=0.15..35645.94 rows=65 width=631) (actual time=19563.006..41397.006 rows=48 loops=4) |
-> 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)|
Filter: ((user_id = 5) AND ("timestamp" >= (now() - '30 days'::interval))) |
Rows Removed by Filter: 500091 |
-> Memoize (cost=0.15..0.41 rows=1 width=582) (actual time=0.581..0.581 rows=1 loops=193) |
Cache Key: tt.stock_id |
Cache Mode: logical |
Hits: 38 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
Worker 0: Hits: 26 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
Worker 1: Hits: 55 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
Worker 2: Hits: 42 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
-> 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) |
Index Cond: (id = tt.stock_id) |
Planning Time: 84.447 ms |
Execution Time: 41473.375 ms |
Parallel Seq Scan на trade_transaction join со stock филтер се применува на крај (after scan) читање на ~2M редови
ЧЕКОР 2: Додавање индекси
CREATE INDEX idx_trade_transaction_user_id ON trade_transaction(user_id); CREATE INDEX idx_trade_transaction_user_time ON trade_transaction(user_id, timestamp); EXPLAIN ANALYZE SELECT * FROM trade_transaction_detail_view WHERE user_id = 5 AND timestamp >= NOW() - INTERVAL '30 days';
idx_trade_transaction_user_id, директно го намалува scan на 2M редови.
idx_trade_transaction_user_time ова е најважниот индекс за овој view бидејќи: прво филтрира user потоа временски range (30 days)
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=6.13..762.23 rows=200 width=631) (actual time=0.372..1.219 rows=193 loops=1) |
-> 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) |
Recheck Cond: (user_id = 5) |
Filter: ("timestamp" >= (now() - '30 days'::interval)) |
Heap Blocks: exact=191 |
-> 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)|
Index Cond: (user_id = 5) |
-> Memoize (cost=0.15..0.41 rows=1 width=582) (actual time=0.000..0.000 rows=1 loops=193) |
Cache Key: tt.stock_id |
Cache Mode: logical |
Hits: 185 Misses: 8 Evictions: 0 Overflows: 0 Memory Usage: 2kB |
-> 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) |
Index Cond: (id = tt.stock_id) |
Planning Time: 0.605 ms |
Execution Time: 1.272 ms |
РЕЗУЛТАТ: 41473.375 ms ->1.272 ms .
За оптимизација на trade_transaction_detail_view се користи реален филтриран query по user_id и временски интервал. Без индекси, PostgreSQL извршува sequential scan над голем број записи, што резултира со високо време на извршување. Со додавање на индекс над user_id и composite индекс (user_id, timestamp) се овозможува значително намалување на бројот на скенирани редови и подобрување на перформансите за user-specific queries.
3. stock_history_detail_view
ЧЕКОР 1: Анализа ПРЕД оптимизација
EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view WHERE stock_id = 3 AND timestamp >= NOW() - INTERVAL '7 days';
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=1000.14..62780.97 rows=71 width=602) (actual time=21251.698..21257.102 rows=0 loops=1) |
-> 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) |
Index Cond: (id = 3) |
-> Gather (cost=1000.00..62772.10 rows=71 width=28) (actual time=21251.654..21257.057 rows=0 loops=1) |
Workers Planned: 4 |
Workers Launched: 4 |
-> 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)|
Filter: ((stock_id = 3) AND ("timestamp" >= (now() - '7 days'::interval))) |
Rows Removed by Filter: 1000000 |
Planning Time: 0.231 ms |
Execution Time: 21257.143 ms |
Seq Scan на 5M rows (stock_history) join со stock филтер по време се прави после scan
CREATE INDEX idx_stock_history_stock_id ON stock_history(stock_id); CREATE INDEX idx_stock_history_stock_time ON stock_history(stock_id, timestamp); EXPLAIN ANALYZE SELECT * FROM stock_history_detail_view WHERE stock_id = 3 AND timestamp >= NOW() - INTERVAL '7 days';
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop (cost=5.31..290.09 rows=71 width=602) (actual time=0.096..0.097 rows=0 loops=1) |
-> 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) |
Index Cond: (id = 3) |
-> 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) |
Recheck Cond: ((stock_id = 3) AND ("timestamp" >= (now() - '7 days'::interval))) |
-> 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)|
Index Cond: ((stock_id = 3) AND ("timestamp" >= (now() - '7 days'::interval))) |
Planning Time: 0.642 ms |
Execution Time: 0.133 ms |
РЕЗУЛТАТ: 21257.143 ms -> 0.133 ms
При анализа на stock_history_detail_view се користи филтрирање по stock_id и временски опсег. Првичниот план покажува sequential scan над голема количина податоци. Со додавање на индекс над stock_id и composite индекс (stock_id, timestamp) се оптимизира пристапот за time-series податоци, што е критично за приказ на графикони и историски цени.
Last modified
5 days ago
Last modified on 05/21/26 02:41:13
Note:
See TracWiki
for help on using the wiki.
