| Version 10 (modified by , 5 days ago) ( diff ) |
|---|
Други Развојни Активности
Анализа на перформанси
Извештај за диверзификација на портфолио (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:
DROP INDEX IF EXISTS idx_sh_stock_id_timestamp;
DROP INDEX IF EXISTS idx_tx_stock_id_timestamp;
DROP INDEX IF EXISTS idx_watchlist_stock_id;
DROP INDEX IF EXISTS idx_tr_symbol_status;
EXPLAIN ANALYZE
SELECT
sh.stock_id,
sh.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 daily_return
FROM stock_history sh
WHERE sh.timestamp >= '2024-01-01'
AND sh.timestamp <= '2024-12-31';
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------+
WindowAgg (cost=8773.08..9784.81 rows=36791 width=28) (actual time=41.592..66.876 rows=36600 loops=1) |
-> Sort (cost=8773.06..8865.03 rows=36791 width=20) (actual time=41.543..46.298 rows=36600 loops=1) |
Sort Key: stock_id, "timestamp" |
Sort Method: external merge Disk: 1256kB |
-> Seq Scan on stock_history sh (cost=0.00..4471.00 rows=36791 width=20) (actual time=0.060..22.078 rows=36600 loops=1)|
Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date)) |
Rows Removed by Filter: 163400 |
Planning Time: 0.526 ms |
Execution Time: 69.235 ms |
Индекси:
---
1. stock_history - Composite Index (stock_id, timestamp)
CREATE INDEX idx_sh_stock_id_timestamp
ON stock_history(stock_id, timestamp);
ANALYZE stock_history;
EXPLAIN ANALYZE
SELECT ...
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------------+
WindowAgg (cost=0.55..9020.40 rows=36500 width=28) (actual time=0.061..39.845 rows=36600 loops=1) |
-> Index Scan using idx_sh_stock_id_timestamp on stock_history sh (cost=0.42..8120.30 rows=36500 width=20) (actual time=0.048..16.502 rows=36600 loops=1)|
Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date)) |
Planning Time: 0.142 ms |
Execution Time: 40.512 ms |
---
2. watchlist - Index (stock_id)
CREATE INDEX idx_watchlist_stock_id ON watchlist(stock_id);
ANALYZE watchlist;
EXPLAIN ANALYZE
SELECT ...
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------------------------------------+
WindowAgg (cost=0.66..9098.75 rows=36545 width=28) (actual time=0.083..42.003 rows=36600 loops=1) |
-> Index Scan using idx_sh_stock_id_timestamp on stock_history sh (cost=0.42..8185.12 rows=36545 width=20) (actual time=0.070..17.564 rows=36600 loops=1)|
Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date)) |
Planning Time: 0.162 ms |
Execution Time: 43.946 ms |
не се користи
Заклучок:
Имаме забрзување.
Безбедност и заштита
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спречуваат дупликати кои би можеле да доведат до безбедносни пропусти
...
Note:
See TracWiki
for help on using the wiki.
