= Други Развојни Активности = == Анализа на перформанси == === Извештај за диверзификација на портфолио (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` спречуваат дупликати кои би можеле да доведат до безбедносни пропусти ... ----