Changes between Version 5 and Version 6 of QueryOptimization


Ignore:
Timestamp:
05/12/26 10:47:18 (2 weeks ago)
Author:
231136
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v5 v6  
    11= Оптимизација на прашалници и погледи =
    22
     3Во оваа фаза ќе ги анализираме погледите дефинирани во [[DatabaseCreation|Фаза 2]] преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
     4
    35=== 1. Анализа на поглед 1, добивање на бројот на следбеници и бројот на профили кои ги следи даден корисник ===
    4 - Доколку сакаме да видиме за конкретен артист извршуваме:
    5 
    6   [[Image(View1_1.png, 800px)]]
    7 
    8   - Времето потребно за извршување за овој прашалник е **~6ms**. Нема потреба од дополнителни индекси.
    9   - Доколку сакаме да извршиме некаква анализа, на пример да ги земеме најдобрите 10 корисници според број на следбеници, можеме да извршиме:
    10 
    11     [[Image(View1_2.png, 800px)]]
    12 
    13   - Потребно време за извршување на овој прашалник е **~200ms**. Нема индекс кој може да се додаде за да ги подобри перформансите на овој прашалник.
    14 
    15   - Query plan:
    16    
    17   [[Image(View1_3.png, 800px)]]
     6
     7Прашалниците кои ќе ги тестираме се следните:
     8{{{
     9-- 1A: информации за конкретен корисник
     10SELECT * FROM user_follow_info WHERE user_id = 5;
     11
     12-- 1B: топ 10 најследени корисници
     13SELECT * FROM user_follow_info ORDER BY followers DESC LIMIT 10;
     14}}}
     15
     16==== Време на извршување без индекси:
     17
     18**1A - 18.639 ms**
     19
     20{{{
     21 Nested Loop Left Join  (cost=0.84..2007.41 rows=1 width=41) (actual time=18.370..18.375 rows=1 loops=1)
     22   ->  Nested Loop Left Join  (cost=0.42..1998.96 rows=1 width=24) (actual time=16.163..16.167 rows=1 loops=1)
     23         ->  GroupAggregate  (cost=0.42..8.45 rows=1 width=16) (actual time=0.099..0.101 rows=1 loops=1)
     24               ->  Index Only Scan using follows_follower_user_id_followed_user_id_key on follows  (cost=0.42..8.44 rows=1 width=16) (actual time=0.090..0.093 rows=1 loops=1)
     25                     Index Cond: (follower_user_id = 5)
     26                     Heap Fetches: 1
     27         ->  GroupAggregate  (cost=0.00..1990.49 rows=1 width=16) (actual time=16.060..16.061 rows=1 loops=1)
     28               ->  Seq Scan on follows follows_1  (cost=0.00..1986.00 rows=1793 width=8) (actual time=0.073..15.916 rows=1727 loops=1)
     29                     Filter: (followed_user_id = 5)
     30                     Rows Removed by Filter: 98273
     31   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=2.202..2.202 rows=1 loops=1)
     32         Index Cond: (id = 5)
     33 Planning Time: 2.882 ms
     34 Execution Time: 18.639 ms
     35}}}
     36
     37**1B - 6055.326 ms**
     38
     39{{{
     40 Limit  (cost=193416.23..193416.26 rows=10 width=41) (actual time=6013.127..6013.139 rows=10 loops=1)
     41   ->  Sort  (cost=193416.23..207558.87 rows=5657054 width=41) (actual time=5995.646..5995.657 rows=10 loops=1)
     42         Sort Key: (COALESCE(uf2.followers, '0'::bigint)) DESC
     43         Sort Method: top-N heapsort  Memory: 25kB
     44         ->  Hash Left Join  (cost=17633.85..71169.33 rows=5657054 width=41) (actual time=1875.103..5973.746 rows=95177 loops=1)
     45               Hash Cond: (follows.follower_user_id = uf2.user_id)
     46               ->  Hash Right Join  (cost=9034.90..60811.91 rows=92693 width=33) (actual time=1798.455..5836.973 rows=95177 loops=1)
     47                     Hash Cond: (u.id = follows.follower_user_id)
     48                     ->  Seq Scan on users u  (cost=0.00..35027.00 rows=1000000 width=25) (actual time=1598.256..4992.144 rows=1000000 loops=1)
     49                     ->  Hash  (cost=7423.24..7423.24 rows=92693 width=16) (actual time=200.143..200.146 rows=95177 loops=1)
     50                           Buckets: 16384  Batches: 16  Memory Usage: 407kB
     51                           ->  GroupAggregate  (cost=0.42..7423.24 rows=92693 width=16) (actual time=0.180..164.602 rows=95177 loops=1)
     52                                 Group Key: follows.follower_user_id
     53                                 ->  Index Only Scan using follows_follower_user_id_followed_user_id_key on follows  (cost=0.42..5996.31 rows=100000 width=16) (actual time=0.134..130.406 rows=100000 loops=1)
     54                                       Heap Fetches: 100000
     55               ->  Hash  (cost=8386.37..8386.37 rows=12206 width=16) (actual time=76.356..76.358 rows=30563 loops=1)
     56                     Buckets: 16384 (originally 16384)  Batches: 4 (originally 2)  Memory Usage: 480kB
     57                     ->  Subquery Scan on uf2  (cost=7361.00..8386.37 rows=12206 width=16) (actual time=43.415..66.845 rows=30563 loops=1)
     58                           ->  HashAggregate  (cost=7361.00..8264.31 rows=12206 width=16) (actual time=43.406..64.059 rows=30563 loops=1)
     59                                 Group Key: follows_1.followed_user_id
     60                                 Planned Partitions: 4  Batches: 21  Memory Usage: 601kB  Disk Usage: 824kB
     61                                 ->  Seq Scan on follows follows_1  (cost=0.00..1736.00 rows=100000 width=8) (actual time=0.022..11.332 rows=100000 loops=1)
     62 Planning Time: 1.715 ms
     63 Execution Time: 6055.326 ms
     64}}}
     65
     66
     67Веќе постои индекс на `(follower_user_id, followed_user_id)` поради unique constraint во ddl-от, па `follower_user_id` може да се земе од таму, но за да се земе `followed_user_id` мора да се скенира табелата секвенцијално. Затоа, го додаваме следниот индекс:
     68{{{
     69CREATE INDEX idx_follows_followed_user_id ON follows(followed_user_id);
     70}}}
     71
     72
     73==== Време на извршување со индекс:
     74
     75**1A — 3.993 ms** (беше 18.639 ms)
     76
     77{{{
     78 Nested Loop Left Join  (cost=27.03..806.01 rows=1 width=41) (actual time=3.702..3.704 rows=1 loops=1)
     79   ->  Nested Loop Left Join  (cost=26.61..797.56 rows=1 width=24) (actual time=3.683..3.685 rows=1 loops=1)
     80         ->  GroupAggregate  (cost=0.42..8.45 rows=1 width=16) (actual time=0.097..0.098 rows=1 loops=1)
     81               ->  Index Only Scan using follows_follower_user_id_followed_user_id_key on follows  (cost=0.42..8.44 rows=1 width=16) (actual time=0.088..0.090 rows=1 loops=1)
     82                     Index Cond: (follower_user_id = 5)
     83                     Heap Fetches: 1
     84         ->  GroupAggregate  (cost=26.19..789.09 rows=1 width=16) (actual time=3.582..3.583 rows=1 loops=1)
     85               ->  Bitmap Heap Scan on follows follows_1  (cost=26.19..784.60 rows=1793 width=8) (actual time=0.363..3.396 rows=1727 loops=1)
     86                     Recheck Cond: (followed_user_id = 5)
     87                     Heap Blocks: exact=666
     88                     ->  Bitmap Index Scan on idx_follows_followed_user_id  (cost=0.00..25.74 rows=1793 width=0) (actual time=0.255..0.255 rows=1727 loops=1)
     89                           Index Cond: (followed_user_id = 5)
     90   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=0.016..0.016 rows=1 loops=1)
     91         Index Cond: (id = 5)
     92 Planning Time: 2.325 ms
     93 Execution Time: 3.993 ms
     94}}}
     95
     96**1B — 1158.681 ms** (беше 6055.326 ms)
     97
     98{{{
     99 Limit  (cost=190809.26..190809.28 rows=10 width=41) (actual time=1119.728..1119.739 rows=10 loops=1)
     100   ->  Sort  (cost=190809.26..204951.89 rows=5657054 width=41) (actual time=1104.495..1104.505 rows=10 loops=1)
     101         Sort Key: (COALESCE(uf2.followers, '0'::bigint)) DESC
     102         Sort Method: top-N heapsort  Memory: 25kB
     103         ->  Hash Left Join  (cost=15026.87..68562.35 rows=5657054 width=41) (actual time=312.601..1083.510 rows=95177 loops=1)
     104               Hash Cond: (follows.follower_user_id = uf2.user_id)
     105               ->  Hash Right Join  (cost=9034.90..60811.91 rows=92693 width=33) (actual time=210.510..922.405 rows=95177 loops=1)
     106                     Hash Cond: (u.id = follows.follower_user_id)
     107                     ->  Seq Scan on users u  (cost=0.00..35027.00 rows=1000000 width=25) (actual time=62.089..259.973 rows=1000000 loops=1)
     108                     ->  Hash  (cost=7423.24..7423.24 rows=92693 width=16) (actual time=148.205..148.208 rows=95177 loops=1)
     109                           Buckets: 16384  Batches: 16  Memory Usage: 407kB
     110                           ->  GroupAggregate  (cost=0.42..7423.24 rows=92693 width=16) (actual time=0.140..116.621 rows=95177 loops=1)
     111                                 Group Key: follows.follower_user_id
     112                                 ->  Index Only Scan using follows_follower_user_id_followed_user_id_key on follows  (cost=0.42..5996.31 rows=100000 width=16) (actual time=0.094..83.835 rows=100000 loops=1)
     113                                       Heap Fetches: 100000
     114               ->  Hash  (cost=5779.40..5779.40 rows=12206 width=16) (actual time=101.825..101.827 rows=30563 loops=1)
     115                     Buckets: 16384 (originally 16384)  Batches: 4 (originally 2)  Memory Usage: 480kB
     116                     ->  Subquery Scan on uf2  (cost=0.29..5779.40 rows=12206 width=16) (actual time=1.560..90.836 rows=30563 loops=1)
     117                           ->  GroupAggregate  (cost=0.29..5657.34 rows=12206 width=16) (actual time=1.552..87.771 rows=30563 loops=1)
     118                                 Group Key: follows_1.followed_user_id
     119                                 ->  Index Only Scan using idx_follows_followed_user_id on follows follows_1  (cost=0.29..5035.28 rows=100000 width=8) (actual time=0.057..71.498 rows=100000 loops=1)
     120                                       Heap Fetches: 100000
     121 Planning Time: 1.857 ms
     122 Execution Time: 1158.681 ms
     123}}}
    18124
    19125=== 2. Анализа на поглед 2, најактивни корисници на платформата според бројот на слушања во изминатите 30 дена ===
    20126
    21 - Доколку сакаме да видиме кои се најактивните корисници на платформата изминатите 30 дена, извршуваме:
    22  
    23   [[Image(View2_1.png, 800px)]]
    24 
    25 - Времето потребно за пребарување:
    26 
    27   [[Image(View2_2.png, 800px)]]
    28 
    29 - **~250ms**
    30 
    31 - Можеме да додадеме индекс на streamed_at колоната со цел да избегнеме sequential scan на song_streams табелата:
    32 
    33   [[Image(View2_3.png, 800px)]]
    34 
    35 - Време на извршување по додавање на индекс:
    36 
    37   [[Image(View2_4.png, 800px)]]
    38 
    39 - **~200ms** -> ~20% подобри перформанси
     127Прашалниците кои ќе ги тестираме се следните:
     128{{{
     129-- 2A: активност на еден корисник
     130SELECT * FROM user_activity_last_30_days WHERE user_id = 100376;
     131
     132-- 2B: топ 10 најактивни корисници
     133SELECT * FROM user_activity_last_30_days ORDER BY stream_count DESC LIMIT 10;
     134}}}
     135
     136==== Време на извршување без индекси:
     137
     138
     139**2A — 389.404 ms**
     140
     141```
     142 Nested Loop  (cost=1000.42..128052.52 rows=1 width=33) (actual time=341.291..351.235 rows=1 loops=1)
     143   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=0.760..0.764 rows=1 loops=1)
     144         Index Cond: (id = 100376)
     145   ->  GroupAggregate  (cost=1000.00..128044.07 rows=1 width=16) (actual time=340.519..350.456 rows=1 loops=1)
     146         ->  Gather  (cost=1000.00..128044.06 rows=1 width=16) (actual time=137.273..350.389 rows=2 loops=1)
     147               Workers Planned: 2
     148               Workers Launched: 2
     149               ->  Parallel Seq Scan on song_streams ss  (cost=0.00..127043.96 rows=1 width=16) (actual time=149.103..281.419 rows=1 loops=3)
     150                     Filter: ((user_id = 100376) AND (streamed_at <= now()) AND (streamed_at >= (CURRENT_DATE - 30)))
     151                     Rows Removed by Filter: 2258560
     152 Planning Time: 1.687 ms
     153 Execution Time: 389.404 ms
     154```
     155
     156**2B — 1976.733 ms**
     157
     158```
     159Limit  (cost=193729.83..193814.38 rows=10 width=162) (actual time=1965.655..1965.803 rows=10 loops=1)
     160  ->  Result  (cost=193729.83..5188284.34 rows=590722 width=162) (actual time=1953.440..1953.585 rows=10 loops=1)
     161        ->  Sort  (cost=193729.83..195206.63 rows=590722 width=16) (actual time=1953.266..1953.270 rows=10 loops=1)
     162              Sort Key: (count(ss.song_id)) DESC
     163              Sort Method: top-N heapsort  Memory: 25kB
     164              ->  HashAggregate  (cost=165561.66..180964.54 rows=590722 width=16) (actual time=1326.637..1865.932 rows=593461 loops=1)
     165                    Group Key: ss.user_id
     166                    Planned Partitions: 8  Batches: 9  Memory Usage: 8273kB  Disk Usage: 31768kB
     167                    ->  Bitmap Heap Scan on song_streams ss  (cost=24927.09..103270.10 rows=972356 width=16) (actual time=109.482..553.897 rows=970108 loops=1)
     168                          Recheck Cond: ((streamed_at >= (CURRENT_DATE - 30)) AND (streamed_at <= now()))
     169                          Heap Blocks: exact=56465
     170                          ->  Bitmap Index Scan on idx_song_streams_streamed_at_song_id  (cost=0.00..24684.00 rows=972356 width=0) (actual time=95.352..95.353 rows=970108 loops=1)
     171                                Index Cond: ((streamed_at >= (CURRENT_DATE - 30)) AND (streamed_at <= now()))
     172        SubPlan 1
     173          ->  Index Scan using users_pkey on users  (cost=0.42..8.44 rows=1 width=17) (actual time=0.021..0.022 rows=1 loops=10)
     174                Index Cond: (id = ss.user_id)
     175Planning Time: 0.476 ms
     176JIT:
     177  Functions: 24
     178"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
     179"  Timing: Generation 2.144 ms (Deform 0.808 ms), Inlining 0.000 ms, Optimization 1.505 ms, Emission 19.128 ms, Total 22.777 ms"
     180Execution Time: 1976.733 ms
     181
     182```
     183
     184Бидејќи `song_streams` нема индекс на `user_id`, за прашалник 2А потребно е секвенцијално скенирање за да се најдат стримовите за еден корисник. Затоа, додаваме индекс на таа колона:
     185
     186{{{
     187CREATE INDEX idx_song_streams_user_id ON song_streams(user_id);
     188}}}
     189
     190==== Време за извршување по додавање на индекс
     191
     192**2A — 0.453 ms** (was 389.404 ms)
     193
     194{{{
     195 Nested Loop  (cost=0.86..45.14 rows=1 width=33) (actual time=0.295..0.297 rows=1 loops=1)
     196   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=0.071..0.072 rows=1 loops=1)
     197         Index Cond: (id = 100376)
     198   ->  GroupAggregate  (cost=0.43..36.68 rows=1 width=16) (actual time=0.220..0.221 rows=1 loops=1)
     199         ->  Index Scan using idx_song_streams_user_id on song_streams ss  (cost=0.43..36.67 rows=1 width=16) (actual time=0.089..0.214 rows=2 loops=1)
     200               Index Cond: (user_id = 100376)
     201               Filter: ((streamed_at <= now()) AND (streamed_at >= (CURRENT_DATE - 30)))
     202               Rows Removed by Filter: 8
     203 Planning Time: 1.843 ms
     204 Execution Time: 0.453 ms
     205}}}
     206
     2072Б остана непроменето - бидејќи прашалникот треба да направи комплексна агрегација на големи табели нема баш некој конкретен индекс што може да ги подобри перформансите. Доколку овој прашалник се извршува често во апликацијата, јасно е дека тоа може да доведе до проблеми. Ова можеме да го решиме на повеќе начини: со менување на погледот во материјализиран поглед, со кеширање и слично. Првиот пристап (материјализирани погледи) како решение ќе го погледнеме понатаму во оптимизацијата на други погледи, а конкретно за овој поглед ќе одиме со вториот пристап, поточно со кеширање во самиот апликациски код.
     208
    40209
    41210
    42211=== 3. Анализа на поглед 3, рангирање на песни по нивните просечни оценки и бројот на вкупни оценки, соодветно ===
    43212
    44 - Доколку сакаме да ги видиме најдобро оценуваните песни на платформата, извршуваме:
    45 
    46   [[Image(View3_1.png, 800px)]]
    47 
    48 - Време потребно за пребарување:
    49 
    50   [[Image(View3_2.png, 800px)]]
    51 
    52 - **~260ms**
    53 
    54 - Можеме да додадеме индекс на song_id, grade со цел да избегнеме sequential scan на songs:
    55 
    56   [[Image(View3_3.png, 800px)]]
    57 
    58 - Време на извршување по додавање на индекс:
    59  
    60   [[Image(View3_4.png, 800px)]]
    61 
    62 - **~200ms** -> ~20% подобри перформанси
    63 
    64 - Време потребно за внесување на нов запис без индекс:
    65 
    66   [[Image(View3_5.png, 800px)]]
    67  
    68   [[Image(View3_6.png, 800px)]]
    69 
    70 - Со индекс:
    71 
    72   [[Image(View3_7.png, 800px)]]
    73 
    74 - Време потребно за ажурирање на запис без индекс:
    75 
    76   [[Image(View3_8.png, 800px)]]
    77 
    78   [[Image(View3_9.png, 800px)]]
    79 
    80 - Со индекс:
    81 
    82   [[Image(View3_10.png, 800px)]]
    83 
    84 - Во двата случаи (запишување и ажурирање) нема никаков ефект врз перформансите додавање на индексот
    85  
     213
    86214
    87215=== 4. Анализа на поглед 4, рангирање на артистите по слушања (популарност) во изминатите 30 дена ===