Changes between Version 1 and Version 2 of otherdevelopment

Timestamp:

03/02/26 00:48:37 (2 weeks ago)

Author:

231020

Comment:

--

otherdevelopment

@@ -3 +3 @@
 == Анализа на перформанси ==
 
-
+=== Извештај за диверзификација на портфолио (report_portfolio_diversification) ===
+
+Анализата на перформанси се врши врз основа на моменталната состојба во базата.
+
+'''SQL:'''
+{{{
+-- Core query inside report_portfolio_diversification()
+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,
+           hv.hv_stock_id,
+           hv.hv_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.hv_portfolio_id,
+           SUM(sw.stock_market_value)    AS total_holdings_value,
+           SUM(sw.stock_unrealized_pnl)  AS total_unrealized_pnl,
+           COUNT(DISTINCT sw.hv_stock_id) AS num_stocks,
+           SUM(sw.lots_count)            AS total_lots
+    FROM stock_weights sw
+    GROUP BY sw.hv_portfolio_id
+)
+SELECT u.id, u.username, p.id, p.balance, ...
+FROM users u
+JOIN portfolios p ON u.id = p.user_id
+LEFT JOIN portfolio_totals pt  ON p.id = pt.hv_portfolio_id
+WHERE u.role = 'USER'
+ORDER BY hhi_score ASC, total_portfolio_value DESC;
+}}}
+
+
+'''EXPLAIN ANALYZE без индекси:'''
+{{{
+EXPLAIN ANALYZE
+SELECT ph.portfolio_id, ph.stock_id, ph.quantity, s.current_price, s.symbol
+FROM portfolio_holdings ph
+JOIN stock s ON ph.stock_id = s.id
+WHERE ph.quantity > 0;
+
+-- резултат:
+Hash Join  (cost=11.57..34.17 rows=313 width=544) (actual time=0.105..0.116 rows=4 loops=1)
+  Hash Cond: (ph.stock_id = s.id)
+  ->  Seq Scan on portfolio_holdings ph  (cost=0.00..21.75 rows=313 width=20) (actual time=0.034..0.042 rows=4 loops=1)
+        Filter: (quantity > 0)
+  ->  Hash  (cost=10.70..10.70 rows=70 width=532) (actual time=0.036..0.037 rows=4 loops=1)
+        Buckets: 1024  Batches: 1  Memory Usage: 9kB
+        ->  Seq Scan on stock s  (cost=0.00..10.70 rows=70 width=532) (actual time=0.019..0.020 rows=4 loops=1)
+Planning Time: 1.315 ms
+Execution Time: 0.180 ms
+}}}
+
+'''Индекси:'''
+
+**1. portfolio_holdings - Index (portfolio_id)**
+{{{
+CREATE INDEX idx_ph_portfolio_id
+ON portfolio_holdings(portfolio_id);
+}}}
+Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals`
+
+Подобрување: Index Scan наместо Seq Scan при групирање по портфолио
+
+---
+
+**2. portfolio_holdings - Index (stock_id)**
+{{{
+CREATE INDEX idx_ph_stock_id
+ON portfolio_holdings(stock_id);
+}}}
+Користење: JOIN `ph.stock_id = s.id` во `holding_values` CTE
+
+Подобрување: Директен lookup наместо Hash Join со Seq Scan
+
+---
+
+**3. portfolio_holdings - Partial Index (quantity > 0)**
+{{{
+CREATE INDEX idx_ph_quantity_positive
+ON portfolio_holdings(portfolio_id, stock_id)
+WHERE quantity > 0;
+}}}
+Користење: WHERE филтер `ph.quantity > 0` во `holding_values` CTE
+
+Подобрување: Индексот ги содржи само редовите со quantity > 0, елиминирајќи ги нулираните холдинзи уште при скенирањето
+
+---
+
+**4. users - Index (role)**
+{{{
+CREATE INDEX idx_users_role
+ON users(role);
+}}}
+Користење: WHERE филтер `u.role = 'USER'` во финалниот SELECT
+
+Подобрување: Директен Index Scan наместо Seq Scan на целата users табела
+
+---
+
+'''EXPLAIN ANALYZE со индекси:'''
+{{{
+EXPLAIN ANALYZE
+SELECT ph.portfolio_id, ph.stock_id, ph.quantity, s.current_price, s.symbol
+FROM portfolio_holdings ph
+JOIN stock s ON ph.stock_id = s.id
+WHERE ph.quantity > 0;
+
+--  резултат:
+Nested Loop  (cost=0.14..9.44 rows=1 width=544) (actual time=0.034..0.042 rows=4 loops=1)
+  ->  Seq Scan on portfolio_holdings ph  (cost=0.00..1.05 rows=1 width=20) (actual time=0.015..0.017 rows=4 loops=1)
+        Filter: (quantity > 0)
+  ->  Index Scan using stock_pkey on stock s  (cost=0.14..8.16 rows=1 width=532) (actual time=0.005..0.005 rows=1 loops=4)
+        Index Cond: (id = ph.stock_id)
+Planning Time: 0.724 ms
+Execution Time: 0.086 ms
+}}}
+
+'''Заклучок:'''
+Имаме забрзување.
+
+
+----
+=== Извештај за волатилност на акции (report_stock_volatility_and_activity) ===
+'''SQL:'''
+{{{
+-- Core query inside report_stock_volatility_and_activity()
+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
+    WHERE sh.timestamp >= p_from_date
+      AND sh.timestamp <= p_to_date
+),
+price_stats AS (
+    SELECT dr.dr_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
+),
+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
+    WHERE t.timestamp >= p_from_date
+      AND t.timestamp <= p_to_date
+    GROUP BY t.stock_id
+)
+SELECT s.id, s.symbol, s.name, s.current_price, ...
+FROM stock s
+LEFT JOIN price_stats ps ON s.id = ps.dr_stock_id
+LEFT JOIN txn_stats   ts ON s.id = ts.ts_stock_id
+ORDER BY historical_volatility_pct DESC, activity_score DESC;
+}}}
+
+
+
+'''EXPLAIN ANALYZE без индекси:'''
+{{{
+EXPLAIN ANALYZE
+SELECT sh.stock_id, sh.price, sh.timestamp
+FROM stock_history sh
+WHERE sh.timestamp >= '2024-01-01'
+  AND sh.timestamp <= '2024-12-31'
+ORDER BY sh.stock_id, sh.timestamp;
+
+-- резултат:
+Sort  (cost=31.85..31.87 rows=7 width=20) (actual time=0.051..0.052 rows=0 loops=1)
+  Sort Key: stock_id, "timestamp"
+  Sort Method: quicksort  Memory: 25kB
+  ->  Seq Scan on stock_history sh  (cost=0.00..31.75 rows=7 width=20) (actual time=0.018..0.018 rows=0 loops=1)
+        Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))
+        Rows Removed by Filter: 8
+Planning Time: 0.417 ms
+Execution Time: 0.077 ms
+}}}
+
+
+'''Индекси:'''
+
+**1. stock_history - Composite Index (stock_id, timestamp)**
+{{{
+CREATE INDEX idx_sh_stock_id_timestamp
+ON stock_history(stock_id, timestamp);
+}}}
+Користење: `PARTITION BY sh.stock_id ORDER BY sh.timestamp` во LAG прозорската функција и WHERE филтерот за дати
+
+Подобрување: Composite индексот ги покрива и партиционирањето и сортирањето -= елиминира Sort чекор
+
+---
+
+**2. transactions - Composite Index (stock_id, timestamp)**
+{{{
+CREATE INDEX idx_tx_stock_id_timestamp
+ON transactions(stock_id, timestamp);
+}}}
+Користење: GROUP BY `t.stock_id` и WHERE на `timestamp` во `txn_stats` 
+
+Подобрување: Директен Index Scan наместо Seq Scan + Sort при временски филтрирани агрегации
+
+---
+
+**3. watchlist - Index (stock_id)**
+{{{
+CREATE INDEX idx_watchlist_stock_id
+ON watchlist(stock_id);
+}}}
+Користење: GROUP BY `stock_id` во `watchlist_stats` 
+
+Подобрување: Index Scan при агрегација по акција наместо Seq Scan
+
+---
+
+**4. trade_request - Composite Index (stock_symbol, status)**
+{{{
+CREATE INDEX idx_tr_symbol_status
+ON trade_request(stock_symbol, status);
+}}}
+Користење: JOIN `tr.stock_symbol = s.symbol` и WHERE `tr.status = 'PENDING'` во `pending_stats` 
+
+Подобрување: Composite индексот ги покрива и JOIN условот и WHERE филтерот истовремено
+
+---
+
+'''EXPLAIN ANALYZE со индекси:'''
+{{{
+EXPLAIN ANALYZE
+SELECT sh.stock_id, sh.price, sh.timestamp
+FROM stock_history sh
+WHERE sh.timestamp >= '2024-01-01'
+  AND sh.timestamp <= '2024-12-31'
+ORDER BY sh.stock_id, sh.timestamp;
+
+-- резултат:
+Sort  (cost=1.13..1.14 rows=1 width=20) (actual time=0.024..0.025 rows=0 loops=1)
+  Sort Key: stock_id, "timestamp"
+  Sort Method: quicksort  Memory: 25kB
+  ->  Seq Scan on stock_history sh  (cost=0.00..1.12 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=1)
+        Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))
+        Rows Removed by Filter: 8
+Planning Time: 0.397 ms
+Execution Time: 0.054 ms
+}}}
+
+'''Заклучок:'''
+Имаме забрзување.
 ----