Changes between Version 6 and Version 7 of QueryOptimization


Ignore:
Timestamp:
05/12/26 11:09:48 (2 weeks ago)
Author:
231136
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v6 v7  
    139139**2A — 389.404 ms**
    140140
    141 ```
     141{{{
    142142 Nested Loop  (cost=1000.42..128052.52 rows=1 width=33) (actual time=341.291..351.235 rows=1 loops=1)
    143143   ->  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)
    … …  
    152152 Planning Time: 1.687 ms
    153153 Execution Time: 389.404 ms
    154 ```
     154}}}
    155155
    156156**2B — 1976.733 ms**
    157157
    158 ```
     158{{{
    159159Limit  (cost=193729.83..193814.38 rows=10 width=162) (actual time=1965.655..1965.803 rows=10 loops=1)
    160160  ->  Result  (cost=193729.83..5188284.34 rows=590722 width=162) (actual time=1953.440..1953.585 rows=10 loops=1)
    … …  
    179179"  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"
    180180Execution Time: 1976.733 ms
    181 
    182 ```
     181}}}
    183182
    184183Бидејќи `song_streams` нема индекс на `user_id`, за прашалник 2А потребно е секвенцијално скенирање за да се најдат стримовите за еден корисник. Затоа, додаваме индекс на таа колона:
    … …  
    205204}}}
    206205
    207 2Б остана непроменето - бидејќи прашалникот треба да направи комплексна агрегација на големи табели нема баш некој конкретен индекс што може да ги подобри перформансите. Доколку овој прашалник се извршува често во апликацијата, јасно е дека тоа може да доведе до проблеми. Ова можеме да го решиме на повеќе начини: со менување на погледот во материјализиран поглед, со кеширање и слично. Првиот пристап (материјализирани погледи) како решение ќе го погледнеме понатаму во оптимизацијата на други погледи, а конкретно за овој поглед ќе одиме со вториот пристап, поточно со кеширање во самиот апликациски код.
     2062Б остана непроменето - бидејќи прашалникот треба да направи комплексна агрегација на големи табели нема баш некој конкретен индекс што може да ги подобри перформансите. Доколку овој прашалник се извршува често во апликацијата, јасно е дека тоа може да доведе до проблеми. Ова можеме да го решиме на повеќе начини: со менување на погледот во материјализиран поглед, со кеширање и слично. Првиот пристап (материјализирани погледи) како решение ќе го погледнеме понатаму во оптимизацијата на други погледи, а конкретно за овој поглед ќе одиме со вториот пристап, поточно со кеширање кое ќе биде имплементирано во самиот апликациски код.
    208207
    209208
    … …  
    211210=== 3. Анализа на поглед 3, рангирање на песни по нивните просечни оценки и бројот на вкупни оценки, соодветно ===
    212211
    213 
     212Прашалниците кои ќе ги тестираме се следните:
     213
     214{{{
     215-- 3A: просечна оценка за една песна
     216SELECT * FROM song_average_grade WHERE song_id = 1;
     217
     218-- 3B: топ 10 најдобро оценети песни
     219SELECT * FROM song_average_grade ORDER BY avg_grade DESC, num_reviews DESC LIMIT 10;
     220}}}
     221
     222==== Време за извршување без индекси
     223
     224**3A — 705.179 ms**
     225
     226{{{
     227 Nested Loop  (cost=1000.85..136433.74 rows=1 width=86) (actual time=645.720..658.140 rows=1 loops=1)
     228   ->  Nested Loop  (cost=1000.43..136425.30 rows=1 width=69) (actual time=645.601..658.019 rows=1 loops=1)
     229         ->  Index Scan using songs_pkey on songs s  (cost=0.43..8.45 rows=1 width=29) (actual time=0.191..0.196 rows=1 loops=1)
     230               Index Cond: (id = 1)
     231         ->  Finalize GroupAggregate  (cost=1000.00..136416.84 rows=1 width=48) (actual time=645.391..657.803 rows=1 loops=1)
     232               ->  Gather  (cost=1000.00..136416.81 rows=2 width=48) (actual time=645.091..657.742 rows=3 loops=1)
     233                     Workers Planned: 2
     234                     Workers Launched: 2
     235                     ->  Partial GroupAggregate  (cost=0.00..135416.61 rows=1 width=48) (actual time=585.860..585.861 rows=1 loops=3)
     236                           ->  Parallel Seq Scan on reviews r  (cost=0.00..135416.59 rows=2 width=12) (actual time=313.794..585.721 rows=2 loops=3)
     237                                 Filter: (song_id = 1)
     238                                 Rows Removed by Filter: 3333331
     239   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=0.077..0.078 rows=1 loops=1)
     240         Index Cond: (id = s.owner_artist_id)
     241 Planning Time: 3.643 ms
     242 Execution Time: 705.179 ms
     243}}}
     244
     245**3B — 20559.318 ms**
     246
     247{{{
     248 Limit  (cost=1696797.46..1696797.48 rows=10 width=86) (actual time=20499.331..20499.476 rows=10 loops=1)
     249   ->  Sort  (cost=1696797.46..1701259.58 rows=1784848 width=86) (actual time=20067.449..20067.593 rows=10 loops=1)
     250         Sort Key: ag.avg_grade DESC, ag.num_reviews DESC
     251         Sort Method: top-N heapsort  Memory: 27kB
     252         ->  Hash Join  (cost=1016994.20..1658227.53 rows=1784848 width=86) (actual time=5289.583..19373.444 rows=1939589 loops=1)
     253               Hash Cond: (s.owner_artist_id = u.id)
     254               ->  Hash Join  (cost=962631.20..1550509.28 rows=1784848 width=69) (actual time=4610.737..16796.605 rows=1939589 loops=1)
     255                     Hash Cond: (ag.song_id = s.id)
     256                     ->  Subquery Scan on ag  (cost=868957.86..1407435.71 rows=1784848 width=48) (actual time=3543.588..8643.418 rows=1939589 loops=1)
     257                           ->  Finalize GroupAggregate  (cost=868957.86..1389587.23 rows=1784848 width=48) (actual time=3543.580..8291.260 rows=1939589 loops=1)
     258                                 Group Key: r.song_id
     259                                 ->  Gather Merge  (cost=868957.86..1340503.91 rows=3569696 width=48) (actual time=3543.530..6378.424 rows=4760010 loops=1)
     260                                       Workers Planned: 2
     261                                       Workers Launched: 2
     262                                       ->  Partial GroupAggregate  (cost=867957.83..927472.39 rows=1784848 width=48) (actual time=3194.440..4716.258 rows=1586670 loops=3)
     263                                             Group Key: r.song_id
     264                                             ->  Sort  (cost=867957.83..878374.35 rows=4166608 width=12) (actual time=3194.358..3822.645 rows=3333333 loops=3)
     265                                                   Sort Key: r.song_id
     266                                                   Sort Method: external merge  Disk: 86264kB
     267                                                   ->  Parallel Seq Scan on reviews r  (cost=0.00..125000.08 rows=4166608 width=12) (actual time=193.403..737.774 rows=3333333 loops=3)
     268                     ->  Hash  (cost=55943.04..55943.04 rows=1951304 width=29) (actual time=1045.998..1045.999 rows=1951232 loops=1)
     269                           ->  Seq Scan on songs s  (cost=0.00..55943.04 rows=1951304 width=29) (actual time=91.198..412.261 rows=1951232 loops=1)
     270               ->  Hash  (cost=35027.00..35027.00 rows=1000000 width=25) (actual time=574.936..574.937 rows=1000000 loops=1)
     271                     ->  Seq Scan on users u  (cost=0.00..35027.00 rows=1000000 width=25) (actual time=63.431..244.709 rows=1000000 loops=1)
     272 Planning Time: 2.253 ms
     273 Execution Time: 20559.318 ms
     274}}}
     275
     276
     277Во 3А имаме секвенцијално скенирање на `reviews` табелата за да се земат `(song_id, grade)`. Прво пробавме да додадеме индекс на `reviews(song_id)`, но планерот го игнорираше индексот бидејќи секако ќе беше потребно скенирање на табелата за да се земе `grade` колоната. Затоа можеме да воведеме сложен индекс кој ќе ги содржи сите потребни колони и ќе му овозможи на планерот да користи Index Only Scan.
     278
     279{{{
     280CREATE INDEX idx_reviews_song_id_grade ON reviews(song_id, grade);
     281}}}
     282
     283**3A — 0.630 ms** (was 705.179 ms)
     284
     285```
     286 Nested Loop  (cost=1.29..41.49 rows=1 width=86) (actual time=0.260..0.263 rows=1 loops=1)
     287   ->  Nested Loop  (cost=0.86..33.05 rows=1 width=69) (actual time=0.215..0.218 rows=1 loops=1)
     288         ->  Index Scan using songs_pkey on songs s  (cost=0.43..8.45 rows=1 width=29) (actual time=0.052..0.053 rows=1 loops=1)
     289               Index Cond: (id = 1)
     290         ->  GroupAggregate  (cost=0.43..24.58 rows=1 width=48) (actual time=0.159..0.159 rows=1 loops=1)
     291               ->  Index Only Scan using idx_reviews_song_id_grade on reviews r  (cost=0.43..24.54 rows=6 width=12) (actual time=0.103..0.145 rows=6 loops=1)
     292                     Index Cond: (song_id = 1)
     293                     Heap Fetches: 5
     294   ->  Index Scan using users_pkey on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=0.041..0.042 rows=1 loops=1)
     295         Index Cond: (id = 1)
     296 Planning Time: 3.016 ms
     297 Execution Time: 0.630 ms
     298```
     299
     300Перформансите на 3Б малку се подобрија (од ~20 секунди на ~14 секунди), но јасно е дека тоа е многу бавно.
     301
     302Поради таа причина обичниот поглед во овој случај ќе го замениме со материјализиран поглед.
     303
     304==== Време за извршување на прашалници по додавање на материјализиран поглед
     305
     306**3A - 0.19 ms**
     307
     308{{{
     309 Index Scan using idx_sag_mv_song_id on song_average_grade_mv  (cost=0.43..8.45 rows=1 width=62) (actual time=0.074..0.075 rows=1 loops=1)
     310   Index Cond: (song_id = 1)
     311 Planning Time: 1.116 ms
     312 Execution Time: 0.189 ms
     313}}}
     314
     315**3B - 0.25 ms**
     316
     317{{{
     318 Limit  (cost=0.43..1.23 rows=10 width=718) (actual time=0.081..0.209 rows=10 loops=1)
     319   ->  Index Scan using idx_sag_mv_avg_grade on song_average_grade_mv  (cost=0.43..155226.26 rows=1939589 width=718) (actual time=0.080..0.205 rows=10 loops=1)
     320 Planning Time: 0.936 ms
     321 Execution Time: 0.247 ms
     322}}}
     323
     324Со материјализирани погледи добиваме <1ms за читање, со тоа што свесно дозволуваме во одредени моменти на корисниците да им се прикажуваат податоци кои може да не се најновите податоци како што беше случајот кај обичните погледи.
     325
     326Исто така вреди да се напомене дека во апликацискиот код ќе треба да имплементираме логика за повремено ажурирање на овие погледи, користејќи `REFRESH MATERIALIZED VIEW`, и дека еден ваков refresh трае ~45 секунди.
    214327
    215328=== 4. Анализа на поглед 4, рангирање на артистите по слушања (популарност) во изминатите 30 дена ===