= Други Развојни Активности = == Анализа на перформанси == === Извештај за диверзификација на портфолио (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% побрзо. Исто така во baseline се гледаат две важни работи кои ги нема со индекси: JIT компилација - 1.398 ms overhead (Inlining + Optimization + Emission). Со индексите планерот избегнува JIT. external merge Disk — без индекси sortовите пишуваат на диск (1.8MB, 2.7MB, 1.6MB). Со индексите сето тоа е in-memory. ---- === Извештај за волатилност на акции (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` спречуваат дупликати кои би можеле да доведат до безбедносни пропусти ... ----