Changes between Version 4 and Version 5 of otherdevelopment


Ignore:
Timestamp:
03/09/26 01:02:44 (10 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v4 v5  
    99'''SQL:'''
    1010{{{
    11 CREATE OR REPLACE FUNCTION report_portfolio_diversification()
    12     RETURNS TABLE (
    13         user_id                   INTEGER,
    14         username                  TEXT,
    15         portfolio_id              INTEGER,
    16         portfolio_cash_balance    NUMERIC,
    17         total_holdings_value      NUMERIC,
    18         total_portfolio_value     NUMERIC,
    19         num_distinct_stocks       BIGINT,
    20         total_lots                BIGINT,
    21         unrealized_pnl            NUMERIC,
    22         unrealized_pnl_pct        NUMERIC,
    23         hhi_score                 NUMERIC,
    24         risk_classification       TEXT,
    25         dominant_stock_symbol     TEXT,
    26         dominant_stock_weight_pct NUMERIC,
    27         max_single_weight_pct     NUMERIC,
    28         min_single_weight_pct     NUMERIC,
    29         diversification_rank      BIGINT
    30     ) AS $$
     11CREATE OR REPLACE FUNCTION test_portfolio_diversification()
     12RETURNS void AS $$
     13DECLARE
     14  start_time timestamptz;
     15  end_time   timestamptz;
     16  duration   int;
    3117BEGIN
    32     RETURN QUERY
    33         WITH
    34         holding_values AS (
    35             SELECT
    36                 ph.portfolio_id                                                             AS hv_portfolio_id,
    37                 ph.stock_id                                                                 AS hv_stock_id,
    38                 s.symbol                                                                    AS hv_symbol,
    39                 ph.quantity::NUMERIC * s.current_price::NUMERIC                            AS market_value,
    40                 ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC)  AS unrealized_pnl
    41             FROM portfolio_holdings ph
    42             JOIN stock s ON ph.stock_id = s.id
    43             WHERE ph.quantity > 0
    44         ),
    45         stock_weights AS (
    46             SELECT
    47                 hv.hv_portfolio_id,
    48                 hv.hv_stock_id,
    49                 hv.hv_symbol,
    50                 SUM(hv.market_value)   AS stock_market_value,
    51                 SUM(hv.unrealized_pnl) AS stock_unrealized_pnl,
    52                 COUNT(*)               AS lots_count
    53             FROM holding_values hv
    54             GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol
    55         ),
    56         portfolio_totals AS (
    57             SELECT
    58                 sw.hv_portfolio_id,
    59                 SUM(sw.stock_market_value)     AS total_holdings_value,
    60                 SUM(sw.stock_unrealized_pnl)   AS total_unrealized_pnl,
    61                 COUNT(DISTINCT sw.hv_stock_id) AS num_stocks,
    62                 SUM(sw.lots_count)             AS total_lots
    63             FROM stock_weights sw
    64             GROUP BY sw.hv_portfolio_id
    65         ),
    66         hhi_calc AS (
    67             SELECT
    68                 sw.hv_portfolio_id AS hhi_portfolio_id,
    69                 SUM(POWER(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), 2::NUMERIC)) AS hhi,
    70                 MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC)     AS max_weight_pct,
    71                 MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC)     AS min_weight_pct
    72             FROM stock_weights sw
    73             JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
    74             GROUP BY sw.hv_portfolio_id
    75         ),
    76         dominant_stock AS (
    77             SELECT DISTINCT ON (sw.hv_portfolio_id)
    78                 sw.hv_portfolio_id AS ds_portfolio_id,
    79                 sw.hv_symbol       AS dominant_symbol,
    80                 ROUND(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC, 2) AS dominant_weight_pct
    81             FROM stock_weights sw
    82             JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
    83             ORDER BY sw.hv_portfolio_id,
    84                      sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
    85         )
    86         SELECT
    87             u.id::INTEGER,
    88             u.username::TEXT,
    89             p.id::INTEGER,
    90             p.balance::NUMERIC,
    91             COALESCE(pt.total_holdings_value, 0::NUMERIC),
    92             (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)),
    93             COALESCE(pt.num_stocks, 0)::BIGINT,
    94             COALESCE(pt.total_lots, 0)::BIGINT,
    95             COALESCE(pt.total_unrealized_pnl, 0::NUMERIC),
    96             ROUND(COALESCE(pt.total_unrealized_pnl, 0::NUMERIC)
    97                 / NULLIF(pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC), 0::NUMERIC) * 100::NUMERIC, 2),
    98             ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4),
    99             CASE
    100                 WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK'
    101                 WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK'
    102                 ELSE                      'WELL DIVERSIFIED'
    103             END::TEXT,
    104             COALESCE(ds.dominant_symbol, 'N/A')::TEXT,
    105             COALESCE(ds.dominant_weight_pct, 0::NUMERIC),
    106             COALESCE(hhi.max_weight_pct, 0::NUMERIC),
    107             COALESCE(hhi.min_weight_pct, 0::NUMERIC),
    108             RANK() OVER (ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC)::BIGINT
    109         FROM users u
    110         JOIN portfolios            p   ON u.id = p.user_id
    111         LEFT JOIN portfolio_totals pt  ON p.id = pt.hv_portfolio_id
    112         LEFT JOIN hhi_calc         hhi ON p.id = hhi.hhi_portfolio_id
    113         LEFT JOIN dominant_stock   ds  ON p.id = ds.ds_portfolio_id
    114         WHERE u.role = 'USER'
    115         ORDER BY hhi_score ASC, total_portfolio_value DESC;
     18  start_time := clock_timestamp();
     19
     20  PERFORM
     21    ph.portfolio_id,
     22    ph.stock_id,
     23    s.symbol,
     24    ph.quantity::NUMERIC * s.current_price::NUMERIC              AS market_value,
     25    ph.quantity::NUMERIC * (s.current_price::NUMERIC
     26                            - ph.avg_price::NUMERIC)             AS unrealized_pnl
     27  FROM portfolio_holdings ph
     28  JOIN stock s ON ph.stock_id = s.id
     29  WHERE ph.quantity > 0;
     30
     31  end_time := clock_timestamp();
     32  duration := round(1000 * (extract(epoch FROM end_time)
     33                           - extract(epoch FROM start_time)));
     34  RAISE NOTICE 'Query executed in: % ms', duration;
    11635END;
    11736$$ LANGUAGE plpgsql;
    118 }}}
    119 
    120 
    121 '''EXPLAIN ANALYZE без индекси:'''
    122 {{{
    123 EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();
    124 
    125 -- резултат:
    126 QUERY PLAN                                                                                                                           |
    127 -------------------------------------------------------------------------------------------------------------------------------------+
    128 Function Scan on report_portfolio_diversification  (cost=0.25..10.25 rows=1000 width=416) (actual time=10.842..10.843 rows=2 loops=1)|
    129 Planning Time: 0.084 ms                                                                                                              |
    130 Execution Time: 12.265 ms                                                                                                            |
    131 
    132 }}}
    133 
    134 
    135 
    136 '''Индекси:'''
    137 
    138 **1. portfolio_holdings - Index (portfolio_id)**
    139 {{{
    140 CREATE INDEX idx_ph_portfolio_id
    141 ON portfolio_holdings(portfolio_id);
    142 }}}
    143 Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals`
    144 
    145 Подобрување: Index Scan наместо Seq Scan при групирање по портфолио
    146 
    147 ---
    148 
    149 **2. portfolio_holdings - Index (stock_id)**
    150 {{{
    151 CREATE INDEX idx_ph_stock_id
    152 ON portfolio_holdings(stock_id);
    153 }}}
    154 Користење: JOIN `ph.stock_id = s.id` во `holding_values` CTE
    155 
    156 Подобрување: Директен lookup наместо Hash Join со Seq Scan
    157 
    158 ---
    159 
    160 **3. portfolio_holdings - Partial Index (quantity > 0)**
    161 {{{
     37
     38
     39DROP INDEX IF EXISTS idx_ph_portfolio_id;
     40DROP INDEX IF EXISTS idx_ph_stock_id;
     41DROP INDEX IF EXISTS idx_ph_quantity_positive;
     42DROP INDEX IF EXISTS idx_users_role;
     43
     44-- run 1: no index
     45SELECT test_portfolio_diversification();
     46
     47-- run 2: + idx_ph_stock_id
     48CREATE INDEX idx_ph_stock_id ON portfolio_holdings(stock_id);
     49ANALYZE portfolio_holdings;
     50SELECT test_portfolio_diversification();
     51
     52-- run 3: + idx_ph_portfolio_id
     53CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id);
     54ANALYZE portfolio_holdings;
     55SELECT test_portfolio_diversification();
     56
     57-- run 4: + idx_ph_quantity_positive (partial index)
    16258CREATE INDEX idx_ph_quantity_positive
    16359ON portfolio_holdings(portfolio_id, stock_id)
    16460WHERE quantity > 0;
     61ANALYZE portfolio_holdings;
     62SELECT test_portfolio_diversification();
     63
     64-- run 5: + idx_users_role
     65CREATE INDEX idx_users_role ON users(role);
     66ANALYZE users;
     67SELECT test_portfolio_diversification();
     68
     69DROP FUNCTION test_portfolio_diversification();
     70}}}
     71
     72
     73
     74
     75
     76'''Индекси:'''
     77
     78**1. portfolio_holdings - Index (portfolio_id)**
     79{{{
     80CREATE INDEX idx_ph_portfolio_id
     81ON portfolio_holdings(portfolio_id);
     82}}}
     83Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals`
     84
     85Подобрување: Index Scan наместо Seq Scan при групирање по портфолио
     86
     87---
     88
     89**2. portfolio_holdings - Index (stock_id)**
     90{{{
     91CREATE INDEX idx_ph_stock_id
     92ON portfolio_holdings(stock_id);
     93}}}
     94Користење: JOIN `ph.stock_id = s.id` во `holding_values` CTE
     95
     96Подобрување: Директен lookup наместо Hash Join со Seq Scan
     97
     98---
     99
     100**3. portfolio_holdings - Partial Index (quantity > 0)**
     101{{{
     102CREATE INDEX idx_ph_quantity_positive
     103ON portfolio_holdings(portfolio_id, stock_id)
     104WHERE quantity > 0;
    165105}}}
    166106Користење: WHERE филтер `ph.quantity > 0` во `holding_values` CTE
    … …  
    181121---
    182122
    183 '''EXPLAIN ANALYZE со индекси:'''
    184 {{{
    185 EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();
    186 
    187 --  резултат:
    188                                                                                                                        
    189 -----------------------------------------------------------------------------------------------------------------------------------+
    190 Function Scan on report_portfolio_diversification  (cost=0.25..10.25 rows=1000 width=416) (actual time=4.145..4.146 rows=2 loops=1)|
    191 Planning Time: 0.070 ms                                                                                                            |
    192 Execution Time: 4.212 ms                                                                                                           |
    193 }}}
    194 
    195 '''Заклучок:'''
     123
     124
     125'''Сумарно:'''
     126Без индекси: 201ms
     127
     128Со индекси: 140ms
     129
    196130Имаме забрзување.
    197 
    198131
    199132----