Changes between Version 9 and Version 10 of otherdevelopment


Ignore:
Timestamp:
04/26/26 21:50:29 (5 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v9 v10  
    88
    99'''SQL:'''
    10 {{{
    11 DROP INDEX IF EXISTS idx_ph_portfolio_id;
    12 DROP INDEX IF EXISTS idx_ph_stock_id;
    13 DROP INDEX IF EXISTS idx_ph_quantity_positive;
    14 DROP INDEX IF EXISTS idx_users_role;
     10- Без индекси
     11{{{
    1512
    1613EXPLAIN ANALYZE
     14WITH
     15holding_values AS (
     16    SELECT
     17        ph.portfolio_id AS hv_portfolio_id,
     18        ph.stock_id AS hv_stock_id,
     19        s.symbol AS hv_symbol,
     20        ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
     21        ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
     22    FROM portfolio_holdings ph
     23    JOIN stock s ON ph.stock_id = s.id
     24    WHERE ph.quantity > 0
     25),
     26stock_weights AS (
     27    SELECT
     28        hv.hv_portfolio_id AS sw_portfolio_id,
     29        hv.hv_stock_id AS sw_stock_id,
     30        hv.hv_symbol AS sw_symbol,
     31        SUM(hv.market_value) AS stock_market_value,
     32        SUM(hv.unrealized_pnl) AS stock_unrealized_pnl,
     33        COUNT(*) AS lots_count
     34    FROM holding_values hv
     35    GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol
     36),
     37portfolio_totals AS (
     38    SELECT
     39        sw.sw_portfolio_id AS pt_portfolio_id,
     40        SUM(sw.stock_market_value) AS total_holdings_value,
     41        SUM(sw.stock_unrealized_pnl) AS total_unrealized_pnl,
     42        COUNT(DISTINCT sw.sw_stock_id) AS num_stocks,
     43        SUM(sw.lots_count) AS total_lots
     44    FROM stock_weights sw
     45    GROUP BY sw.sw_portfolio_id
     46),
     47hhi_calc AS (
     48    SELECT
     49        sw.sw_portfolio_id AS hhi_portfolio_id,
     50        SUM(POWER(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), 2::NUMERIC)) AS hhi,
     51        MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS max_weight_pct,
     52        MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS min_weight_pct
     53    FROM stock_weights sw
     54    JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id
     55    GROUP BY sw.sw_portfolio_id
     56),
     57dominant_stock AS (
     58    SELECT DISTINCT ON (sw.sw_portfolio_id)
     59        sw.sw_portfolio_id AS ds_portfolio_id,
     60        sw.sw_symbol AS dominant_symbol,
     61        ROUND(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC, 2) AS dominant_weight_pct
     62    FROM stock_weights sw
     63    JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id
     64    ORDER BY sw.sw_portfolio_id, sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
     65)
    1766SELECT
    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
    23 FROM portfolio_holdings ph
    24 JOIN stock s ON ph.stock_id = s.id
    25 WHERE quantity > 0 AND portfolio_id = 1;
    26 
    27 
    28 
    29 QUERY PLAN                                                                                                                      |
    30 --------------------------------------------------------------------------------------------------------------------------------+
    31 Nested 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)                                                                                    |
    41 Planning Time: 0.799 ms                                                                                                         |
    42 Execution Time: 8.064 ms                                                                                                        |
     67    u.id::INTEGER AS user_id,
     68    u.username::TEXT,
     69    p.id::INTEGER AS portfolio_id,
     70    p.balance::NUMERIC AS portfolio_cash_balance,
     71    COALESCE(pt.total_holdings_value, 0::NUMERIC) AS total_holdings_value,
     72    (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)) AS total_portfolio_value,
     73    COALESCE(pt.num_stocks, 0)::BIGINT AS num_distinct_stocks,
     74    COALESCE(pt.total_lots, 0)::BIGINT AS total_lots,
     75    COALESCE(pt.total_unrealized_pnl, 0::NUMERIC) AS unrealized_pnl,
     76    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,
     77    ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4) AS hhi_score,
     78    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,
     79    COALESCE(ds.dominant_symbol, 'N/A')::TEXT AS dominant_stock_symbol,
     80    COALESCE(ds.dominant_weight_pct, 0::NUMERIC) AS dominant_stock_weight_pct,
     81    COALESCE(hhi.max_weight_pct, 0::NUMERIC) AS max_single_weight_pct,
     82    COALESCE(hhi.min_weight_pct, 0::NUMERIC) AS min_single_weight_pct,
     83    RANK() OVER (ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC)::BIGINT AS diversification_rank
     84FROM users u
     85JOIN portfolios p ON u.id = p.user_id
     86LEFT JOIN portfolio_totals pt ON p.id = pt.pt_portfolio_id
     87LEFT JOIN hhi_calc hhi ON p.id = hhi.hhi_portfolio_id
     88LEFT JOIN dominant_stock ds ON p.id = ds.ds_portfolio_id
     89WHERE u.role = 'USER'
     90ORDER BY hhi_score ASC, total_portfolio_value DESC;
     91
     92
     93QUERY PLAN                                                                                                                                                                             |
     94---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     95Sort  (cost=10000076265.17..10000076267.67 rows=1000 width=448) (actual time=2950.286..2950.368 rows=1000 loops=1)                                                                     |
     96  Sort Key: (round(COALESCE(hhi.hhi, '0'::numeric), 4)), (((p.balance)::numeric + COALESCE(pt.total_holdings_value, '0'::numeric))) DESC                                               |
     97  Sort Method: quicksort  Memory: 212kB                                                                                                                                                |
     98  CTE stock_weights                                                                                                                                                                    |
     99    ->  GroupAggregate  (cost=10000007600.41..10000013734.23 rows=50009 width=93) (actual time=913.089..1157.834 rows=50000 loops=1)                                                   |
     100          Group Key: ph.stock_id, ph.portfolio_id, s.symbol                                                                                                                            |
     101          ->  Incremental Sort  (cost=10000007600.41..10000011358.81 rows=50009 width=39) (actual time=913.012..941.655 rows=50000 loops=1)                                            |
     102                Sort Key: ph.stock_id, ph.portfolio_id, s.symbol                                                                                                                       |
     103                Presorted Key: ph.stock_id                                                                                                                                             |
     104                Full-sort Groups: 50  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB                                                                                  |
     105                Pre-sorted Groups: 50  Sort Method: quicksort  Average Memory: 87kB  Peak Memory: 87kB                                                                                 |
     106                ->  Merge Join  (cost=10000007547.32..10000008306.30 rows=50009 width=39) (actual time=912.479..927.879 rows=50000 loops=1)                                            |
     107                      Merge Cond: (ph.stock_id = s.id)                                                                                                                                 |
     108                      ->  Sort  (cost=10000007547.18..10000007672.20 rows=50009 width=26) (actual time=912.375..919.789 rows=50000 loops=1)                                            |
     109                            Sort Key: ph.stock_id                                                                                                                                      |
     110                            Sort Method: external merge  Disk: 1824kB                                                                                                                  |
     111                            ->  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)            |
     112                                  Filter: (quantity > 0)                                                                                                                               |
     113                                  Rows Removed by Filter: 10000                                                                                                                        |
     114                      ->  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)                                      |
     115  CTE portfolio_totals                                                                                                                                                                 |
     116    ->  GroupAggregate  (cost=9691.36..10444.99 rows=200 width=112) (actual time=1232.448..1261.387 rows=1000 loops=1)                                                                 |
     117          Group Key: sw_2.sw_portfolio_id                                                                                                                                              |
     118          ->  Sort  (cost=9691.36..9816.38 rows=50009 width=88) (actual time=1232.344..1246.691 rows=50000 loops=1)                                                                    |
     119                Sort Key: sw_2.sw_portfolio_id, sw_2.sw_stock_id                                                                                                                       |
     120                Sort Method: external merge  Disk: 2704kB                                                                                                                              |
     121                ->  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)                                        |
     122  ->  WindowAgg  (cost=51785.63..51833.12 rows=1000 width=448) (actual time=2946.255..2948.249 rows=1000 loops=1)                                                                      |
     123        ->  Sort  (cost=51785.62..51788.12 rows=1000 width=817) (actual time=2946.215..2946.303 rows=1000 loops=1)                                                                     |
     124              Sort Key: (COALESCE(hhi.hhi, '1'::numeric))                                                                                                                              |
     125              Sort Method: quicksort  Memory: 187kB                                                                                                                                    |
     126              ->  Hash Join  (cost=46946.33..51371.79 rows=1000 width=817) (actual time=2815.414..2945.323 rows=1000 loops=1)                                                          |
     127                    Hash Cond: (p.user_id = u.id)                                                                                                                                      |
     128                    ->  Hash Left Join  (cost=46879.05..51301.88 rows=1000 width=772) (actual time=1628.954..1758.394 rows=1000 loops=1)                                               |
     129                          Hash Cond: (p.id = hhi.hhi_portfolio_id)                                                                                                                     |
     130                          ->  Merge Left Join  (cost=42364.74..46784.93 rows=1000 width=676) (actual time=1263.716..1392.616 rows=1000 loops=1)                                        |
     131                                Merge Cond: (p.id = sw.sw_portfolio_id)                                                                                                                |
     132                                ->  Merge Left Join  (cost=0.28..55.77 rows=1000 width=128) (actual time=1232.543..1234.126 rows=1000 loops=1)                                         |
     133                                      Merge Cond: (p.id = pt.pt_portfolio_id)                                                                                                          |
     134                                      ->  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)         |
     135                                      ->  Materialize  (cost=0.00..4.50 rows=200 width=112) (actual time=1232.456..1232.755 rows=1000 loops=1)                                         |
     136                                            ->  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)               |
     137                                ->  Unique  (cost=42364.47..46722.15 rows=200 width=588) (actual time=31.143..158.023 rows=1000 loops=1)                                               |
     138                                      ->  Incremental Sort  (cost=42364.47..46597.13 rows=50009 width=588) (actual time=31.141..154.964 rows=49951 loops=1)                            |
     139                                            Sort Key: sw.sw_portfolio_id, ((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric))) DESC                             |
     140                                            Presorted Key: sw.sw_portfolio_id                                                                                                          |
     141                                            Full-sort Groups: 1000  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                    |
     142                                            ->  Merge Join  (cost=42346.36..43976.15 rows=50009 width=588) (actual time=30.962..100.854 rows=50000 loops=1)                            |
     143                                                  Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id)                                                                              |
     144                                                  ->  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)              |
     145                                                  ->  Materialize  (cost=42346.36..42596.40 rows=50009 width=556) (actual time=30.913..45.619 rows=50000 loops=1)                      |
     146                                                        ->  Sort  (cost=42346.36..42471.38 rows=50009 width=556) (actual time=30.910..39.318 rows=50000 loops=1)                       |
     147                                                              Sort Key: sw.sw_portfolio_id                                                                                             |
     148                                                              Sort Method: external merge  Disk: 1616kB                                                                                |
     149                                                              ->  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)|
     150                          ->  Hash  (cost=4511.81..4511.81 rows=200 width=104) (actual time=365.175..365.180 rows=1000 loops=1)                                                        |
     151                                Buckets: 1024  Batches: 1  Memory Usage: 94kB                                                                                                          |
     152                                ->  Subquery Scan on hhi  (cost=4507.31..4511.81 rows=200 width=104) (actual time=237.945..364.912 rows=1000 loops=1)                                  |
     153                                      ->  HashAggregate  (cost=4507.31..4509.81 rows=200 width=104) (actual time=237.936..364.756 rows=1000 loops=1)                                   |
     154                                            Group Key: sw_1.sw_portfolio_id                                                                                                            |
     155                                            Batches: 5  Memory Usage: 625kB  Disk Usage: 232kB                                                                                         |
     156                                            ->  Hash Join  (cost=6.50..2882.02 rows=50009 width=72) (actual time=29.760..53.495 rows=50000 loops=1)                                    |
     157                                                  Hash Cond: (sw_1.sw_portfolio_id = pt_2.pt_portfolio_id)                                                                             |
     158                                                  ->  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)           |
     159                                                  ->  Hash  (cost=4.00..4.00 rows=200 width=40) (actual time=29.701..29.703 rows=1000 loops=1)                                         |
     160                                                        Buckets: 1024  Batches: 1  Memory Usage: 63kB                                                                                  |
     161                                                        ->  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)       |
     162                    ->  Hash  (cost=54.77..54.77 rows=1000 width=21) (actual time=1186.429..1186.430 rows=1000 loops=1)                                                                |
     163                          Buckets: 1024  Batches: 1  Memory Usage: 61kB                                                                                                                |
     164                          ->  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)                         |
     165                                Filter: ((role)::text = 'USER'::text)                                                                                                                  |
     166Planning Time: 3.006 ms                                                                                                                                                                |
     167JIT:                                                                                                                                                                                   |
     168  Functions: 83                                                                                                                                                                        |
     169  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                          |
     170  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                                                   |
     171Execution Time: 2960.889 ms                                                                                                                                                            |
    43172
    44173}}}
    … …  
    49178
    50179
    51 **1. portfolio_holdings - Index (stock_id)**
    52 {{{
    53 CREATE INDEX idx_ph_stock_id ON portfolio_holdings(stock_id);
    54 ANALYZE portfolio_holdings;
     180**1. idx_ph_portfolio_stock_qty (Composite partial index)**
     181
     182Composite partial индекс на portfolio_holdings(portfolio_id, stock_id, quantity) со услов WHERE quantity > 0.
     183
     184{{{
     185CREATE INDEX idx_ph_portfolio_stock_qty
     186  ON portfolio_holdings (portfolio_id, stock_id, quantity)
     187  WHERE quantity > 0;
     188
     189
     190
    55191
    56192EXPLAIN ANALYZE
    57 SELECT
    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
    63 FROM portfolio_holdings ph
    64 JOIN stock s ON ph.stock_id = s.id
    65 WHERE quantity > 0 AND portfolio_id = 1;
    66 
    67 QUERY PLAN                                                                                                                      |
    68 --------------------------------------------------------------------------------------------------------------------------------+
    69 Nested 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)                                                                                    |
    79 Planning Time: 0.471 ms                                                                                                         |
    80 Execution Time: 5.802 ms                                                                                                        |
    81 
    82 }}}
     193...
     194
     195
     196QUERY PLAN                                                                                                                                                                                                                            |
     197--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     198Sort  (cost=20008.39..20010.89 rows=1000 width=448) (actual time=1159.420..1159.488 rows=1000 loops=1)                                                                                                                                |
     199  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|
     200  Sort Method: quicksort  Memory: 212kB                                                                                                                                                                                               |
     201  CTE stock_weights                                                                                                                                                                                                                   |
     202    ->  GroupAggregate  (cost=1.53..8892.19 rows=50009 width=93) (actual time=0.297..343.555 rows=50000 loops=1)                                                                                                                      |
     203          Group Key: ph.portfolio_id, ph.stock_id, s.symbol                                                                                                                                                                           |
     204          ->  Incremental Sort  (cost=1.53..6516.76 rows=50009 width=39) (actual time=0.261..101.899 rows=50000 loops=1)                                                                                                              |
     205                Sort Key: ph.portfolio_id, ph.stock_id, s.symbol                                                                                                                                                                      |
     206                Presorted Key: ph.portfolio_id, ph.stock_id                                                                                                                                                                           |
     207                Full-sort Groups: 1563  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                                               |
     208                ->  Nested Loop  (cost=0.57..5006.72 rows=50009 width=39) (actual time=0.083..84.356 rows=50000 loops=1)                                                                                                              |
     209                      ->  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)                                               |
     210                      ->  Memoize  (cost=0.15..0.17 rows=1 width=21) (actual time=0.000..0.000 rows=1 loops=50000)                                                                                                                    |
     211                            Cache Key: ph.stock_id                                                                                                                                                                                    |
     212                            Cache Mode: logical                                                                                                                                                                                       |
     213                            Hits: 49950  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 7kB                                                                                                                                    |
     214                            ->  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)                                                                                  |
     215                                  Index Cond: (id = ph.stock_id)                                                                                                                                                                      |
     216  CTE portfolio_totals                                                                                                                                                                                                                |
     217    ->  GroupAggregate  (cost=0.00..1628.79 rows=200 width=112) (actual time=0.702..862.171 rows=1000 loops=1)                                                                                                                        |
     218          Group Key: sw_2.sw_portfolio_id                                                                                                                                                                                             |
     219          ->  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)                                                                                                |
     220  ->  WindowAgg  (cost=9187.09..9234.58 rows=1000 width=448) (actual time=1154.807..1157.221 rows=1000 loops=1)                                                                                                                       |
     221        ->  Sort  (cost=9187.08..9189.58 rows=1000 width=817) (actual time=1154.785..1154.927 rows=1000 loops=1)                                                                                                                      |
     222              Sort Key: (COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '1'::numeric))                                                                               |
     223              Sort Method: quicksort  Memory: 187kB                                                                                                                                                                                   |
     224              ->  Hash Join  (cost=90.03..8773.25 rows=1000 width=817) (actual time=2.268..1152.842 rows=1000 loops=1)                                                                                                                |
     225                    Hash Cond: (p.user_id = u.id)                                                                                                                                                                                     |
     226                    ->  Merge Left Join  (cost=22.76..8703.34 rows=1000 width=772) (actual time=1.460..1150.243 rows=1000 loops=1)                                                                                                    |
     227                          Merge Cond: (p.id = sw_1.sw_portfolio_id)                                                                                                                                                                   |
     228                          ->  Merge Left Join  (cost=0.28..3445.38 rows=1000 width=224) (actual time=1.312..995.965 rows=1000 loops=1)                                                                                                |
     229                                Merge Cond: (p.id = sw.sw_portfolio_id)                                                                                                                                                               |
     230                                ->  Merge Left Join  (cost=0.28..55.77 rows=1000 width=128) (actual time=0.789..5.346 rows=1000 loops=1)                                                                                              |
     231                                      Merge Cond: (p.id = pt.pt_portfolio_id)                                                                                                                                                         |
     232                                      ->  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)                                                        |
     233                                      ->  Materialize  (cost=0.00..4.50 rows=200 width=112) (actual time=0.707..1.297 rows=1000 loops=1)                                                                                              |
     234                                            ->  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)                                                                    |
     235                                ->  GroupAggregate  (cost=0.00..3382.61 rows=200 width=104) (actual time=0.520..989.374 rows=1000 loops=1)                                                                                            |
     236                                      Group Key: sw.sw_portfolio_id                                                                                                                                                                   |
     237                                      ->  Merge Join  (cost=0.00..1754.82 rows=50009 width=72) (actual time=0.004..887.199 rows=50000 loops=1)                                                                                        |
     238                                            Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id)                                                                                                                                   |
     239                                            ->  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)                                                                 |
     240                                            ->  Materialize  (cost=0.00..1125.20 rows=50009 width=40) (actual time=0.002..12.323 rows=50000 loops=1)                                                                                  |
     241                                                  ->  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)                                                            |
     242                          ->  Unique  (cost=22.48..5250.95 rows=200 width=588) (actual time=0.146..152.842 rows=1000 loops=1)                                                                                                         |
     243                                ->  Incremental Sort  (cost=22.48..5125.93 rows=50009 width=588) (actual time=0.145..148.672 rows=49951 loops=1)                                                                                      |
     244                                      Sort Key: sw_1.sw_portfolio_id, ((sw_1.stock_market_value / NULLIF(pt_2.total_holdings_value, '0'::numeric))) DESC                                                                              |
     245                                      Presorted Key: sw_1.sw_portfolio_id                                                                                                                                                             |
     246                                      Full-sort Groups: 1000  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                         |
     247                                      ->  Merge Join  (cost=0.00..2504.95 rows=50009 width=588) (actual time=0.006..91.344 rows=50000 loops=1)                                                                                        |
     248                                            Merge Cond: (pt_2.pt_portfolio_id = sw_1.sw_portfolio_id)                                                                                                                                 |
     249                                            ->  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)                                                                   |
     250                                            ->  Materialize  (cost=0.00..1125.20 rows=50009 width=556) (actual time=0.001..28.674 rows=50000 loops=1)                                                                                 |
     251                                                  ->  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)                                                        |
     252                    ->  Hash  (cost=54.77..54.77 rows=1000 width=21) (actual time=0.793..0.795 rows=1000 loops=1)                                                                                                                     |
     253                          Buckets: 1024  Batches: 1  Memory Usage: 61kB                                                                                                                                                               |
     254                          ->  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)                                                                              |
     255                                Filter: ((role)::text = 'USER'::text)                                                                                                                                                                 |
     256Planning Time: 186.270 ms                                                                                                                                                                                                             |
     257Execution Time: 1160.749 ms                                                                                                                                                                                                           |
     258
     259}}}
     260
     261idx_ph_portfolio_stock_qty => Index Scan using idx_ph_portfolio_stock_qty on portfolio_holdings ph
    83262
    84263---
    85264
    86 **2. portfolio_holdings - Index (portfolio_id)**
    87 {{{
    88 CREATE INDEX idx_ph_portfolio_id ON portfolio_holdings(portfolio_id);
    89 ANALYZE portfolio_holdings;
     265**2. idx_stock_id_covering (Covering index на stock) **
     266
     267Covering индекс на stock(id) со INCLUDE (symbol, current_price) за елиминирање на heap fetches.
     268
     269
     270{{{
     271CREATE INDEX idx_stock_id_covering
     272  ON stock (id)
     273  INCLUDE (symbol, current_price);
     274
     275
    90276
    91277EXPLAIN ANALYZE
    92 SELECT
    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
    98 FROM portfolio_holdings ph
    99 JOIN stock s ON ph.stock_id = s.id
    100 WHERE quantity > 0 AND portfolio_id = 1;
    101 
    102 
    103 QUERY PLAN                                                                                                                        |
    104 ----------------------------------------------------------------------------------------------------------------------------------+
    105 Hash Join  (cost=9.00..181.17 rows=50 width=85) (actual time=0.245..0.515 rows=50 loops=1)                                        |
    106   Hash Cond: (ph.stock_id = s.id)                                                                                                 |
    107   ->  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)  |
    108         Recheck Cond: (portfolio_id = 1)                                                                                          |
    109         Filter: (quantity > 0)                                                                                                    |
    110         Rows Removed by Filter: 10                                                                                                |
    111         Heap Blocks: exact=60                                                                                                     |
    112         ->  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)|
    113               Index Cond: (portfolio_id = 1)                                                                                      |
    114   ->  Hash  (cost=3.00..3.00 rows=100 width=21) (actual time=0.059..0.060 rows=100 loops=1)                                       |
    115         Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                             |
    116         ->  Seq Scan on stock s  (cost=0.00..3.00 rows=100 width=21) (actual time=0.019..0.035 rows=100 loops=1)                  |
    117 Planning Time: 0.526 ms                                                                                                           |
    118 Execution Time: 0.554 ms                                                                                                          |
    119 
    120 }}}
    121 
    122 
    123 Подобрување: Index Scan наместо Seq Scan при групирање по портфолио
     278...
     279
     280QUERY PLAN                                                                                                                                                                                                                            |
     281--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     282Sort  (cost=20008.39..20010.89 rows=1000 width=448) (actual time=743.813..743.880 rows=1000 loops=1)                                                                                                                                  |
     283  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|
     284  Sort Method: quicksort  Memory: 212kB                                                                                                                                                                                               |
     285  CTE stock_weights                                                                                                                                                                                                                   |
     286    ->  GroupAggregate  (cost=1.53..8892.19 rows=50009 width=93) (actual time=0.732..359.488 rows=50000 loops=1)                                                                                                                      |
     287          Group Key: ph.portfolio_id, ph.stock_id, s.symbol                                                                                                                                                                           |
     288          ->  Incremental Sort  (cost=1.53..6516.76 rows=50009 width=39) (actual time=0.700..108.625 rows=50000 loops=1)                                                                                                              |
     289                Sort Key: ph.portfolio_id, ph.stock_id, s.symbol                                                                                                                                                                      |
     290                Presorted Key: ph.portfolio_id, ph.stock_id                                                                                                                                                                           |
     291                Full-sort Groups: 1563  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                                               |
     292                ->  Nested Loop  (cost=0.57..5006.72 rows=50009 width=39) (actual time=0.084..89.347 rows=50000 loops=1)                                                                                                              |
     293                      ->  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)                                               |
     294                      ->  Memoize  (cost=0.15..0.17 rows=1 width=21) (actual time=0.000..0.000 rows=1 loops=50000)                                                                                                                    |
     295                            Cache Key: ph.stock_id                                                                                                                                                                                    |
     296                            Cache Mode: logical                                                                                                                                                                                       |
     297                            Hits: 49950  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 7kB                                                                                                                                    |
     298                            ->  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)                                                                  |
     299                                  Index Cond: (id = ph.stock_id)                                                                                                                                                                      |
     300                                  Heap Fetches: 50                                                                                                                                                                                    |
     301  CTE portfolio_totals                                                                                                                                                                                                                |
     302    ->  GroupAggregate  (cost=0.00..1628.79 rows=200 width=112) (actual time=1.455..434.830 rows=1000 loops=1)                                                                                                                        |
     303          Group Key: sw_2.sw_portfolio_id                                                                                                                                                                                             |
     304          ->  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)                                                                                                |
     305  ->  WindowAgg  (cost=9187.09..9234.58 rows=1000 width=448) (actual time=739.406..741.751 rows=1000 loops=1)                                                                                                                         |
     306        ->  Sort  (cost=9187.08..9189.58 rows=1000 width=817) (actual time=739.382..739.512 rows=1000 loops=1)                                                                                                                        |
     307              Sort Key: (COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '1'::numeric))                                                                               |
     308              Sort Method: quicksort  Memory: 187kB                                                                                                                                                                                   |
     309              ->  Hash Join  (cost=90.03..8773.25 rows=1000 width=817) (actual time=2.938..737.368 rows=1000 loops=1)                                                                                                                 |
     310                    Hash Cond: (p.user_id = u.id)                                                                                                                                                                                     |
     311                    ->  Merge Left Join  (cost=22.76..8703.34 rows=1000 width=772) (actual time=2.190..734.798 rows=1000 loops=1)                                                                                                     |
     312                          Merge Cond: (p.id = sw_1.sw_portfolio_id)                                                                                                                                                                   |
     313                          ->  Merge Left Join  (cost=0.28..3445.38 rows=1000 width=224) (actual time=2.043..573.048 rows=1000 loops=1)                                                                                                |
     314                                Merge Cond: (p.id = sw.sw_portfolio_id)                                                                                                                                                               |
     315                                ->  Merge Left Join  (cost=0.28..55.77 rows=1000 width=128) (actual time=1.518..6.548 rows=1000 loops=1)                                                                                              |
     316                                      Merge Cond: (p.id = pt.pt_portfolio_id)                                                                                                                                                         |
     317                                      ->  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)                                                        |
     318                                      ->  Materialize  (cost=0.00..4.50 rows=200 width=112) (actual time=1.459..2.160 rows=1000 loops=1)                                                                                              |
     319                                            ->  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)                                                                    |
     320                                ->  GroupAggregate  (cost=0.00..3382.61 rows=200 width=104) (actual time=0.522..565.120 rows=1000 loops=1)                                                                                            |
     321                                      Group Key: sw.sw_portfolio_id                                                                                                                                                                   |
     322                                      ->  Merge Join  (cost=0.00..1754.82 rows=50009 width=72) (actual time=0.004..460.251 rows=50000 loops=1)                                                                                        |
     323                                            Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id)                                                                                                                                   |
     324                                            ->  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)                                                                 |
     325                                            ->  Materialize  (cost=0.00..1125.20 rows=50009 width=40) (actual time=0.002..12.786 rows=50000 loops=1)                                                                                  |
     326                                                  ->  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)                                                            |
     327                          ->  Unique  (cost=22.48..5250.95 rows=200 width=588) (actual time=0.145..160.204 rows=1000 loops=1)                                                                                                         |
     328                                ->  Incremental Sort  (cost=22.48..5125.93 rows=50009 width=588) (actual time=0.144..155.872 rows=49951 loops=1)                                                                                      |
     329                                      Sort Key: sw_1.sw_portfolio_id, ((sw_1.stock_market_value / NULLIF(pt_2.total_holdings_value, '0'::numeric))) DESC                                                                              |
     330                                      Presorted Key: sw_1.sw_portfolio_id                                                                                                                                                             |
     331                                      Full-sort Groups: 1000  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                         |
     332                                      ->  Merge Join  (cost=0.00..2504.95 rows=50009 width=588) (actual time=0.006..96.233 rows=50000 loops=1)                                                                                        |
     333                                            Merge Cond: (pt_2.pt_portfolio_id = sw_1.sw_portfolio_id)                                                                                                                                 |
     334                                            ->  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)                                                                   |
     335                                            ->  Materialize  (cost=0.00..1125.20 rows=50009 width=556) (actual time=0.002..31.566 rows=50000 loops=1)                                                                                 |
     336                                                  ->  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)                                                        |
     337                    ->  Hash  (cost=54.77..54.77 rows=1000 width=21) (actual time=0.737..0.738 rows=1000 loops=1)                                                                                                                     |
     338                          Buckets: 1024  Batches: 1  Memory Usage: 61kB                                                                                                                                                               |
     339                          ->  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)                                                                              |
     340                                Filter: ((role)::text = 'USER'::text)                                                                                                                                                                 |
     341Planning Time: 1.743 ms                                                                                                                                                                                                               |
     342Execution Time: 745.179 ms                                                                                                                                                                                                            |
     343
     344
     345
     346}}}
     347
     348
     349idx_stock_id_covering => Index Only Scan using idx_stock_id_covering on stock s + Heap Fetches: 50 (наместо претходниот Index Scan using stock_pkey кој правеше heap fetch за секој ред)
    124350
    125351---
    126352
    127353
    128 **3. partial index**
    129 {{{
    130 CREATE INDEX idx_ph_quantity_positive
    131 ON portfolio_holdings(portfolio_id, stock_id)
    132 WHERE quantity > 0;
    133 
    134 ANALYZE portfolio_holdings;
     354**3. idx_portfolios_user_id (Foreign key index) **
     355
     356Индекс на portfolios(user_id) за оптимизација на JOIN-от portfolios => users.
     357
     358{{{
     359 CREATE INDEX idx_portfolios_user_id
     360  ON portfolios (user_id);
     361
     362
    135363
    136364EXPLAIN ANALYZE
    137 SELECT
    138   ph.portfolio_id,
    139   ph.stock_id,
    140   s.symbol,
    141   ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
    142   ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
    143 FROM portfolio_holdings ph
    144 JOIN stock s ON ph.stock_id = s.id
    145 WHERE quantity > 0 AND portfolio_id = 1;
    146 
    147 
    148 QUERY PLAN                                                                                                                             |
    149 ---------------------------------------------------------------------------------------------------------------------------------------+
    150 Hash Join  (cost=8.93..158.07 rows=50 width=85) (actual time=0.315..0.573 rows=50 loops=1)                                             |
    151   Hash Cond: (ph.stock_id = s.id)                                                                                                      |
    152   ->  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)       |
    153         Recheck Cond: ((portfolio_id = 1) AND (quantity > 0))                                                                          |
    154         Heap Blocks: exact=50                                                                                                          |
    155         ->  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)|
    156               Index Cond: (portfolio_id = 1)                                                                                           |
    157   ->  Hash  (cost=3.00..3.00 rows=100 width=21) (actual time=0.059..0.060 rows=100 loops=1)                                            |
    158         Buckets: 1024  Batches: 1  Memory Usage: 14kB                                                                                  |
    159         ->  Seq Scan on stock s  (cost=0.00..3.00 rows=100 width=21) (actual time=0.021..0.037 rows=100 loops=1)                       |
    160 Planning Time: 0.401 ms                                                                                                                |
    161 Execution Time: 0.409 ms                                                                                                               |
    162 
    163 }}}
     365...
     366
     367QUERY PLAN                                                                                                                                                                                                                            |
     368--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     369Sort  (cost=20008.39..20010.89 rows=1000 width=448) (actual time=714.206..714.272 rows=1000 loops=1)                                                                                                                                  |
     370  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|
     371  Sort Method: quicksort  Memory: 212kB                                                                                                                                                                                               |
     372  CTE stock_weights                                                                                                                                                                                                                   |
     373    ->  GroupAggregate  (cost=1.53..8892.19 rows=50009 width=93) (actual time=0.974..347.230 rows=50000 loops=1)                                                                                                                      |
     374          Group Key: ph.portfolio_id, ph.stock_id, s.symbol                                                                                                                                                                           |
     375          ->  Incremental Sort  (cost=1.53..6516.76 rows=50009 width=39) (actual time=0.941..105.364 rows=50000 loops=1)                                                                                                              |
     376                Sort Key: ph.portfolio_id, ph.stock_id, s.symbol                                                                                                                                                                      |
     377                Presorted Key: ph.portfolio_id, ph.stock_id                                                                                                                                                                           |
     378                Full-sort Groups: 1563  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                                               |
     379                ->  Nested Loop  (cost=0.57..5006.72 rows=50009 width=39) (actual time=0.102..87.212 rows=50000 loops=1)                                                                                                              |
     380                      ->  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)                                               |
     381                      ->  Memoize  (cost=0.15..0.17 rows=1 width=21) (actual time=0.000..0.000 rows=1 loops=50000)                                                                                                                    |
     382                            Cache Key: ph.stock_id                                                                                                                                                                                    |
     383                            Cache Mode: logical                                                                                                                                                                                       |
     384                            Hits: 49950  Misses: 50  Evictions: 0  Overflows: 0  Memory Usage: 7kB                                                                                                                                    |
     385                            ->  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)                                                                  |
     386                                  Index Cond: (id = ph.stock_id)                                                                                                                                                                      |
     387                                  Heap Fetches: 50                                                                                                                                                                                    |
     388  CTE portfolio_totals                                                                                                                                                                                                                |
     389    ->  GroupAggregate  (cost=0.00..1628.79 rows=200 width=112) (actual time=1.730..416.989 rows=1000 loops=1)                                                                                                                        |
     390          Group Key: sw_2.sw_portfolio_id                                                                                                                                                                                             |
     391          ->  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)                                                                                                |
     392  ->  WindowAgg  (cost=9187.09..9234.58 rows=1000 width=448) (actual time=709.858..712.200 rows=1000 loops=1)                                                                                                                         |
     393        ->  Sort  (cost=9187.08..9189.58 rows=1000 width=817) (actual time=709.836..709.963 rows=1000 loops=1)                                                                                                                        |
     394              Sort Key: (COALESCE((sum(power((sw.stock_market_value / NULLIF(pt_1.total_holdings_value, '0'::numeric)), '2'::numeric))), '1'::numeric))                                                                               |
     395              Sort Method: quicksort  Memory: 187kB                                                                                                                                                                                   |
     396              ->  Hash Join  (cost=90.03..8773.25 rows=1000 width=817) (actual time=3.217..707.863 rows=1000 loops=1)                                                                                                                 |
     397                    Hash Cond: (p.user_id = u.id)                                                                                                                                                                                     |
     398                    ->  Merge Left Join  (cost=22.76..8703.34 rows=1000 width=772) (actual time=2.453..705.425 rows=1000 loops=1)                                                                                                     |
     399                          Merge Cond: (p.id = sw_1.sw_portfolio_id)                                                                                                                                                                   |
     400                          ->  Merge Left Join  (cost=0.28..3445.38 rows=1000 width=224) (actual time=2.306..550.820 rows=1000 loops=1)                                                                                                |
     401                                Merge Cond: (p.id = sw.sw_portfolio_id)                                                                                                                                                               |
     402                                ->  Merge Left Join  (cost=0.28..55.77 rows=1000 width=128) (actual time=1.793..6.307 rows=1000 loops=1)                                                                                              |
     403                                      Merge Cond: (p.id = pt.pt_portfolio_id)                                                                                                                                                         |
     404                                      ->  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)                                                        |
     405                                      ->  Materialize  (cost=0.00..4.50 rows=200 width=112) (actual time=1.735..2.412 rows=1000 loops=1)                                                                                              |
     406                                            ->  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)                                                                    |
     407                                ->  GroupAggregate  (cost=0.00..3382.61 rows=200 width=104) (actual time=0.510..543.124 rows=1000 loops=1)                                                                                            |
     408                                      Group Key: sw.sw_portfolio_id                                                                                                                                                                   |
     409                                      ->  Merge Join  (cost=0.00..1754.82 rows=50009 width=72) (actual time=0.005..441.070 rows=50000 loops=1)                                                                                        |
     410                                            Merge Cond: (pt_1.pt_portfolio_id = sw.sw_portfolio_id)                                                                                                                                   |
     411                                            ->  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)                                                                 |
     412                                            ->  Materialize  (cost=0.00..1125.20 rows=50009 width=40) (actual time=0.003..12.318 rows=50000 loops=1)                                                                                  |
     413                                                  ->  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)                                                            |
     414                          ->  Unique  (cost=22.48..5250.95 rows=200 width=588) (actual time=0.144..153.167 rows=1000 loops=1)                                                                                                         |
     415                                ->  Incremental Sort  (cost=22.48..5125.93 rows=50009 width=588) (actual time=0.144..149.002 rows=49951 loops=1)                                                                                      |
     416                                      Sort Key: sw_1.sw_portfolio_id, ((sw_1.stock_market_value / NULLIF(pt_2.total_holdings_value, '0'::numeric))) DESC                                                                              |
     417                                      Presorted Key: sw_1.sw_portfolio_id                                                                                                                                                             |
     418                                      Full-sort Groups: 1000  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB                                                                                                         |
     419                                      ->  Merge Join  (cost=0.00..2504.95 rows=50009 width=588) (actual time=0.006..91.481 rows=50000 loops=1)                                                                                        |
     420                                            Merge Cond: (pt_2.pt_portfolio_id = sw_1.sw_portfolio_id)                                                                                                                                 |
     421                                            ->  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)                                                                   |
     422                                            ->  Materialize  (cost=0.00..1125.20 rows=50009 width=556) (actual time=0.002..28.890 rows=50000 loops=1)                                                                                 |
     423                                                  ->  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)                                                        |
     424                    ->  Hash  (cost=54.77..54.77 rows=1000 width=21) (actual time=0.752..0.753 rows=1000 loops=1)                                                                                                                     |
     425                          Buckets: 1024  Batches: 1  Memory Usage: 61kB                                                                                                                                                               |
     426                          ->  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)                                                                              |
     427                                Filter: ((role)::text = 'USER'::text)                                                                                                                                                                 |
     428Planning Time: 1.951 ms                                                                                                                                                                                                               |
     429Execution Time: 715.560 ms                                                                                                                                                                                                            |
     430
     431}}}
     432
     433idx_portfolios_user_id => Овој не се гледа во планот — планерот продолжи да го користи portfolios_pkey. Причина: сите 1.000 портфолија се читаат (нема селективен филтер), па PK скенот е поефикасен.
    164434
    165435
    166436---
    167437
    168 **4. Add index on users.role**
    169 {{{
    170 CREATE INDEX idx_users_role ON users(role);
    171 ANALYZE users;
    172 
    173 EXPLAIN ANALYZE
    174 SELECT
    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
    180 FROM portfolio_holdings ph
    181 JOIN stock s ON ph.stock_id = s.id
    182 WHERE quantity > 0 AND portfolio_id = 1;
    183 
    184 
    185 
    186 
    187 }}}
    188 
    189 Овој индекс не влијае на перформансите на оваа анализа бидејќи табелата users не е дел од query-то.
    190 
    191 ---
    192438
    193439'''Сумарно:'''
    194440
    195441
    196 20x побрзо
     44275% побрзо.
    197443
    198444----