Други Развојни Активности
Анализа на перформанси
Извештај за диверзификација на портфолио (report_portfolio_diversification)
Анализата на перформанси се врши врз основа на моменталната состојба во базата.
SQL:
- Без индекси
EXPLAIN ANALYZE WITH holding_values AS ( SELECT ph.portfolio_id AS hv_portfolio_id, ph.stock_id AS hv_stock_id, s.symbol AS hv_symbol, ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value, ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl FROM portfolio_holdings ph JOIN stock s ON ph.stock_id = s.id WHERE ph.quantity > 0 ), stock_weights AS ( SELECT hv.hv_portfolio_id AS sw_portfolio_id, hv.hv_stock_id AS sw_stock_id, hv.hv_symbol AS sw_symbol, SUM(hv.market_value) AS stock_market_value, SUM(hv.unrealized_pnl) AS stock_unrealized_pnl, COUNT(*) AS lots_count FROM holding_values hv GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol ), portfolio_totals AS ( SELECT sw.sw_portfolio_id AS pt_portfolio_id, SUM(sw.stock_market_value) AS total_holdings_value, SUM(sw.stock_unrealized_pnl) AS total_unrealized_pnl, COUNT(DISTINCT sw.sw_stock_id) AS num_stocks, SUM(sw.lots_count) AS total_lots FROM stock_weights sw GROUP BY sw.sw_portfolio_id ), hhi_calc AS ( SELECT sw.sw_portfolio_id AS hhi_portfolio_id, SUM(POWER(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), 2::NUMERIC)) AS hhi, MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS max_weight_pct, MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS min_weight_pct FROM stock_weights sw JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id GROUP BY sw.sw_portfolio_id ), dominant_stock AS ( SELECT DISTINCT ON (sw.sw_portfolio_id) sw.sw_portfolio_id AS ds_portfolio_id, sw.sw_symbol AS dominant_symbol, ROUND(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC, 2) AS dominant_weight_pct FROM stock_weights sw JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id ORDER BY sw.sw_portfolio_id, sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC ) SELECT u.id::INTEGER AS user_id, u.username::TEXT, p.id::INTEGER AS portfolio_id, p.balance::NUMERIC AS portfolio_cash_balance, COALESCE(pt.total_holdings_value, 0::NUMERIC) AS total_holdings_value, (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)) AS total_portfolio_value, COALESCE(pt.num_stocks, 0)::BIGINT AS num_distinct_stocks, COALESCE(pt.total_lots, 0)::BIGINT AS total_lots, COALESCE(pt.total_unrealized_pnl, 0::NUMERIC) AS unrealized_pnl, ROUND(COALESCE(pt.total_unrealized_pnl, 0::NUMERIC) / NULLIF(pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC), 0::NUMERIC) * 100::NUMERIC, 2) AS unrealized_pnl_pct, ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4) AS hhi_score, CASE WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK' WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK' ELSE 'WELL DIVERSIFIED' END::TEXT AS risk_classification, COALESCE(ds.dominant_symbol, 'N/A')::TEXT AS dominant_stock_symbol, COALESCE(ds.dominant_weight_pct, 0::NUMERIC) AS dominant_stock_weight_pct, COALESCE(hhi.max_weight_pct, 0::NUMERIC) AS max_single_weight_pct, COALESCE(hhi.min_weight_pct, 0::NUMERIC) AS min_single_weight_pct, RANK() OVER (ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC)::BIGINT AS diversification_rank FROM users u JOIN portfolios p ON u.id = p.user_id LEFT JOIN portfolio_totals pt ON p.id = pt.pt_portfolio_id LEFT JOIN hhi_calc hhi ON p.id = hhi.hhi_portfolio_id LEFT JOIN dominant_stock ds ON p.id = ds.ds_portfolio_id WHERE u.role = 'USER' ORDER BY hhi_score ASC, total_portfolio_value DESC; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Sort (cost=10000076265.17..10000076267.67 rows=1000 width=448) (actual time=2950.286..2950.368 rows=1000 loops=1) | Sort Key: (round(COALESCE(hhi.hhi, '0'::numeric), 4)), (((p.balance)::numeric + COALESCE(pt.total_holdings_value, '0'::numeric))) DESC | Sort Method: quicksort Memory: 212kB | CTE stock_weights | -> GroupAggregate (cost=10000007600.41..10000013734.23 rows=50009 width=93) (actual time=913.089..1157.834 rows=50000 loops=1) | Group Key: ph.stock_id, ph.portfolio_id, s.symbol | -> Incremental Sort (cost=10000007600.41..10000011358.81 rows=50009 width=39) (actual time=913.012..941.655 rows=50000 loops=1) | Sort Key: ph.stock_id, ph.portfolio_id, s.symbol | Presorted Key: ph.stock_id | Full-sort Groups: 50 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB | Pre-sorted Groups: 50 Sort Method: quicksort Average Memory: 87kB Peak Memory: 87kB | -> Merge Join (cost=10000007547.32..10000008306.30 rows=50009 width=39) (actual time=912.479..927.879 rows=50000 loops=1) | Merge Cond: (ph.stock_id = s.id) | -> Sort (cost=10000007547.18..10000007672.20 rows=50009 width=26) (actual time=912.375..919.789 rows=50000 loops=1) | Sort Key: ph.stock_id | Sort Method: external merge Disk: 1824kB | -> Seq Scan on portfolio_holdings ph (cost=10000000000.00..10000001250.00 rows=50009 width=26) (actual time=0.062..13.649 rows=50000 loops=1) | Filter: (quantity > 0) | Rows Removed by Filter: 10000 | -> Index Scan using stock_pkey on stock s (cost=0.14..14.64 rows=100 width=21) (actual time=0.025..0.196 rows=50 loops=1) | CTE portfolio_totals | -> GroupAggregate (cost=9691.36..10444.99 rows=200 width=112) (actual time=1232.448..1261.387 rows=1000 loops=1) | Group Key: sw_2.sw_portfolio_id | -> Sort (cost=9691.36..9816.38 rows=50009 width=88) (actual time=1232.344..1246.691 rows=50000 loops=1) | Sort Key: sw_2.sw_portfolio_id, sw_2.sw_stock_id | Sort Method: external merge Disk: 2704kB | -> CTE Scan on stock_weights sw_2 (cost=0.00..1000.18 rows=50009 width=88) (actual time=913.102..1191.852 rows=50000 loops=1) | -> WindowAgg (cost=51785.63..51833.12 rows=1000 width=448) (actual time=2946.255..2948.249 rows=1000 loops=1) | -> Sort (cost=51785.62..51788.12 rows=1000 width=817) (actual time=2946.215..2946.303 rows=1000 loops=1) | Sort Key: (COALESCE(hhi.hhi, '1'::numeric)) | Sort Method: quicksort Memory: 187kB | -> Hash Join (cost=46946.33..51371.79 rows=1000 width=817) (actual time=2815.414..2945.323 rows=1000 loops=1) | Hash Cond: (p.user_id = u.id) | -> Hash Left Join (cost=46879.05..51301.88 rows=1000 width=772) (actual time=1628.954..1758.394 rows=1000 loops=1) | Hash Cond: (p.id = hhi.hhi_portfolio_id) | -> Merge Left Join (cost=42364.74..46784.93 rows=1000 width=676) (actual time=1263.716..1392.616 rows=1000 loops=1) | Merge Cond: (p.id = sw.sw_portfolio_id) | -> Merge Left Join (cost=0.28..55.77 rows=1000 width=128) (actual time=1232.543..1234.126 rows=1000 loops=1) | Merge Cond: (p.id = pt.pt_portfolio_id) | -> Index Scan using portfolios_pkey on portfolios p (cost=0.28..46.27 rows=1000 width=24) (actual time=0.048..0.814 rows=1000 loops=1) | -> Materialize (cost=0.00..4.50 rows=200 width=112) (actual time=1232.456..1232.755 rows=1000 loops=1) | -> CTE Scan on portfolio_totals pt (cost=0.00..4.00 rows=200 width=112) (actual time=1232.452..1232.606 rows=1000 loops=1) | -> Unique (cost=42364.47..46722.15 rows=200 width=588) (actual time=31.143..158.023 rows=1000 loops=1) | -> Incremental Sort (cost=42364.47..46597.13 rows=50009 width=588) (actual time=31.141..154.964 rows=49951 loops=1) | Sort Key: sw.sw_portfolio_id, ((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric))) DESC | Presorted Key: sw.sw_portfolio_id | Full-sort Groups: 1000 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB | -> Merge Join (cost=42346.36..43976.15 rows=50009 width=588) (actual time=30.962..100.854 rows=50000 loops=1) | Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id) | -> CTE Scan on portfolio_totals pt_1 (cost=0.00..4.00 rows=200 width=40) (actual time=0.001..0.332 rows=1000 loops=1) | -> Materialize (cost=42346.36..42596.40 rows=50009 width=556) (actual time=30.913..45.619 rows=50000 loops=1) | -> Sort (cost=42346.36..42471.38 rows=50009 width=556) (actual time=30.910..39.318 rows=50000 loops=1) | Sort Key: sw.sw_portfolio_id | Sort Method: external merge Disk: 1616kB | -> CTE Scan on stock_weights sw (cost=0.00..1000.18 rows=50009 width=556) (actual time=0.046..9.117 rows=50000 loops=1)| -> Hash (cost=4511.81..4511.81 rows=200 width=104) (actual time=365.175..365.180 rows=1000 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 94kB | -> Subquery Scan on hhi (cost=4507.31..4511.81 rows=200 width=104) (actual time=237.945..364.912 rows=1000 loops=1) | -> HashAggregate (cost=4507.31..4509.81 rows=200 width=104) (actual time=237.936..364.756 rows=1000 loops=1) | Group Key: sw_1.sw_portfolio_id | Batches: 5 Memory Usage: 625kB Disk Usage: 232kB | -> Hash Join (cost=6.50..2882.02 rows=50009 width=72) (actual time=29.760..53.495 rows=50000 loops=1) | Hash Cond: (sw_1.sw_portfolio_id = pt_2.pt_portfolio_id) | -> CTE Scan on stock_weights sw_1 (cost=0.00..1000.18 rows=50009 width=40) (actual time=0.011..9.383 rows=50000 loops=1) | -> Hash (cost=4.00..4.00 rows=200 width=40) (actual time=29.701..29.703 rows=1000 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 63kB | -> CTE Scan on portfolio_totals pt_2 (cost=0.00..4.00 rows=200 width=40) (actual time=0.014..29.427 rows=1000 loops=1) | -> Hash (cost=54.77..54.77 rows=1000 width=21) (actual time=1186.429..1186.430 rows=1000 loops=1) | Buckets: 1024 Batches: 1 Memory Usage: 61kB | -> Index Scan using users_pkey on users u (cost=0.28..54.77 rows=1000 width=21) (actual time=1185.685..1186.197 rows=1000 loops=1) | Filter: ((role)::text = 'USER'::text) | Planning Time: 3.006 ms | JIT: | Functions: 83 | Options: Inlining true, Optimization true, Expressions true, Deforming true | Timing: Generation 8.317 ms (Deform 3.321 ms), Inlining 71.671 ms, Optimization 773.085 ms, Emission 545.906 ms, Total 1398.978 ms | Execution Time: 2960.889 ms |
Индекси:
1. idx_ph_portfolio_stock_qty (Composite partial index)
Composite partial индекс на portfolio_holdings(portfolio_id, stock_id, quantity) со услов WHERE quantity > 0.
CREATE INDEX idx_ph_portfolio_stock_qty
ON portfolio_holdings (portfolio_id, stock_id, quantity)
WHERE quantity > 0;
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=20008.39..20010.89 rows=1000 width=448) (actual time=1159.420..1159.488 rows=1000 loops=1) |
Sort Key: (round(COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '0'::numeric), 4)), (((p.balance)::numeric + COALESCE(pt.total_holdings_value, '0'::numeric))) DESC|
Sort Method: quicksort Memory: 212kB |
CTE stock_weights |
-> GroupAggregate (cost=1.53..8892.19 rows=50009 width=93) (actual time=0.297..343.555 rows=50000 loops=1) |
Group Key: ph.portfolio_id, ph.stock_id, s.symbol |
-> Incremental Sort (cost=1.53..6516.76 rows=50009 width=39) (actual time=0.261..101.899 rows=50000 loops=1) |
Sort Key: ph.portfolio_id, ph.stock_id, s.symbol |
Presorted Key: ph.portfolio_id, ph.stock_id |
Full-sort Groups: 1563 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Nested Loop (cost=0.57..5006.72 rows=50009 width=39) (actual time=0.083..84.356 rows=50000 loops=1) |
-> Index Scan using idx_ph_portfolio_stock_qty on portfolio_holdings ph (cost=0.41..3754.09 rows=50009 width=26) (actual time=0.051..56.091 rows=50000 loops=1) |
-> Memoize (cost=0.15..0.17 rows=1 width=21) (actual time=0.000..0.000 rows=1 loops=50000) |
Cache Key: ph.stock_id |
Cache Mode: logical |
Hits: 49950 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 7kB |
-> Index Scan using stock_pkey on stock s (cost=0.14..0.16 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=50) |
Index Cond: (id = ph.stock_id) |
CTE portfolio_totals |
-> GroupAggregate (cost=0.00..1628.79 rows=200 width=112) (actual time=0.702..862.171 rows=1000 loops=1) |
Group Key: sw_2.sw_portfolio_id |
-> CTE Scan on stock_weights sw_2 (cost=0.00..1000.18 rows=50009 width=88) (actual time=0.299..837.648 rows=50000 loops=1) |
-> WindowAgg (cost=9187.09..9234.58 rows=1000 width=448) (actual time=1154.807..1157.221 rows=1000 loops=1) |
-> Sort (cost=9187.08..9189.58 rows=1000 width=817) (actual time=1154.785..1154.927 rows=1000 loops=1) |
Sort Key: (COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '1'::numeric)) |
Sort Method: quicksort Memory: 187kB |
-> Hash Join (cost=90.03..8773.25 rows=1000 width=817) (actual time=2.268..1152.842 rows=1000 loops=1) |
Hash Cond: (p.user_id = u.id) |
-> Merge Left Join (cost=22.76..8703.34 rows=1000 width=772) (actual time=1.460..1150.243 rows=1000 loops=1) |
Merge Cond: (p.id = sw_1.sw_portfolio_id) |
-> Merge Left Join (cost=0.28..3445.38 rows=1000 width=224) (actual time=1.312..995.965 rows=1000 loops=1) |
Merge Cond: (p.id = sw.sw_portfolio_id) |
-> Merge Left Join (cost=0.28..55.77 rows=1000 width=128) (actual time=0.789..5.346 rows=1000 loops=1) |
Merge Cond: (p.id = pt.pt_portfolio_id) |
-> Index Scan using portfolios_pkey on portfolios p (cost=0.28..46.27 rows=1000 width=24) (actual time=0.075..2.856 rows=1000 loops=1) |
-> Materialize (cost=0.00..4.50 rows=200 width=112) (actual time=0.707..1.297 rows=1000 loops=1) |
-> CTE Scan on portfolio_totals pt (cost=0.00..4.00 rows=200 width=112) (actual time=0.704..0.942 rows=1000 loops=1) |
-> GroupAggregate (cost=0.00..3382.61 rows=200 width=104) (actual time=0.520..989.374 rows=1000 loops=1) |
Group Key: sw.sw_portfolio_id |
-> Merge Join (cost=0.00..1754.82 rows=50009 width=72) (actual time=0.004..887.199 rows=50000 loops=1) |
Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id) |
-> CTE Scan on portfolio_totals pt_1 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..862.598 rows=1000 loops=1) |
-> Materialize (cost=0.00..1125.20 rows=50009 width=40) (actual time=0.002..12.323 rows=50000 loops=1) |
-> CTE Scan on stock_weights sw (cost=0.00..1000.18 rows=50009 width=40) (actual time=0.000..5.927 rows=50000 loops=1) |
-> Unique (cost=22.48..5250.95 rows=200 width=588) (actual time=0.146..152.842 rows=1000 loops=1) |
-> Incremental Sort (cost=22.48..5125.93 rows=50009 width=588) (actual time=0.145..148.672 rows=49951 loops=1) |
Sort Key: sw_1.sw_portfolio_id, ((sw_1.stock_market_value / NULLIF(pt_2.total_holdings_value, '0'::numeric))) DESC |
Presorted Key: sw_1.sw_portfolio_id |
Full-sort Groups: 1000 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Merge Join (cost=0.00..2504.95 rows=50009 width=588) (actual time=0.006..91.344 rows=50000 loops=1) |
Merge Cond: (pt_2.pt_portfolio_id = sw_1.sw_portfolio_id) |
-> CTE Scan on portfolio_totals pt_2 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..0.344 rows=1000 loops=1) |
-> Materialize (cost=0.00..1125.20 rows=50009 width=556) (actual time=0.001..28.674 rows=50000 loops=1) |
-> CTE Scan on stock_weights sw_1 (cost=0.00..1000.18 rows=50009 width=556) (actual time=0.000..21.606 rows=50000 loops=1) |
-> Hash (cost=54.77..54.77 rows=1000 width=21) (actual time=0.793..0.795 rows=1000 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 61kB |
-> Index Scan using users_pkey on users u (cost=0.28..54.77 rows=1000 width=21) (actual time=0.017..0.598 rows=1000 loops=1) |
Filter: ((role)::text = 'USER'::text) |
Planning Time: 186.270 ms |
Execution Time: 1160.749 ms |
idx_ph_portfolio_stock_qty => Index Scan using idx_ph_portfolio_stock_qty on portfolio_holdings ph
---
2. idx_stock_id_covering (Covering index на stock)
Covering индекс на stock(id) со INCLUDE (symbol, current_price) за елиминирање на heap fetches.
CREATE INDEX idx_stock_id_covering
ON stock (id)
INCLUDE (symbol, current_price);
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=20008.39..20010.89 rows=1000 width=448) (actual time=743.813..743.880 rows=1000 loops=1) |
Sort Key: (round(COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '0'::numeric), 4)), (((p.balance)::numeric + COALESCE(pt.total_holdings_value, '0'::numeric))) DESC|
Sort Method: quicksort Memory: 212kB |
CTE stock_weights |
-> GroupAggregate (cost=1.53..8892.19 rows=50009 width=93) (actual time=0.732..359.488 rows=50000 loops=1) |
Group Key: ph.portfolio_id, ph.stock_id, s.symbol |
-> Incremental Sort (cost=1.53..6516.76 rows=50009 width=39) (actual time=0.700..108.625 rows=50000 loops=1) |
Sort Key: ph.portfolio_id, ph.stock_id, s.symbol |
Presorted Key: ph.portfolio_id, ph.stock_id |
Full-sort Groups: 1563 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Nested Loop (cost=0.57..5006.72 rows=50009 width=39) (actual time=0.084..89.347 rows=50000 loops=1) |
-> Index Scan using idx_ph_portfolio_stock_qty on portfolio_holdings ph (cost=0.41..3754.09 rows=50009 width=26) (actual time=0.055..59.156 rows=50000 loops=1) |
-> Memoize (cost=0.15..0.17 rows=1 width=21) (actual time=0.000..0.000 rows=1 loops=50000) |
Cache Key: ph.stock_id |
Cache Mode: logical |
Hits: 49950 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 7kB |
-> Index Only Scan using idx_stock_id_covering on stock s (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50) |
Index Cond: (id = ph.stock_id) |
Heap Fetches: 50 |
CTE portfolio_totals |
-> GroupAggregate (cost=0.00..1628.79 rows=200 width=112) (actual time=1.455..434.830 rows=1000 loops=1) |
Group Key: sw_2.sw_portfolio_id |
-> CTE Scan on stock_weights sw_2 (cost=0.00..1000.18 rows=50009 width=88) (actual time=0.733..409.152 rows=50000 loops=1) |
-> WindowAgg (cost=9187.09..9234.58 rows=1000 width=448) (actual time=739.406..741.751 rows=1000 loops=1) |
-> Sort (cost=9187.08..9189.58 rows=1000 width=817) (actual time=739.382..739.512 rows=1000 loops=1) |
Sort Key: (COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '1'::numeric)) |
Sort Method: quicksort Memory: 187kB |
-> Hash Join (cost=90.03..8773.25 rows=1000 width=817) (actual time=2.938..737.368 rows=1000 loops=1) |
Hash Cond: (p.user_id = u.id) |
-> Merge Left Join (cost=22.76..8703.34 rows=1000 width=772) (actual time=2.190..734.798 rows=1000 loops=1) |
Merge Cond: (p.id = sw_1.sw_portfolio_id) |
-> Merge Left Join (cost=0.28..3445.38 rows=1000 width=224) (actual time=2.043..573.048 rows=1000 loops=1) |
Merge Cond: (p.id = sw.sw_portfolio_id) |
-> Merge Left Join (cost=0.28..55.77 rows=1000 width=128) (actual time=1.518..6.548 rows=1000 loops=1) |
Merge Cond: (p.id = pt.pt_portfolio_id) |
-> Index Scan using portfolios_pkey on portfolios p (cost=0.28..46.27 rows=1000 width=24) (actual time=0.053..3.198 rows=1000 loops=1) |
-> Materialize (cost=0.00..4.50 rows=200 width=112) (actual time=1.459..2.160 rows=1000 loops=1) |
-> CTE Scan on portfolio_totals pt (cost=0.00..4.00 rows=200 width=112) (actual time=1.457..1.720 rows=1000 loops=1) |
-> GroupAggregate (cost=0.00..3382.61 rows=200 width=104) (actual time=0.522..565.120 rows=1000 loops=1) |
Group Key: sw.sw_portfolio_id |
-> Merge Join (cost=0.00..1754.82 rows=50009 width=72) (actual time=0.004..460.251 rows=50000 loops=1) |
Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id) |
-> CTE Scan on portfolio_totals pt_1 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..434.549 rows=1000 loops=1) |
-> Materialize (cost=0.00..1125.20 rows=50009 width=40) (actual time=0.002..12.786 rows=50000 loops=1) |
-> CTE Scan on stock_weights sw (cost=0.00..1000.18 rows=50009 width=40) (actual time=0.000..6.167 rows=50000 loops=1) |
-> Unique (cost=22.48..5250.95 rows=200 width=588) (actual time=0.145..160.204 rows=1000 loops=1) |
-> Incremental Sort (cost=22.48..5125.93 rows=50009 width=588) (actual time=0.144..155.872 rows=49951 loops=1) |
Sort Key: sw_1.sw_portfolio_id, ((sw_1.stock_market_value / NULLIF(pt_2.total_holdings_value, '0'::numeric))) DESC |
Presorted Key: sw_1.sw_portfolio_id |
Full-sort Groups: 1000 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Merge Join (cost=0.00..2504.95 rows=50009 width=588) (actual time=0.006..96.233 rows=50000 loops=1) |
Merge Cond: (pt_2.pt_portfolio_id = sw_1.sw_portfolio_id) |
-> CTE Scan on portfolio_totals pt_2 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..0.330 rows=1000 loops=1) |
-> Materialize (cost=0.00..1125.20 rows=50009 width=556) (actual time=0.002..31.566 rows=50000 loops=1) |
-> CTE Scan on stock_weights sw_1 (cost=0.00..1000.18 rows=50009 width=556) (actual time=0.000..24.237 rows=50000 loops=1) |
-> Hash (cost=54.77..54.77 rows=1000 width=21) (actual time=0.737..0.738 rows=1000 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 61kB |
-> Index Scan using users_pkey on users u (cost=0.28..54.77 rows=1000 width=21) (actual time=0.017..0.546 rows=1000 loops=1) |
Filter: ((role)::text = 'USER'::text) |
Planning Time: 1.743 ms |
Execution Time: 745.179 ms |
idx_stock_id_covering => Index Only Scan using idx_stock_id_covering on stock s + Heap Fetches: 50 (наместо претходниот Index Scan using stock_pkey кој правеше heap fetch за секој ред)
---
3. idx_portfolios_user_id (Foreign key index)
Индекс на portfolios(user_id) за оптимизација на JOIN-от portfolios => users.
CREATE INDEX idx_portfolios_user_id
ON portfolios (user_id);
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=20008.39..20010.89 rows=1000 width=448) (actual time=714.206..714.272 rows=1000 loops=1) |
Sort Key: (round(COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '0'::numeric), 4)), (((p.balance)::numeric + COALESCE(pt.total_holdings_value, '0'::numeric))) DESC|
Sort Method: quicksort Memory: 212kB |
CTE stock_weights |
-> GroupAggregate (cost=1.53..8892.19 rows=50009 width=93) (actual time=0.974..347.230 rows=50000 loops=1) |
Group Key: ph.portfolio_id, ph.stock_id, s.symbol |
-> Incremental Sort (cost=1.53..6516.76 rows=50009 width=39) (actual time=0.941..105.364 rows=50000 loops=1) |
Sort Key: ph.portfolio_id, ph.stock_id, s.symbol |
Presorted Key: ph.portfolio_id, ph.stock_id |
Full-sort Groups: 1563 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Nested Loop (cost=0.57..5006.72 rows=50009 width=39) (actual time=0.102..87.212 rows=50000 loops=1) |
-> Index Scan using idx_ph_portfolio_stock_qty on portfolio_holdings ph (cost=0.41..3754.09 rows=50009 width=26) (actual time=0.071..57.958 rows=50000 loops=1) |
-> Memoize (cost=0.15..0.17 rows=1 width=21) (actual time=0.000..0.000 rows=1 loops=50000) |
Cache Key: ph.stock_id |
Cache Mode: logical |
Hits: 49950 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 7kB |
-> Index Only Scan using idx_stock_id_covering on stock s (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50) |
Index Cond: (id = ph.stock_id) |
Heap Fetches: 50 |
CTE portfolio_totals |
-> GroupAggregate (cost=0.00..1628.79 rows=200 width=112) (actual time=1.730..416.989 rows=1000 loops=1) |
Group Key: sw_2.sw_portfolio_id |
-> CTE Scan on stock_weights sw_2 (cost=0.00..1000.18 rows=50009 width=88) (actual time=0.976..392.621 rows=50000 loops=1) |
-> WindowAgg (cost=9187.09..9234.58 rows=1000 width=448) (actual time=709.858..712.200 rows=1000 loops=1) |
-> Sort (cost=9187.08..9189.58 rows=1000 width=817) (actual time=709.836..709.963 rows=1000 loops=1) |
Sort Key: (COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '1'::numeric)) |
Sort Method: quicksort Memory: 187kB |
-> Hash Join (cost=90.03..8773.25 rows=1000 width=817) (actual time=3.217..707.863 rows=1000 loops=1) |
Hash Cond: (p.user_id = u.id) |
-> Merge Left Join (cost=22.76..8703.34 rows=1000 width=772) (actual time=2.453..705.425 rows=1000 loops=1) |
Merge Cond: (p.id = sw_1.sw_portfolio_id) |
-> Merge Left Join (cost=0.28..3445.38 rows=1000 width=224) (actual time=2.306..550.820 rows=1000 loops=1) |
Merge Cond: (p.id = sw.sw_portfolio_id) |
-> Merge Left Join (cost=0.28..55.77 rows=1000 width=128) (actual time=1.793..6.307 rows=1000 loops=1) |
Merge Cond: (p.id = pt.pt_portfolio_id) |
-> Index Scan using portfolios_pkey on portfolios p (cost=0.28..46.27 rows=1000 width=24) (actual time=0.052..2.793 rows=1000 loops=1) |
-> Materialize (cost=0.00..4.50 rows=200 width=112) (actual time=1.735..2.412 rows=1000 loops=1) |
-> CTE Scan on portfolio_totals pt (cost=0.00..4.00 rows=200 width=112) (actual time=1.732..1.985 rows=1000 loops=1) |
-> GroupAggregate (cost=0.00..3382.61 rows=200 width=104) (actual time=0.510..543.124 rows=1000 loops=1) |
Group Key: sw.sw_portfolio_id |
-> Merge Join (cost=0.00..1754.82 rows=50009 width=72) (actual time=0.005..441.070 rows=50000 loops=1) |
Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id) |
-> CTE Scan on portfolio_totals pt_1 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..416.394 rows=1000 loops=1) |
-> Materialize (cost=0.00..1125.20 rows=50009 width=40) (actual time=0.003..12.318 rows=50000 loops=1) |
-> CTE Scan on stock_weights sw (cost=0.00..1000.18 rows=50009 width=40) (actual time=0.000..5.926 rows=50000 loops=1) |
-> Unique (cost=22.48..5250.95 rows=200 width=588) (actual time=0.144..153.167 rows=1000 loops=1) |
-> Incremental Sort (cost=22.48..5125.93 rows=50009 width=588) (actual time=0.144..149.002 rows=49951 loops=1) |
Sort Key: sw_1.sw_portfolio_id, ((sw_1.stock_market_value / NULLIF(pt_2.total_holdings_value, '0'::numeric))) DESC |
Presorted Key: sw_1.sw_portfolio_id |
Full-sort Groups: 1000 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
-> Merge Join (cost=0.00..2504.95 rows=50009 width=588) (actual time=0.006..91.481 rows=50000 loops=1) |
Merge Cond: (pt_2.pt_portfolio_id = sw_1.sw_portfolio_id) |
-> CTE Scan on portfolio_totals pt_2 (cost=0.00..4.00 rows=200 width=40) (actual time=0.000..0.324 rows=1000 loops=1) |
-> Materialize (cost=0.00..1125.20 rows=50009 width=556) (actual time=0.002..28.890 rows=50000 loops=1) |
-> CTE Scan on stock_weights sw_1 (cost=0.00..1000.18 rows=50009 width=556) (actual time=0.000..21.838 rows=50000 loops=1) |
-> Hash (cost=54.77..54.77 rows=1000 width=21) (actual time=0.752..0.753 rows=1000 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 61kB |
-> Index Scan using users_pkey on users u (cost=0.28..54.77 rows=1000 width=21) (actual time=0.018..0.522 rows=1000 loops=1) |
Filter: ((role)::text = 'USER'::text) |
Planning Time: 1.951 ms |
Execution Time: 715.560 ms |
idx_portfolios_user_id => Овој не се гледа во планот — планерот продолжи да го користи portfolios_pkey. Причина: сите 1.000 портфолија се читаат (нема селективен филтер), па PK скенот е поефикасен.
---
Сумарно:
75% побрзо.
Извештај за волатилност на акции (report_stock_volatility_and_activity)
SQL:
EXPLAIN ANALYZE
WITH
daily_returns AS (
SELECT
sh.stock_id AS dr_stock_id,
sh.price AS dr_price,
(sh.price - LAG(sh.price) OVER (
PARTITION BY sh.stock_id ORDER BY sh.timestamp
)) / NULLIF(LAG(sh.price) OVER (
PARTITION BY sh.stock_id ORDER BY sh.timestamp
), 0) AS dr_daily_return
FROM stock_history sh
),
price_stats AS (
SELECT
dr.dr_stock_id AS ps_stock_id,
COUNT(*) AS ps_total_points,
MIN(dr.dr_price) AS ps_min_price,
MAX(dr.dr_price) AS ps_max_price,
AVG(dr.dr_price) AS ps_avg_price,
COALESCE(STDDEV(dr.dr_daily_return) * 100, 0) AS ps_volatility_pct
FROM daily_returns dr
GROUP BY dr.dr_stock_id
),
price_ordered AS (
SELECT
sh.stock_id AS po_stock_id,
ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp) AS po_rn,
sh.price AS po_price
FROM stock_history sh
),
trend_calc AS (
SELECT
po.po_stock_id AS tc_stock_id,
ROUND(
((COUNT(*) * SUM(po.po_rn * po.po_price) - SUM(po.po_rn) * SUM(po.po_price))
/ NULLIF(COUNT(*) * SUM(po.po_rn * po.po_rn) - SUM(po.po_rn) * SUM(po.po_rn), 0))::NUMERIC
, 6) AS tc_slope
FROM price_ordered po
GROUP BY po.po_stock_id
),
txn_stats AS (
SELECT
t.stock_id AS ts_stock_id,
SUM(t.price * t.quantity) AS ts_total_volume,
COUNT(*) AS ts_total_count,
COUNT(*) FILTER (WHERE t.type = 'BUY') AS ts_buy_count,
COUNT(*) FILTER (WHERE t.type = 'SELL') AS ts_sell_count
FROM transactions t
GROUP BY t.stock_id
),
watchlist_stats AS (
SELECT
w.stock_id AS ws_stock_id,
COUNT(DISTINCT w.user_id) AS ws_active_watchers
FROM watchlist w
GROUP BY w.stock_id
),
pending_stats AS (
SELECT
s.id AS pst_stock_id,
COUNT(tr.id) AS pst_pending_count
FROM trade_request tr
JOIN stock s ON tr.stock_symbol = s.symbol
WHERE tr.status = 'PENDING'
GROUP BY s.id
)
SELECT
s.id,
s.symbol,
s.name,
s.current_price,
COALESCE(ps.ps_min_price, s.current_price),
COALESCE(ps.ps_max_price, s.current_price),
ROUND(
(
(COALESCE(ps.ps_max_price, s.current_price)
- COALESCE(ps.ps_min_price, s.current_price))
/ NULLIF(COALESCE(ps.ps_min_price, s.current_price), 0) * 100
)::NUMERIC
, 2),
COALESCE(ps.ps_avg_price, s.current_price),
COALESCE(ps.ps_volatility_pct, 0),
COALESCE(ps.ps_total_points, 0),
COALESCE(ts.ts_total_volume, 0),
COALESCE(ts.ts_total_count, 0),
COALESCE(ts.ts_buy_count, 0),
COALESCE(ts.ts_sell_count, 0),
ROUND(
(COALESCE(ts.ts_buy_count, 0)::NUMERIC
/ NULLIF(COALESCE(ts.ts_sell_count, 0), 0))
, 2),
COALESCE(ws.ws_active_watchers, 0),
COALESCE(pst.pst_pending_count, 0),
CASE
WHEN tc.tc_slope > 0.01 THEN 'UPTREND'
WHEN tc.tc_slope < -0.01 THEN 'DOWNTREND'
ELSE 'SIDEWAYS'
END,
COALESCE(tc.tc_slope, 0),
ROUND(
(COALESCE(ws.ws_active_watchers, 0) * 2.0
+ COALESCE(ts.ts_total_count, 0) * 1.0
+ COALESCE(pst.pst_pending_count, 0) * 3.0)
, 2),
RANK() OVER (ORDER BY COALESCE(ps.ps_volatility_pct, 0) DESC),
RANK() OVER (ORDER BY (
COALESCE(ws.ws_active_watchers, 0) * 2.0
+ COALESCE(ts.ts_total_count, 0)
+ COALESCE(pst.pst_pending_count, 0) * 3.0
) DESC)
FROM stock s
LEFT JOIN price_stats ps ON s.id = ps.ps_stock_id
LEFT JOIN trend_calc tc ON s.id = tc.tc_stock_id
LEFT JOIN txn_stats ts ON s.id = ts.ts_stock_id
LEFT JOIN watchlist_stats ws ON s.id = ws.ws_stock_id
LEFT JOIN pending_stats pst ON s.id = pst.pst_stock_id
ORDER BY ps.ps_volatility_pct DESC;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=50000113394.02..50000113394.27 rows=100 width=338) (actual time=1981.897..1981.915 rows=100 loops=1) |
Sort Key: (COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)) DESC |
Sort Method: quicksort Memory: 46kB |
-> WindowAgg (cost=50000113381.71..50000113390.70 rows=100 width=338) (actual time=1981.413..1981.748 rows=100 loops=1) |
-> Sort (cost=50000113381.70..50000113381.95 rows=100 width=202) (actual time=1981.382..1981.402 rows=100 loops=1) |
Sort Key: (COALESCE((COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)), '0'::double precision)) DESC|
Sort Method: quicksort Memory: 39kB |
-> WindowAgg (cost=50000113374.89..50000113378.37 rows=100 width=202) (actual time=1981.205..1981.305 rows=100 loops=1) |
-> Sort (cost=50000113374.87..50000113375.12 rows=100 width=194) (actual time=1981.148..1981.166 rows=100 loops=1) |
Sort Key: (((((COALESCE((count(DISTINCT w.user_id)), '0'::bigint))::numeric * 2.0) + (COALESCE((count(*)), '0'::bigint))::numeric) + ((COALESCE((count(tr.id)), '0'::bigint))::numeric * 3.0))) DESC |
Sort Method: quicksort Memory: 38kB |
-> Merge Left Join (cost=50000082962.33..50000113371.55 rows=100 width=194) (actual time=1674.083..1980.884 rows=100 loops=1) |
Merge Cond: (s.id = s_1.id) |
-> Merge Left Join (cost=40000082962.19..40000107889.58 rows=100 width=146) (actual time=1671.513..1933.067 rows=100 loops=1) |
Merge Cond: (s.id = w.stock_id) |
-> Merge Left Join (cost=30000080823.42..30000105599.91 rows=100 width=138) (actual time=1657.677..1914.227 rows=100 loops=1) |
Merge Cond: (s.id = t.stock_id) |
-> Merge Left Join (cost=20000058569.46..20000079595.67 rows=100 width=106) (actual time=1506.027..1762.509 rows=100 loops=1) |
Merge Cond: (s.id = sh_1.stock_id) |
-> Merge Left Join (cost=10000029284.80..10000039803.53 rows=100 width=74) (actual time=1355.378..1508.374 rows=100 loops=1) |
Merge Cond: (s.id = sh.stock_id) |
-> Index Scan using stock_pkey on stock s (cost=0.14..14.64 rows=100 width=34) (actual time=0.031..0.094 rows=100 loops=1) |
-> GroupAggregate (cost=10000029284.66..10000039786.39 rows=100 width=48) (actual time=167.128..319.895 rows=100 loops=1) |
Group Key: sh.stock_id |
-> WindowAgg (cost=10000029284.66..10000034784.64 rows=200000 width=28) (actual time=165.583..292.548 rows=200000 loops=1) |
-> Sort (cost=10000029284.64..10000029784.64 rows=200000 width=20) (actual time=165.491..192.959 rows=200000 loops=1) |
Sort Key: sh.stock_id, sh."timestamp" |
Sort Method: external merge Disk: 6680kB |
-> Seq Scan on stock_history sh (cost=10000000000.00..10000003471.00 rows=200000 width=20) (actual time=0.126..33.684 rows=200000 loops=1) |
-> GroupAggregate (cost=10000029284.66..10000039789.64 rows=100 width=40) (actual time=150.618..253.952 rows=100 loops=1) |
Group Key: sh_1.stock_id |
-> WindowAgg (cost=10000029284.66..10000033284.64 rows=200000 width=28) (actual time=149.544..233.175 rows=200000 loops=1) |
-> Sort (cost=10000029284.64..10000029784.64 rows=200000 width=20) (actual time=149.487..176.585 rows=200000 loops=1) |
Sort Key: sh_1.stock_id, sh_1."timestamp" |
Sort Method: external merge Disk: 6680kB |
-> Seq Scan on stock_history sh_1 (cost=10000000000.00..10000003471.00 rows=200000 width=20) (actual time=0.025..20.128 rows=200000 loops=1) |
-> GroupAggregate (cost=10000022253.95..10000026003.96 rows=1 width=40) (actual time=151.624..151.625 rows=1 loops=1) |
Group Key: t.stock_id |
-> Sort (cost=10000022253.95..10000022628.95 rows=150000 width=24) (actual time=107.994..127.905 rows=150000 loops=1) |
Sort Key: t.stock_id |
Sort Method: external merge Disk: 5096kB |
-> Seq Scan on transactions t (cost=10000000000.00..10000003205.00 rows=150000 width=24) (actual time=0.081..32.995 rows=150000 loops=1) |
-> GroupAggregate (cost=10000002138.77..10000002288.97 rows=20 width=16) (actual time=13.812..18.748 rows=20 loops=1) |
Group Key: w.stock_id |
-> Sort (cost=10000002138.77..10000002188.77 rows=20000 width=16) (actual time=13.523..16.642 rows=20000 loops=1) |
Sort Key: w.stock_id, w.user_id |
Sort Method: external merge Disk: 512kB |
-> Seq Scan on watchlist w (cost=10000000000.00..10000000367.00 rows=20000 width=16) (actual time=0.044..3.934 rows=20000 loops=1) |
-> GroupAggregate (cost=10000000000.14..10000005477.72 rows=100 width=16) (actual time=2.541..47.575 rows=100 loops=1) |
Group Key: s_1.id |
-> Nested Loop (cost=10000000000.14..10000005459.00 rows=3544 width=16) (actual time=0.120..47.189 rows=3544 loops=1) |
Join Filter: ((s_1.symbol)::text = (tr.stock_symbol)::text) |
Rows Removed by Join Filter: 350856 |
-> Index Scan using stock_pkey on stock s_1 (cost=0.14..14.64 rows=100 width=13) (actual time=0.033..0.347 rows=100 loops=1) |
-> Materialize (cost=10000000000.00..10000000137.22 rows=3544 width=13) (actual time=0.001..0.190 rows=3544 loops=100) |
-> Seq Scan on trade_request tr (cost=10000000000.00..10000000119.50 rows=3544 width=13) (actual time=0.065..1.321 rows=3544 loops=1) |
Filter: ((status)::text = 'PENDING'::text) |
Rows Removed by Filter: 1456 |
Planning Time: 5.352 ms |
JIT: |
Functions: 72 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
Timing: Generation 9.390 ms (Deform 3.248 ms), Inlining 173.252 ms, Optimization 557.229 ms, Emission 458.237 ms, Total 1198.107 ms |
Execution Time: 2034.799 ms |
Индекси:
---
1. idx_sh_stock_timestamp
CREATE INDEX idx_sh_stock_timestamp
ON stock_history (stock_id, timestamp)
INCLUDE (price);
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=30000067769.58..30000067769.83 rows=100 width=338) (actual time=1676.027..1676.046 rows=100 loops=1) |
Sort Key: (COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)) DESC |
Sort Method: quicksort Memory: 46kB |
-> WindowAgg (cost=30000067757.27..30000067766.25 rows=100 width=338) (actual time=1675.578..1675.913 rows=100 loops=1) |
-> Sort (cost=30000067757.25..30000067757.50 rows=100 width=202) (actual time=1675.548..1675.566 rows=100 loops=1) |
Sort Key: (COALESCE((COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)), '0'::double precision)) DESC|
Sort Method: quicksort Memory: 39kB |
-> WindowAgg (cost=30000067750.45..30000067753.93 rows=100 width=202) (actual time=1675.391..1675.492 rows=100 loops=1) |
-> Sort (cost=30000067750.43..30000067750.68 rows=100 width=194) (actual time=1675.324..1675.341 rows=100 loops=1) |
Sort Key: (((((COALESCE((count(DISTINCT w.user_id)), '0'::bigint))::numeric * 2.0) + (COALESCE((count(*)), '0'::bigint))::numeric) + ((COALESCE((count(tr.id)), '0'::bigint))::numeric * 3.0))) DESC |
Sort Method: quicksort Memory: 38kB |
-> Merge Left Join (cost=30000024393.95..30000067747.11 rows=100 width=194) (actual time=1319.475..1675.091 rows=100 loops=1) |
Merge Cond: (s.id = s_1.id) |
-> Merge Left Join (cost=20000024393.81..20000062265.14 rows=100 width=146) (actual time=1317.005..1627.105 rows=100 loops=1) |
Merge Cond: (s.id = w.stock_id) |
-> Merge Left Join (cost=10000022255.04..10000059975.47 rows=100 width=138) (actual time=1303.857..1608.799 rows=100 loops=1) |
Merge Cond: (s.id = t.stock_id) |
-> Merge Left Join (cost=1.09..33971.23 rows=100 width=106) (actual time=1155.604..1460.455 rows=100 loops=1) |
Merge Cond: (s.id = sh_1.stock_id) |
-> Merge Left Join (cost=0.61..16991.31 rows=100 width=74) (actual time=1154.257..1339.341 rows=100 loops=1) |
Merge Cond: (s.id = sh.stock_id) |
-> Index Scan using stock_pkey on stock s (cost=0.14..14.64 rows=100 width=34) (actual time=0.157..0.215 rows=100 loops=1) |
-> GroupAggregate (cost=0.47..16974.17 rows=100 width=48) (actual time=2.079..186.903 rows=100 loops=1) |
Group Key: sh.stock_id |
-> WindowAgg (cost=0.47..11972.42 rows=200000 width=28) (actual time=0.172..158.424 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.124..43.800 rows=200000 loops=1) |
Heap Fetches: 0 |
-> GroupAggregate (cost=0.47..16977.42 rows=100 width=40) (actual time=1.320..120.928 rows=100 loops=1) |
Group Key: sh_1.stock_id |
-> WindowAgg (cost=0.47..10472.42 rows=200000 width=28) (actual time=0.031..99.876 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh_1 (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.018..30.217 rows=200000 loops=1) |
Heap Fetches: 0 |
-> GroupAggregate (cost=10000022253.95..10000026003.96 rows=1 width=40) (actual time=148.227..148.228 rows=1 loops=1) |
Group Key: t.stock_id |
-> Sort (cost=10000022253.95..10000022628.95 rows=150000 width=24) (actual time=104.714..124.482 rows=150000 loops=1) |
Sort Key: t.stock_id |
Sort Method: external merge Disk: 5096kB |
-> Seq Scan on transactions t (cost=10000000000.00..10000003205.00 rows=150000 width=24) (actual time=0.075..29.959 rows=150000 loops=1) |
-> GroupAggregate (cost=10000002138.77..10000002288.97 rows=20 width=16) (actual time=13.124..18.215 rows=20 loops=1) |
Group Key: w.stock_id |
-> Sort (cost=10000002138.77..10000002188.77 rows=20000 width=16) (actual time=12.831..16.052 rows=20000 loops=1) |
Sort Key: w.stock_id, w.user_id |
Sort Method: external merge Disk: 512kB |
-> Seq Scan on watchlist w (cost=10000000000.00..10000000367.00 rows=20000 width=16) (actual time=0.054..3.396 rows=20000 loops=1) |
-> GroupAggregate (cost=10000000000.14..10000005477.72 rows=100 width=16) (actual time=2.438..47.689 rows=100 loops=1) |
Group Key: s_1.id |
-> Nested Loop (cost=10000000000.14..10000005459.00 rows=3544 width=16) (actual time=0.109..47.268 rows=3544 loops=1) |
Join Filter: ((s_1.symbol)::text = (tr.stock_symbol)::text) |
Rows Removed by Join Filter: 350856 |
-> Index Scan using stock_pkey on stock s_1 (cost=0.14..14.64 rows=100 width=13) (actual time=0.040..0.293 rows=100 loops=1) |
-> Materialize (cost=10000000000.00..10000000137.22 rows=3544 width=13) (actual time=0.001..0.190 rows=3544 loops=100) |
-> Seq Scan on trade_request tr (cost=10000000000.00..10000000119.50 rows=3544 width=13) (actual time=0.047..1.240 rows=3544 loops=1) |
Filter: ((status)::text = 'PENDING'::text) |
Rows Removed by Filter: 1456 |
Planning Time: 3.885 ms |
JIT: |
Functions: 68 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
Timing: Generation 4.970 ms (Deform 1.546 ms), Inlining 133.811 ms, Optimization 561.492 ms, Emission 457.098 ms, Total 1157.371 ms |
Execution Time: 1720.407 ms |
idx_sh_stock_timestamp => Baseline: Seq Scan on stock_history + Sort Method: external merge Disk: 6680kB (два пати). Со индексот: Index Only Scan using idx_sh_stock_timestamp + Heap Fetches: 0 (два пати, за daily_returns и price_ordered).
---
2. idx_txn_stock_timestamp
CREATE INDEX idx_txn_stock_timestamp
ON transactions (stock_id, timestamp)
INCLUDE (price, quantity, type);
SET enable_seqscan = off;
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=20000049527.58..20000049527.83 rows=100 width=338) (actual time=1646.056..1646.190 rows=100 loops=1) |
Sort Key: (COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)) DESC |
Sort Method: quicksort Memory: 46kB |
-> WindowAgg (cost=20000049515.28..20000049524.26 rows=100 width=338) (actual time=1645.651..1646.098 rows=100 loops=1) |
-> Sort (cost=20000049515.26..20000049515.51 rows=100 width=202) (actual time=1645.616..1645.752 rows=100 loops=1) |
Sort Key: (COALESCE((COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)), '0'::double precision)) DESC|
Sort Method: quicksort Memory: 39kB |
-> WindowAgg (cost=20000049508.46..20000049511.94 rows=100 width=202) (actual time=1645.448..1645.673 rows=100 loops=1) |
-> Sort (cost=20000049508.44..20000049508.69 rows=100 width=194) (actual time=1645.383..1645.519 rows=100 loops=1) |
Sort Key: (((((COALESCE((count(DISTINCT w.user_id)), '0'::bigint))::numeric * 2.0) + (COALESCE((count(*)), '0'::bigint))::numeric) + ((COALESCE((count(tr.id)), '0'::bigint))::numeric * 3.0))) DESC |
Sort Method: quicksort Memory: 38kB |
-> Merge Left Join (cost=20000003140.44..20000049505.12 rows=100 width=194) (actual time=1286.813..1645.278 rows=100 loops=1) |
Merge Cond: (s.id = s_1.id) |
-> Merge Left Join (cost=10000003140.30..10000044023.15 rows=100 width=146) (actual time=1284.382..1597.207 rows=100 loops=1) |
Merge Cond: (s.id = w.stock_id) |
-> Merge Left Join (cost=1001.53..41733.47 rows=100 width=138) (actual time=1271.103..1578.714 rows=100 loops=1) |
Merge Cond: (s.id = t.stock_id) |
-> Merge Left Join (cost=1.09..33971.23 rows=100 width=106) (actual time=1029.039..1336.446 rows=100 loops=1) |
Merge Cond: (s.id = sh_1.stock_id) |
-> Merge Left Join (cost=0.61..16991.31 rows=100 width=74) (actual time=1027.729..1214.561 rows=100 loops=1) |
Merge Cond: (s.id = sh.stock_id) |
-> Index Scan using stock_pkey on stock s (cost=0.14..14.64 rows=100 width=34) (actual time=0.078..0.129 rows=100 loops=1) |
-> GroupAggregate (cost=0.47..16974.17 rows=100 width=48) (actual time=2.043..188.628 rows=100 loops=1) |
Group Key: sh.stock_id |
-> WindowAgg (cost=0.47..11972.42 rows=200000 width=28) (actual time=0.159..160.690 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.111..44.612 rows=200000 loops=1) |
Heap Fetches: 0 |
-> GroupAggregate (cost=0.47..16977.42 rows=100 width=40) (actual time=1.285..121.682 rows=100 loops=1) |
Group Key: sh_1.stock_id |
-> WindowAgg (cost=0.47..10472.42 rows=200000 width=28) (actual time=0.030..101.139 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh_1 (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.018..30.274 rows=200000 loops=1) |
Heap Fetches: 0 |
-> Finalize GroupAggregate (cost=1000.44..7761.97 rows=1 width=40) (actual time=242.038..242.157 rows=1 loops=1) |
Group Key: t.stock_id |
-> Gather Merge (cost=1000.44..7761.93 rows=2 width=40) (actual time=241.940..242.069 rows=3 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Partial GroupAggregate (cost=0.42..6761.68 rows=1 width=40) (actual time=147.936..147.937 rows=1 loops=3) |
Group Key: t.stock_id |
-> Parallel Index Only Scan using idx_txn_stock_timestamp on transactions t (cost=0.42..5355.42 rows=62500 width=24) (actual time=0.087..11.496 rows=50000 loops=3) |
Heap Fetches: 0 |
-> GroupAggregate (cost=10000002138.77..10000002288.97 rows=20 width=16) (actual time=13.241..18.369 rows=20 loops=1) |
Group Key: w.stock_id |
-> Sort (cost=10000002138.77..10000002188.77 rows=20000 width=16) (actual time=12.943..16.184 rows=20000 loops=1) |
Sort Key: w.stock_id, w.user_id |
Sort Method: external merge Disk: 512kB |
-> Seq Scan on watchlist w (cost=10000000000.00..10000000367.00 rows=20000 width=16) (actual time=0.063..3.306 rows=20000 loops=1) |
-> GroupAggregate (cost=10000000000.14..10000005477.72 rows=100 width=16) (actual time=2.399..47.769 rows=100 loops=1) |
Group Key: s_1.id |
-> Nested Loop (cost=10000000000.14..10000005459.00 rows=3544 width=16) (actual time=0.097..47.346 rows=3544 loops=1) |
Join Filter: ((s_1.symbol)::text = (tr.stock_symbol)::text) |
Rows Removed by Join Filter: 350856 |
-> Index Scan using stock_pkey on stock s_1 (cost=0.14..14.64 rows=100 width=13) (actual time=0.019..0.281 rows=100 loops=1) |
-> Materialize (cost=10000000000.00..10000000137.22 rows=3544 width=13) (actual time=0.001..0.190 rows=3544 loops=100) |
-> Seq Scan on trade_request tr (cost=10000000000.00..10000000119.50 rows=3544 width=13) (actual time=0.056..1.147 rows=3544 loops=1) |
Filter: ((status)::text = 'PENDING'::text) |
Rows Removed by Filter: 1456 |
Planning Time: 2.269 ms |
JIT: |
Functions: 73 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
Timing: Generation 8.438 ms (Deform 1.947 ms), Inlining 309.921 ms, Optimization 601.312 ms, Emission 504.049 ms, Total 1423.720 ms |
Execution Time: 1654.111 ms |
idx_txn_stock_timestamp → Baseline: Seq Scan on transactions + Sort Method: external merge Disk: 5096kB. Со индексот: Parallel Index Only Scan using idx_txn_stock_timestamp + Heap Fetches: 0 + планерот активира parallel workers (2 workers).
3. idx_watchlist_stock_user
CREATE INDEX idx_watchlist_stock_user
ON watchlist (stock_id, user_id);
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=10000047955.10..10000047955.35 rows=100 width=338) (actual time=1601.286..1601.442 rows=100 loops=1) |
Sort Key: (COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)) DESC |
Sort Method: quicksort Memory: 46kB |
-> WindowAgg (cost=10000047942.80..10000047951.78 rows=100 width=338) (actual time=1600.879..1601.347 rows=100 loops=1) |
-> Sort (cost=10000047942.78..10000047943.03 rows=100 width=202) (actual time=1600.850..1601.007 rows=100 loops=1) |
Sort Key: (COALESCE((COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)), '0'::double precision)) DESC|
Sort Method: quicksort Memory: 39kB |
-> WindowAgg (cost=10000047935.97..10000047939.46 rows=100 width=202) (actual time=1600.697..1600.935 rows=100 loops=1) |
-> Sort (cost=10000047935.96..10000047936.21 rows=100 width=194) (actual time=1600.631..1600.788 rows=100 loops=1) |
Sort Key: (((((COALESCE((count(DISTINCT w.user_id)), '0'::bigint))::numeric * 2.0) + (COALESCE((count(*)), '0'::bigint))::numeric) + ((COALESCE((count(tr.id)), '0'::bigint))::numeric * 3.0))) DESC |
Sort Method: quicksort Memory: 38kB |
-> Merge Left Join (cost=10000001001.96..10000047932.63 rows=100 width=194) (actual time=1244.977..1600.547 rows=100 loops=1) |
Merge Cond: (s.id = s_1.id) |
-> Merge Left Join (cost=1001.82..42450.66 rows=100 width=146) (actual time=1242.575..1552.403 rows=100 loops=1) |
Merge Cond: (s.id = w.stock_id) |
-> Merge Left Join (cost=1001.53..41733.47 rows=100 width=138) (actual time=1242.105..1546.828 rows=100 loops=1) |
Merge Cond: (s.id = t.stock_id) |
-> Merge Left Join (cost=1.09..33971.23 rows=100 width=106) (actual time=997.055..1301.564 rows=100 loops=1) |
Merge Cond: (s.id = sh_1.stock_id) |
-> Merge Left Join (cost=0.61..16991.31 rows=100 width=74) (actual time=995.695..1179.337 rows=100 loops=1) |
Merge Cond: (s.id = sh.stock_id) |
-> Index Scan using stock_pkey on stock s (cost=0.14..14.64 rows=100 width=34) (actual time=0.031..0.104 rows=100 loops=1) |
-> GroupAggregate (cost=0.47..16974.17 rows=100 width=48) (actual time=2.087..185.453 rows=100 loops=1) |
Group Key: sh.stock_id |
-> WindowAgg (cost=0.47..11972.42 rows=200000 width=28) (actual time=0.163..157.533 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.114..43.186 rows=200000 loops=1) |
Heap Fetches: 0 |
-> GroupAggregate (cost=0.47..16977.42 rows=100 width=40) (actual time=1.330..122.037 rows=100 loops=1) |
Group Key: sh_1.stock_id |
-> WindowAgg (cost=0.47..10472.42 rows=200000 width=28) (actual time=0.035..100.974 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh_1 (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.020..30.254 rows=200000 loops=1) |
Heap Fetches: 0 |
-> Finalize GroupAggregate (cost=1000.44..7761.97 rows=1 width=40) (actual time=244.996..245.137 rows=1 loops=1) |
Group Key: t.stock_id |
-> Gather Merge (cost=1000.44..7761.93 rows=2 width=40) (actual time=244.927..245.070 rows=3 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Partial GroupAggregate (cost=0.42..6761.68 rows=1 width=40) (actual time=149.842..149.844 rows=1 loops=3) |
Group Key: t.stock_id |
-> Parallel Index Only Scan using idx_txn_stock_timestamp on transactions t (cost=0.42..5355.42 rows=62500 width=24) (actual time=0.108..11.561 rows=50000 loops=3) |
Heap Fetches: 0 |
-> GroupAggregate (cost=0.29..716.49 rows=20 width=16) (actual time=0.446..5.468 rows=20 loops=1) |
Group Key: w.stock_id |
-> Index Only Scan using idx_watchlist_stock_user on watchlist w (cost=0.29..616.29 rows=20000 width=16) (actual time=0.142..3.794 rows=20000 loops=1) |
Heap Fetches: 0 |
-> GroupAggregate (cost=10000000000.14..10000005477.72 rows=100 width=16) (actual time=2.370..47.843 rows=100 loops=1) |
Group Key: s_1.id |
-> Nested Loop (cost=10000000000.14..10000005459.00 rows=3544 width=16) (actual time=0.105..47.438 rows=3544 loops=1) |
Join Filter: ((s_1.symbol)::text = (tr.stock_symbol)::text) |
Rows Removed by Join Filter: 350856 |
-> Index Scan using stock_pkey on stock s_1 (cost=0.14..14.64 rows=100 width=13) (actual time=0.028..0.264 rows=100 loops=1) |
-> Materialize (cost=10000000000.00..10000000137.22 rows=3544 width=13) (actual time=0.001..0.191 rows=3544 loops=100) |
-> Seq Scan on trade_request tr (cost=10000000000.00..10000000119.50 rows=3544 width=13) (actual time=0.053..1.160 rows=3544 loops=1) |
Filter: ((status)::text = 'PENDING'::text) |
Rows Removed by Filter: 1456 |
Planning Time: 2.079 ms |
JIT: |
Functions: 69 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
Timing: Generation 9.372 ms (Deform 1.922 ms), Inlining 312.699 ms, Optimization 583.995 ms, Emission 491.538 ms, Total 1397.603 ms |
Execution Time: 1609.894 ms |
idx_watchlist_stock_user → Baseline: Seq Scan on watchlist + Sort Method: external merge Disk: 512kB. Со индексот: Index Only Scan using idx_watchlist_stock_user + Heap Fetches: 0.
4. idx_tr_status_symbol
CREATE INDEX idx_tr_status_symbol
ON trade_request (status, stock_symbol)
WHERE status = 'PENDING';
EXPLAIN ANALYZE
...
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort (cost=42665.89..42666.14 rows=100 width=338) (actual time=419.467..419.632 rows=100 loops=1) |
Sort Key: (COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)) DESC |
Sort Method: quicksort Memory: 46kB |
-> WindowAgg (cost=42653.59..42662.57 rows=100 width=338) (actual time=418.999..419.538 rows=100 loops=1) |
-> Sort (cost=42653.57..42653.82 rows=100 width=202) (actual time=418.977..419.144 rows=100 loops=1) |
Sort Key: (COALESCE((COALESCE((stddev((((sh.price - lag(sh.price) OVER (?)) / NULLIF(lag(sh.price) OVER (?), '0'::double precision)))) * '100'::double precision), '0'::double precision)), '0'::double precision)) DESC|
Sort Method: quicksort Memory: 39kB |
-> WindowAgg (cost=42646.77..42650.25 rows=100 width=202) (actual time=418.825..419.071 rows=100 loops=1) |
-> Sort (cost=42646.75..42647.00 rows=100 width=194) (actual time=418.813..418.977 rows=100 loops=1) |
Sort Key: (((((COALESCE((count(DISTINCT w.user_id)), '0'::bigint))::numeric * 2.0) + (COALESCE((count(*)), '0'::bigint))::numeric) + ((COALESCE(pst.pst_pending_count, '0'::bigint))::numeric * 3.0))) DESC |
Sort Method: quicksort Memory: 38kB |
-> Hash Left Join (cost=1192.56..42643.43 rows=100 width=194) (actual time=62.141..418.751 rows=100 loops=1) |
Hash Cond: (s.id = pst.pst_stock_id) |
-> Merge Left Join (cost=1001.82..42450.66 rows=100 width=146) (actual time=59.398..415.685 rows=100 loops=1) |
Merge Cond: (s.id = w.stock_id) |
-> Merge Left Join (cost=1001.53..41733.47 rows=100 width=138) (actual time=59.023..409.474 rows=100 loops=1) |
Merge Cond: (s.id = t.stock_id) |
-> Merge Left Join (cost=1.09..33971.23 rows=100 width=106) (actual time=3.629..353.874 rows=100 loops=1) |
Merge Cond: (s.id = sh_1.stock_id) |
-> Merge Left Join (cost=0.61..16991.31 rows=100 width=74) (actual time=2.155..211.070 rows=100 loops=1) |
Merge Cond: (s.id = sh.stock_id) |
-> Index Scan using stock_pkey on stock s (cost=0.14..14.64 rows=100 width=34) (actual time=0.031..0.224 rows=100 loops=1) |
-> GroupAggregate (cost=0.47..16974.17 rows=100 width=48) (actual time=2.119..210.639 rows=100 loops=1) |
Group Key: sh.stock_id |
-> WindowAgg (cost=0.47..11972.42 rows=200000 width=28) (actual time=0.072..174.742 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.060..45.741 rows=200000 loops=1) |
Heap Fetches: 0 |
-> GroupAggregate (cost=0.47..16977.42 rows=100 width=40) (actual time=1.471..142.632 rows=100 loops=1) |
Group Key: sh_1.stock_id |
-> WindowAgg (cost=0.47..10472.42 rows=200000 width=28) (actual time=0.024..108.616 rows=200000 loops=1) |
-> Index Only Scan using idx_sh_stock_timestamp on stock_history sh_1 (cost=0.42..6972.42 rows=200000 width=20) (actual time=0.021..30.572 rows=200000 loops=1) |
Heap Fetches: 0 |
-> Finalize GroupAggregate (cost=1000.44..7761.97 rows=1 width=40) (actual time=55.389..55.533 rows=1 loops=1) |
Group Key: t.stock_id |
-> Gather Merge (cost=1000.44..7761.93 rows=2 width=40) (actual time=55.378..55.522 rows=3 loops=1) |
Workers Planned: 2 |
Workers Launched: 2 |
-> Partial GroupAggregate (cost=0.42..6761.68 rows=1 width=40) (actual time=25.926..25.927 rows=1 loops=3) |
Group Key: t.stock_id |
-> Parallel Index Only Scan using idx_txn_stock_timestamp on transactions t (cost=0.42..5355.42 rows=62500 width=24) (actual time=0.103..16.865 rows=50000 loops=3) |
Heap Fetches: 0 |
-> GroupAggregate (cost=0.29..716.49 rows=20 width=16) (actual time=0.369..6.122 rows=20 loops=1) |
Group Key: w.stock_id |
-> Index Only Scan using idx_watchlist_stock_user on watchlist w (cost=0.29..616.29 rows=20000 width=16) (actual time=0.086..3.968 rows=20000 loops=1) |
Heap Fetches: 0 |
-> Hash (cost=189.49..189.49 rows=100 width=16) (actual time=2.708..2.714 rows=100 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 13kB |
-> Subquery Scan on pst (cost=187.49..189.49 rows=100 width=16) (actual time=2.657..2.692 rows=100 loops=1) |
-> HashAggregate (cost=187.49..188.49 rows=100 width=16) (actual time=2.656..2.677 rows=100 loops=1) |
Group Key: s_1.id |
Batches: 1 Memory Usage: 24kB |
-> Hash Join (cost=58.78..169.77 rows=3544 width=16) (actual time=0.277..1.953 rows=3544 loops=1) |
Hash Cond: ((tr.stock_symbol)::text = (s_1.symbol)::text) |
-> Bitmap Heap Scan on trade_request tr (cost=42.89..144.19 rows=3544 width=13) (actual time=0.180..0.788 rows=3544 loops=1) |
Recheck Cond: ((status)::text = 'PENDING'::text) |
Heap Blocks: exact=57 |
-> Bitmap Index Scan on idx_tr_status_symbol (cost=0.00..42.00 rows=3544 width=0) (actual time=0.156..0.156 rows=3544 loops=1) |
-> Hash (cost=14.64..14.64 rows=100 width=13) (actual time=0.074..0.075 rows=100 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 13kB |
-> Index Scan using stock_pkey on stock s_1 (cost=0.14..14.64 rows=100 width=13) (actual time=0.016..0.051 rows=100 loops=1) |
Planning Time: 2.119 ms |
Execution Time: 419.999 ms |
idx_tr_status_symbol => Baseline: Seq Scan on trade_request + Filter: status = 'PENDING' + Rows Removed by Filter: 1456 + Nested Loop Join Filter. Со индексот: Bitmap Index Scan on idx_tr_status_symbol => Bitmap Heap Scan + Hash Join наместо Nested Loop. Ова е најголемото забрзување — 4.8x.
Заклучок:
Имаме забрзување од ~80%.
Безбедност и заштита
JWT Token Authorization (Spring Security)
JWT е stateless начин на автентикација - серверот НЕ чува информации за активни сесии во база, туку сите потребни податоци се енкодирани во самиот токен кој корисникот го чува локално. JWT содржи енкодирани информации: user_id, email, role, expiry.
Имплементацијата во SecurityConfig.java дефинира кои endpoints се јавни и кои бараат автентикација, со посебни правила за ADMIN:
// SecurityConfig.java
@Bean
public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
http
.cors(cors -> cors.configurationSource(corsConfigurationSource()))
.csrf(csrf -> csrf.disable())
.authorizeHttpRequests(auth -> auth
// Public endpoints - no token required
.requestMatchers("/api/auth/**").permitAll()
.requestMatchers("/api/stocks/**").permitAll()
.requestMatchers("/ws/**", "/topic/**").permitAll()
.requestMatchers("/api/history/**").permitAll()
// Admin only - role-based access control
.requestMatchers("/api/trades/*/approve").hasAuthority("ADMIN")
.requestMatchers("/api/trades/*/decline").hasAuthority("ADMIN")
.requestMatchers("/api/trades/pending").hasAuthority("ADMIN")
// Authenticated users
.requestMatchers("/api/trades/**").authenticated()
.requestMatchers("/api/watchlist/**").authenticated()
.requestMatchers("/api/transactions/**").authenticated()
.anyRequest().authenticated()
)
.sessionManagement(s -> s
.sessionCreationPolicy(SessionCreationPolicy.STATELESS)
)
.authenticationProvider(authenticationProvider)
.addFilterBefore(jwtAuthenticationFilter, UsernamePasswordAuthenticationFilter.class);
return http.build();
}
Секој HTTP барање поминува низ JwtAuthenticationFilter пред да стигне до контролерот. Филтерот го верификува потписот на токенот, го проверува expiry и го вчитува корисникот. Корисник без валиден JWT токен добива HTTP 401 Unauthorized.
Хеширање на лозинки (BCrypt)
Лозинките на корисниците се чуваат во базата во хеширана форма преку BCrypt алгоритам, а никогаш како plain text.
Во базата колоната users.password содржи BCrypt hash со формат $2a$10$....
CORS Конфигурација
CORS е безбеден механизам кој ги ограничува HTTP барањата само од дозволени домени.
Апликацијата има двојна CORS конфигурација:
// SecurityConfig.java - CORS на Spring Security ниво
@Bean
public CorsConfigurationSource corsConfigurationSource() {
CorsConfiguration config = new CorsConfiguration();
config.setAllowedOrigins(List.of(
"http://localhost:5173",
"http://localhost:5174",
"http://localhost:5175",
"http://localhost:5176",
"http://localhost:3000"
));
config.setAllowedMethods(List.of("GET", "POST", "PUT", "DELETE", "OPTIONS", "PATCH"));
config.setAllowedHeaders(List.of("*"));
config.setAllowCredentials(true);
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
source.registerCorsConfiguration("/**", source);
return source;
}
// WebConfig.java - CORS на Spring MVC ниво
@Configuration
public class WebConfig implements WebMvcConfigurer {
@Override
public void addCorsMappings(CorsRegistry registry) {
registry.addMapping("/**")
.allowedOrigins(
"http://localhost:5173",
"http://localhost:5174",
"http://localhost:5175",
"http://localhost:3000"
)
.allowedMethods("*")
.allowedHeaders("*")
.allowCredentials(true);
}
}
Само барања кои потекнуваат од дозволените localhost портови ќе бидат прифатени. Секое барање од непознат домен добива HTTP 403 уште на ниво на CORS preflight проверката.
Заштита на податоци на ниво на база
На ниво на базата на податоци, безбедноста е обезбедена преку:
- Role-based CHECK constraints -
users.role CHECK (role IN ('USER','ADMIN'))спречува внесување на невалидни улоги директно во базата - Enum CHECK constraints -
transactions.origin CHECK (origin IN ('INTERNAL','EXTERNAL'))иuser_auth_providers.auth_providers CHECK (auth_providers IN ('INTERNAL','GOOGLE'))обезбедуваат интегритет на enum вредностите - Foreign key constraints - сите референцијални правила се дефинирани на ниво на база, не само на апликациско ниво
- UNIQUE constraints -
users.email UNIQUE,users.username UNIQUE,stock.symbol UNIQUEиportfolios.user_id UNIQUEспречуваат дупликати кои би можеле да доведат до безбедносни пропусти
...
Last modified
5 days ago
Last modified on 04/27/26 12:46:03
Note:
See TracWiki
for help on using the wiki.
