Changes between Version 8 and Version 9 of otherdevelopment


Ignore:
Timestamp:
03/25/26 00:01:08 (2 weeks ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v8 v9  
    9999JOIN stock s ON ph.stock_id = s.id
    100100WHERE quantity > 0 AND portfolio_id = 1;
    101 
    102 
    103 QUERY PLAN                                                                                                                      |
    104 --------------------------------------------------------------------------------------------------------------------------------+
    105 Nested 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)                                                                                    |
    115 Planning Time: 0.471 ms                                                                                                         |
    116 Execution 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 {{{
    127 CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id);
    128 ANALYZE portfolio_holdings;
    129 
    130 EXPLAIN ANALYZE
    131 SELECT
    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
    137 FROM portfolio_holdings ph
    138 JOIN stock s ON ph.stock_id = s.id
    139 WHERE quantity > 0 AND portfolio_id = 1;
    140 
    141101
    142102
    … …  
    161121
    162122
    163 ---
    164 
    165 **4. partial index**
     123Подобрување: Index Scan наместо Seq Scan при групирање по портфолио
     124
     125---
     126
     127
     128**3. partial index**
    166129{{{
    167130CREATE INDEX idx_ph_quantity_positive
    … …  
    195158        Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                                  |
    196159        ->  Seq Scan on stock s  (cost=0.00..3.00 rows=100 width=21) (actual time=0.021..0.037 rows=100 loops=1)                       |
    197 Planning Time: 0.608 ms                                                                                                                |
    198 Execution Time: 0.609 ms                                                                                                               |
    199 
    200 }}}
    201 
    202 
    203 ---
    204 
    205 **5. Add index on users.role**
     160Planning Time: 0.401 ms                                                                                                                |
     161Execution Time: 0.409 ms                                                                                                               |
     162
     163}}}
     164
     165
     166---
     167
     168**4. Add index on users.role**
    206169{{{
    207170CREATE INDEX idx_users_role ON users(role);
    … …  
    220183
    221184
    222 QUERY PLAN                                                                                                                             |
    223 ---------------------------------------------------------------------------------------------------------------------------------------+
    224 Hash 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)                       |
    234 Planning Time: 0.299 ms                                                                                                                |
    235 Execution Time: 0.416 ms                                                                                                               |
    236 
    237 }}}
    238 
     185
     186
     187}}}
     188
     189Овој индекс не влијае на перформансите на оваа анализа бидејќи табелата users не е дел од query-то.
    239190
    240191---
    … …  
    288239---
    289240
    290 **1. transactions - Composite Index (stock_id, timestamp)**
    291 {{{
    292 CREATE INDEX idx_tx_stock_id_timestamp
    293 ON transactions(stock_id, timestamp);
    294 
    295 ANALYZE transactions;
     241**1. stock_history - Composite Index (stock_id, timestamp)**
     242{{{
     243CREATE INDEX idx_sh_stock_id_timestamp
     244ON stock_history(stock_id, timestamp);
     245
     246ANALYZE stock_history;
    296247EXPLAIN ANALYZE
    297248SELECT ...
    … …  
    300251QUERY PLAN                                                                                                                                                   |
    301252-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    302 WindowAgg  (cost=0.66..9098.75 rows=36545 width=28) (actual time=0.078..41.712 rows=36600 loops=1)                                                           |
    303   ->  Index Scan using idx_sh_stock_id_timestamp on stock_history sh  (cost=0.42..8185.12 rows=36545 width=20) (actual time=0.066..17.591 rows=36600 loops=1)|
     253WindowAgg  (cost=0.55..9020.40 rows=36500 width=28) (actual time=0.061..39.845 rows=36600 loops=1)                                                           |
     254  ->  Index Scan using idx_sh_stock_id_timestamp on stock_history sh  (cost=0.42..8120.30 rows=36500 width=20) (actual time=0.048..16.502 rows=36600 loops=1)|
    304255        Index Cond: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))                                                            |
    305 Planning Time: 0.163 ms                                                                                                                                      |
    306 Execution Time: 43.629 ms                                                                                                                                    |
     256Planning Time: 0.142 ms                                                                                                                                      |
     257Execution Time: 40.512 ms                                                                                                                                    |
    307258
    308259}}}
    … …  
    327278}}}
    328279
    329 
     280не се користи
    330281
    331282
    332283'''Заклучок:'''
    333284
    334 Имаме забрзување од ~60%
     285Имаме забрзување.
    335286
    336287----