Changes between Version 6 and Version 7 of otherdevelopment


Ignore:
Timestamp:
03/17/26 01:18:41 (2 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v6 v7  
    99'''SQL:'''
    1010{{{
    11 CREATE OR REPLACE FUNCTION test_portfolio_diversification()
    12 RETURNS void AS $$
    13 DECLARE
    14   start_time timestamptz;
    15   end_time   timestamptz;
    16   duration   int;
    17 BEGIN
    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;
    35 END;
    36 $$ LANGUAGE plpgsql;
    37 
    38 
    3911DROP INDEX IF EXISTS idx_ph_portfolio_id;
    4012DROP INDEX IF EXISTS idx_ph_stock_id;
    … …  
    4214DROP INDEX IF EXISTS idx_users_role;
    4315
    44 -- run 1: no index
    45 SELECT test_portfolio_diversification();
    46 
    47 -- run 2: + idx_ph_stock_id
     16EXPLAIN ANALYZE
     17SELECT
     18  ph.portfolio_id,
     19  ph.stock_id,
     20  s.symbol,
     21  ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     22  ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     23FROM portfolio_holdings ph
     24JOIN stock s ON ph.stock_id = s.id
     25WHERE quantity > 0 AND portfolio_id = 1;
     26
     27
     28
     29QUERY PLAN                                                                                                                      |
     30--------------------------------------------------------------------------------------------------------------------------------+
     31Nested Loop  (cost=0.15..1418.59 rows=50 width=85) (actual time=0.085..8.009 rows=50 loops=1)                                   |
     32  ->  Seq Scan on portfolio_holdings ph  (cost=0.00..1400.00 rows=50 width=26) (actual time=0.037..7.462 rows=50 loops=1)       |
     33        Filter: ((quantity > 0) AND (portfolio_id = 1))                                                                         |
     34        Rows Removed by Filter: 59950                                                                                           |
     35  ->  Memoize  (cost=0.15..0.49 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=50)                                     |
     36        Cache Key: ph.stock_id                                                                                                  |
     37        Cache Mode: logical                                                                                                     |
     38        Hits: 0  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 7kB                                                      |
     39        ->  Index Scan using stock_pkey on stock s  (cost=0.14..0.48 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50)|
     40              Index Cond: (id = ph.stock_id)                                                                                    |
     41Planning Time: 0.799 ms                                                                                                         |
     42Execution Time: 8.064 ms                                                                                                        |
     43
     44}}}
     45
     46
     47'''Индекси:'''
     48
     49
     50
     51**1. portfolio_holdings - Index (stock_id)**
     52{{{
    4853CREATE INDEX idx_ph_stock_id ON portfolio_holdings(stock_id);
    4954ANALYZE portfolio_holdings;
    50 SELECT test_portfolio_diversification();
    51 
    52 -- run 3: + idx_ph_portfolio_id
     55
     56EXPLAIN ANALYZE
     57SELECT
     58  ph.portfolio_id,
     59  ph.stock_id,
     60  s.symbol,
     61  ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     62  ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     63FROM portfolio_holdings ph
     64JOIN stock s ON ph.stock_id = s.id
     65WHERE quantity > 0 AND portfolio_id = 1;
     66
     67QUERY PLAN                                                                                                                      |
     68--------------------------------------------------------------------------------------------------------------------------------+
     69Nested Loop  (cost=0.15..1418.59 rows=50 width=85) (actual time=0.052..5.758 rows=50 loops=1)                                   |
     70  ->  Seq Scan on portfolio_holdings ph  (cost=0.00..1400.00 rows=50 width=26) (actual time=0.018..5.389 rows=50 loops=1)       |
     71        Filter: ((quantity > 0) AND (portfolio_id = 1))                                                                         |
     72        Rows Removed by Filter: 59950                                                                                           |
     73  ->  Memoize  (cost=0.15..0.49 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50)                                     |
     74        Cache Key: ph.stock_id                                                                                                  |
     75        Cache Mode: logical                                                                                                     |
     76        Hits: 0  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 7kB                                                      |
     77        ->  Index Scan using stock_pkey on stock s  (cost=0.14..0.48 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=50)|
     78              Index Cond: (id = ph.stock_id)                                                                                    |
     79Planning Time: 0.471 ms                                                                                                         |
     80Execution Time: 5.802 ms                                                                                                        |
     81
     82}}}
     83
     84---
     85
     86**2. portfolio_holdings - Index (portfolio_id)**
     87{{{
    5388CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id);
    5489ANALYZE portfolio_holdings;
    55 SELECT test_portfolio_diversification();
    56 
    57 -- run 4: + idx_ph_quantity_positive (partial index)
     90
     91EXPLAIN ANALYZE
     92SELECT
     93  ph.portfolio_id,
     94  ph.stock_id,
     95  s.symbol,
     96  ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     97  ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     98FROM portfolio_holdings ph
     99JOIN stock s ON ph.stock_id = s.id
     100WHERE quantity > 0 AND portfolio_id = 1;
     101
     102
     103QUERY PLAN                                                                                                                      |
     104--------------------------------------------------------------------------------------------------------------------------------+
     105Nested Loop  (cost=0.15..1418.59 rows=50 width=85) (actual time=0.052..5.758 rows=50 loops=1)                                   |
     106  ->  Seq Scan on portfolio_holdings ph  (cost=0.00..1400.00 rows=50 width=26) (actual time=0.018..5.389 rows=50 loops=1)       |
     107        Filter: ((quantity > 0) AND (portfolio_id = 1))                                                                         |
     108        Rows Removed by Filter: 59950                                                                                           |
     109  ->  Memoize  (cost=0.15..0.49 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=50)                                     |
     110        Cache Key: ph.stock_id                                                                                                  |
     111        Cache Mode: logical                                                                                                     |
     112        Hits: 0  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 7kB                                                      |
     113        ->  Index Scan using stock_pkey on stock s  (cost=0.14..0.48 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=50)|
     114              Index Cond: (id = ph.stock_id)                                                                                    |
     115Planning Time: 0.471 ms                                                                                                         |
     116Execution Time: 5.802 ms                                                                                                        |
     117
     118}}}
     119Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals`
     120
     121Подобрување: Index Scan наместо Seq Scan при групирање по портфолио
     122
     123---
     124
     125**3. Add index on portfolio_id**
     126{{{
     127CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id);
     128ANALYZE portfolio_holdings;
     129
     130EXPLAIN ANALYZE
     131SELECT
     132  ph.portfolio_id,
     133  ph.stock_id,
     134  s.symbol,
     135  ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     136  ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     137FROM portfolio_holdings ph
     138JOIN stock s ON ph.stock_id = s.id
     139WHERE quantity > 0 AND portfolio_id = 1;
     140
     141
     142
     143QUERY PLAN                                                                                                                        |
     144----------------------------------------------------------------------------------------------------------------------------------+
     145Hash Join  (cost=9.00..181.17 rows=50 width=85) (actual time=0.245..0.515 rows=50 loops=1)                                        |
     146  Hash Cond: (ph.stock_id = s.id)                                                                                                 |
     147  ->  Bitmap Heap Scan on portfolio_holdings ph  (cost=4.75..175.92 rows=50 width=26) (actual time=0.156..0.265 rows=50 loops=1)  |
     148        Recheck Cond: (portfolio_id = 1)                                                                                          |
     149        Filter: (quantity > 0)                                                                                                    |
     150        Rows Removed by Filter: 10                                                                                                |
     151        Heap Blocks: exact=60                                                                                                     |
     152        ->  Bitmap Index Scan on idx_ph_portfolio_id  (cost=0.00..4.74 rows=60 width=0) (actual time=0.136..0.137 rows=60 loops=1)|
     153              Index Cond: (portfolio_id = 1)                                                                                      |
     154  ->  Hash  (cost=3.00..3.00 rows=100 width=21) (actual time=0.059..0.060 rows=100 loops=1)                                       |
     155        Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                             |
     156        ->  Seq Scan on stock s  (cost=0.00..3.00 rows=100 width=21) (actual time=0.019..0.035 rows=100 loops=1)                  |
     157Planning Time: 0.526 ms                                                                                                           |
     158Execution Time: 0.554 ms                                                                                                          |
     159
     160}}}
     161
     162
     163---
     164
     165**4. partial index**
     166{{{
    58167CREATE INDEX idx_ph_quantity_positive
    59168ON portfolio_holdings(portfolio_id, stock_id)
    60169WHERE quantity > 0;
     170
    61171ANALYZE portfolio_holdings;
    62 SELECT test_portfolio_diversification();
    63 
    64 -- run 5: + idx_users_role
     172
     173EXPLAIN ANALYZE
     174SELECT
     175  ph.portfolio_id,
     176  ph.stock_id,
     177  s.symbol,
     178  ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     179  ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     180FROM portfolio_holdings ph
     181JOIN stock s ON ph.stock_id = s.id
     182WHERE quantity > 0 AND portfolio_id = 1;
     183
     184
     185QUERY PLAN                                                                                                                             |
     186---------------------------------------------------------------------------------------------------------------------------------------+
     187Hash Join  (cost=8.93..158.07 rows=50 width=85) (actual time=0.315..0.573 rows=50 loops=1)                                             |
     188  Hash Cond: (ph.stock_id = s.id)                                                                                                      |
     189  ->  Bitmap Heap Scan on portfolio_holdings ph  (cost=4.68..152.81 rows=50 width=26) (actual time=0.225..0.320 rows=50 loops=1)       |
     190        Recheck Cond: ((portfolio_id = 1) AND (quantity > 0))                                                                          |
     191        Heap Blocks: exact=50                                                                                                          |
     192        ->  Bitmap Index Scan on idx_ph_quantity_positive  (cost=0.00..4.67 rows=50 width=0) (actual time=0.209..0.209 rows=50 loops=1)|
     193              Index Cond: (portfolio_id = 1)                                                                                           |
     194  ->  Hash  (cost=3.00..3.00 rows=100 width=21) (actual time=0.059..0.060 rows=100 loops=1)                                            |
     195        Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                                  |
     196        ->  Seq Scan on stock s  (cost=0.00..3.00 rows=100 width=21) (actual time=0.021..0.037 rows=100 loops=1)                       |
     197Planning Time: 0.608 ms                                                                                                                |
     198Execution Time: 0.609 ms                                                                                                               |
     199
     200}}}
     201
     202
     203---
     204
     205**5. Add index on users.role**
     206{{{
    65207CREATE INDEX idx_users_role ON users(role);
    66208ANALYZE users;
    67 SELECT test_portfolio_diversification();
    68 
    69 DROP FUNCTION test_portfolio_diversification();
    70 }}}
    71 
    72 
    73 
    74 
    75 
    76 '''Индекси:'''
    77 
    78 **1. portfolio_holdings - Index (portfolio_id)**
    79 {{{
    80 CREATE INDEX idx_ph_portfolio_id
    81 ON 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 {{{
    91 CREATE INDEX idx_ph_stock_id
    92 ON 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 {{{
    102 CREATE INDEX idx_ph_quantity_positive
    103 ON portfolio_holdings(portfolio_id, stock_id)
    104 WHERE quantity > 0;
    105 }}}
    106 Користење: WHERE филтер `ph.quantity > 0` во `holding_values` CTE
    107 
    108 Подобрување: Индексот ги содржи само редовите со quantity > 0, елиминирајќи ги нулираните холдинзи уште при скенирањето
    109 
    110 ---
    111 
    112 **4. users - Index (role)**
    113 {{{
    114 CREATE INDEX idx_users_role
    115 ON users(role);
    116 }}}
    117 Користење: WHERE филтер `u.role = 'USER'` во финалниот SELECT
    118 
    119 Подобрување: Директен Index Scan наместо Seq Scan на целата users табела
    120 
    121 ---
    122 
    123 
     209
     210EXPLAIN ANALYZE
     211SELECT
     212  ph.portfolio_id,
     213  ph.stock_id,
     214  s.symbol,
     215  ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     216  ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     217FROM portfolio_holdings ph
     218JOIN stock s ON ph.stock_id = s.id
     219WHERE quantity > 0 AND portfolio_id = 1;
     220
     221
     222QUERY PLAN                                                                                                                             |
     223---------------------------------------------------------------------------------------------------------------------------------------+
     224Hash Join  (cost=8.93..158.07 rows=50 width=85) (actual time=0.123..0.377 rows=50 loops=1)                                             |
     225  Hash Cond: (ph.stock_id = s.id)                                                                                                      |
     226  ->  Bitmap Heap Scan on portfolio_holdings ph  (cost=4.68..152.81 rows=50 width=26) (actual time=0.032..0.125 rows=50 loops=1)       |
     227        Recheck Cond: ((portfolio_id = 1) AND (quantity > 0))                                                                          |
     228        Heap Blocks: exact=50                                                                                                          |
     229        ->  Bitmap Index Scan on idx_ph_quantity_positive  (cost=0.00..4.67 rows=50 width=0) (actual time=0.016..0.017 rows=50 loops=1)|
     230              Index Cond: (portfolio_id = 1)                                                                                           |
     231  ->  Hash  (cost=3.00..3.00 rows=100 width=21) (actual time=0.060..0.060 rows=100 loops=1)                                            |
     232        Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                                  |
     233        ->  Seq Scan on stock s  (cost=0.00..3.00 rows=100 width=21) (actual time=0.018..0.037 rows=100 loops=1)                       |
     234Planning Time: 0.299 ms                                                                                                                |
     235Execution Time: 0.416 ms                                                                                                               |
     236
     237}}}
     238
     239
     240---
    124241
    125242'''Сумарно:'''
    126 Без индекси: 201ms
    127 
    128 Со индекси: 140ms
    129 
    130 Имаме забрзување.
     243
     244
     24520x побрзо
    131246
    132247----