= Оптимизација на прашалници == 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%.