Changes between Version 1 and Version 2 of QueryOptimization


Ignore:
Timestamp:
05/26/26 22:01:45 (17 hours ago)
Author:
231088
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v1 v2  
    11= Оптимизација на прашалници и погледи =
    22
    3 Во оваа фаза ќе ги анализираме погледите дефинирани во [[DatabaseCreation|Фаза 2]] преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
    4 
    5 === 1. Анализа на поглед 1, добивање на бројот на следбеници и бројот на профили кои ги следи даден корисник ===
    6 
    7 Прашалниците кои ќе ги тестираме се следните:
    8 {{{
    9 -- 1A: информации за конкретен корисник
    10 SELECT * FROM user_follow_info WHERE user_id = 5;
    11 
    12 -- 1B: топ 10 најследени корисници
    13 SELECT * 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 {{{
    69 CREATE 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 }}}
    124 
    125 === 2. Анализа на поглед 2, најактивни корисници на платформата според бројот на слушања во изминатите 30 дена ===
    126 
    127 Прашалниците кои ќе ги тестираме се следните:
    128 {{{
    129 -- 2A: активност на еден корисник
    130 SELECT * FROM user_activity_last_30_days WHERE user_id = 100376;
    131 
    132 -- 2B: топ 10 најактивни корисници
    133 SELECT * 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 {{{
    159 Limit  (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)
    175 Planning Time: 0.476 ms
    176 JIT:
    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"
    180 Execution Time: 1976.733 ms
    181 }}}
    182 
    183 Бидејќи `song_streams` нема индекс на `user_id`, за прашалник 2А потребно е секвенцијално скенирање за да се најдат стримовите за еден корисник. Затоа, додаваме индекс на таа колона:
    184 
    185 {{{
    186 CREATE INDEX idx_song_streams_user_id ON song_streams(user_id);
    187 }}}
    188 
    189 ==== Време за извршување по додавање на индекс
    190 
    191 **2A — 0.453 ms** (was 389.404 ms)
    192 
    193 {{{
    194  Nested Loop  (cost=0.86..45.14 rows=1 width=33) (actual time=0.295..0.297 rows=1 loops=1)
    195    ->  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)
    196          Index Cond: (id = 100376)
    197    ->  GroupAggregate  (cost=0.43..36.68 rows=1 width=16) (actual time=0.220..0.221 rows=1 loops=1)
    198          ->  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)
    199                Index Cond: (user_id = 100376)
    200                Filter: ((streamed_at <= now()) AND (streamed_at >= (CURRENT_DATE - 30)))
    201                Rows Removed by Filter: 8
    202  Planning Time: 1.843 ms
    203  Execution Time: 0.453 ms
    204 }}}
    205 
    206 2Б остана непроменето - бидејќи прашалникот треба да направи комплексна агрегација на големи табели нема баш некој конкретен индекс што може да ги подобри перформансите. Доколку овој прашалник се извршува често во апликацијата, јасно е дека тоа може да доведе до проблеми. Ова можеме да го решиме на повеќе начини: со менување на погледот во материјализиран поглед, со кеширање и слично. Првиот пристап (материјализирани погледи) како решение ќе го погледнеме понатаму во оптимизацијата на други погледи, а конкретно за овој поглед ќе одиме со вториот пристап, поточно со кеширање кое ќе биде имплементирано во самиот апликациски код.
    207 
    208 
    209 
    210 === 3. Анализа на поглед 3, рангирање на песни по нивните просечни оценки и бројот на вкупни оценки, соодветно ===
    211 
    212 Прашалниците кои ќе ги тестираме се следните:
    213 
    214 {{{
    215 -- 3A: просечна оценка за една песна
    216 SELECT * FROM song_average_grade WHERE song_id = 1;
    217 
    218 -- 3B: топ 10 најдобро оценети песни
    219 SELECT * 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 {{{
    280 CREATE 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 секунди.
    327 
    328 === 4. Анализа на поглед 4, број на слушања (популарност) на артисте за изминатите 30 дена
    329 
    330 Прашалник кој ќе го тестираме:
    331 
    332 {{{
    333 SELECT * FROM artist_popularity_last_30_days WHERE artist_display_name='Rush';
    334 }}}
    335 
    336 ==== Време на извршување без индекси:
    337 
    338 **5295.544 ms**
    339 
    340 {{{
    341  Subquery Scan on artist_popularity_last_30_days  (cost=258658.87..261658.85 rows=500 width=60) (actual time=5248.277..5288.441 rows=41 loops=1)
    342    Filter: ((artist_popularity_last_30_days.artist_display_name)::text = 'Rush'::text)
    343    Rows Removed by Filter: 99959
    344    ->  WindowAgg  (cost=258658.87..260408.85 rows=100000 width=60) (actual time=5247.481..5281.467 rows=100000 loops=1)
    345          ->  Sort  (cost=258658.85..258908.85 rows=100000 width=52) (actual time=5247.444..5253.777 rows=100000 loops=1)
    346                Sort Key: artist_listens.total_listens DESC
    347                Sort Method: quicksort  Memory: 7706kB
    348                ->  Subquery Scan on artist_listens  (cost=249104.03..250354.03 rows=100000 width=52) (actual time=5163.853..5210.492 rows=100000 loops=1)
    349                      ->  HashAggregate  (cost=249104.03..250354.03 rows=100000 width=52) (actual time=5163.847..5200.274 rows=100000 loops=1)
    350                            Group Key: a.id
    351                            Batches: 1  Memory Usage: 22545kB
    352                            ->  Hash Left Join  (cost=173161.35..239347.87 rows=1951232 width=28) (actual time=3172.252..4602.821 rows=1953805 loops=1)
    353                                  Hash Cond: (s.id = sc.song_id)
    354                                  ->  Hash Right Join  (cost=3618.00..64682.53 rows=1951232 width=28) (actual time=123.141..1016.733 rows=1953805 loops=1)
    355                                        Hash Cond: (s.owner_artist_id = a.id)
    356                                        ->  Seq Scan on songs s  (cost=0.00..55942.32 rows=1951232 width=16) (actual time=70.738..304.609 rows=1951232 loops=1)
    357                                        ->  Hash  (cost=2368.00..2368.00 rows=100000 width=20) (actual time=52.166..52.167 rows=100000 loops=1)
    358                                              Buckets: 131072  Batches: 1  Memory Usage: 6157kB
    359                                              ->  Seq Scan on artists a  (cost=0.00..2368.00 rows=100000 width=20) (actual time=21.265..34.097 rows=100000 loops=1)
    360                                  ->  Hash  (cost=167349.12..167349.12 rows=175538 width=16) (actual time=3048.716..3048.831 rows=304092 loops=1)
    361                                        Buckets: 524288 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 18351kB
    362                                        ->  Subquery Scan on sc  (cost=163838.36..167349.12 rows=175538 width=16) (actual time=2861.695..2967.031 rows=304092 loops=1)
    363                                              ->  Finalize HashAggregate  (cost=163838.36..165593.74 rows=175538 width=16) (actual time=2861.688..2940.091 rows=304092 loops=1)
    364                                                    Group Key: ss.song_id
    365                                                    Batches: 1  Memory Usage: 36881kB
    366                                                    ->  Gather  (cost=88357.02..160327.60 rows=702152 width=16) (actual time=2650.692..2751.352 rows=304092 loops=1)
    367                                                          Workers Planned: 4
    368                                                          Workers Launched: 0
    369                                                          ->  Partial HashAggregate  (cost=87357.02..89112.40 rows=175538 width=16) (actual time=2650.252..2729.313 rows=304092 loops=1)
    370                                                                Group Key: ss.song_id
    371                                                                Batches: 1  Memory Usage: 36881kB
    372                                                                ->  Parallel Seq Scan on song_streams ss  (cost=0.00..86108.61 rows=249682 width=8) (actual time=0.039..2307.530 rows=996439 loops=1)
    373                                                                      Filter: (streamed_at >= (CURRENT_TIMESTAMP - '30 days'::interval))
    374                                                                      Rows Removed by Filter: 5779244
    375  Planning Time: 0.601 ms
    376  JIT:
    377    Functions: 38
    378  "  Options: Inlining false, Optimization false, Expressions true, Deforming true"
    379  "  Timing: Generation 2.191 ms (Deform 0.875 ms), Inlining 0.000 ms, Optimization 0.746 ms, Emission 20.211 ms, Total 23.149 ms"
    380  Execution Time: 5295.544 ms
    381 }}}
    382 
    383 Најбавните делови се секвенцијално скенирање на табелите {{{song_streams}}} и {{{songs}}}, што можеме да го оптимизираме со индекс:
    384 
    385 {{{
    386 CREATE INDEX idx_song_streams_streamed_at_song_id ON song_streams(streamed_at, song_id);
    387 CREATE INDEX idx_songs_owner_artist_id ON songs(owner_artist_id);
    388 }}}
    389 
    390 ==== Време на извршување со индекси:
    391 
    392 **2923.180 ms**
    393 
    394 {{{
    395 WindowAgg  (cost=132657.36..134407.34 rows=100000 width=60) (actual time=2878.456..2912.811 rows=100000 loops=1)
    396   ->  Sort  (cost=132657.34..132907.34 rows=100000 width=52) (actual time=2878.421..2884.685 rows=100000 loops=1)
    397         Sort Key: artist_listens.total_listens DESC
    398         Sort Method: quicksort  Memory: 7706kB
    399         ->  Subquery Scan on artist_listens  (cost=123102.52..124352.52 rows=100000 width=52) (actual time=2795.069..2840.462 rows=100000 loops=1)
    400               ->  HashAggregate  (cost=123102.52..124352.52 rows=100000 width=52) (actual time=2795.063..2830.225 rows=100000 loops=1)
    401                     Group Key: a.id
    402                     Batches: 1  Memory Usage: 22545kB
    403                     ->  Hash Left Join  (cost=47159.84..113346.36 rows=1951232 width=28) (actual time=826.776..2239.576 rows=1953805 loops=1)
    404                           Hash Cond: (s.id = sc.song_id)
    405                           ->  Hash Right Join  (cost=3618.00..64682.53 rows=1951232 width=28) (actual time=118.783..1004.286 rows=1953805 loops=1)
    406                                 Hash Cond: (s.owner_artist_id = a.id)
    407                                 ->  Seq Scan on songs s  (cost=0.00..55942.32 rows=1951232 width=16) (actual time=69.772..303.956 rows=1951232 loops=1)
    408                                 ->  Hash  (cost=2368.00..2368.00 rows=100000 width=20) (actual time=48.780..48.781 rows=100000 loops=1)
    409                                       Buckets: 131072  Batches: 1  Memory Usage: 6157kB
    410                                       ->  Seq Scan on artists a  (cost=0.00..2368.00 rows=100000 width=20) (actual time=17.634..30.840 rows=100000 loops=1)
    411                           ->  Hash  (cost=41347.61..41347.61 rows=175538 width=16) (actual time=707.572..707.574 rows=304102 loops=1)
    412                                 Buckets: 524288 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 18351kB
    413                                 ->  Subquery Scan on sc  (cost=37836.85..41347.61 rows=175538 width=16) (actual time=533.395..631.097 rows=304102 loops=1)
    414                                       ->  HashAggregate  (cost=37836.85..39592.23 rows=175538 width=16) (actual time=533.387..597.876 rows=304102 loops=1)
    415                                             Group Key: ss.song_id
    416                                             Batches: 1  Memory Usage: 36881kB
    417                                             ->  Index Only Scan using idx_song_streams_streamed_at_song_id on song_streams ss  (cost=0.44..32842.98 rows=998774 width=8) (actual time=0.057..245.680 rows=996484 loops=1)
    418                                                   Index Cond: (streamed_at >= (CURRENT_TIMESTAMP - '30 days'::interval))
    419                                                   Heap Fetches: 0
    420 Planning Time: 0.573 ms
    421 JIT:
    422   Functions: 32
    423 "  Options: Inlining false, Optimization false, Expressions true, Deforming true"
    424 "  Timing: Generation 2.147 ms (Deform 0.709 ms), Inlining 0.000 ms, Optimization 0.652 ms, Emission 16.640 ms, Total 19.439 ms"
    425 Execution Time: 2923.180 ms
    426 }}}
    427 
    428 Сега планерот го користи креираниот индекс за табелата {{{song_streams}}}, но сепак табелата {{{songs}}} треба секвенцијално да се скенира за да се пресмета статистиката за артистите. Дополнителна оптимизација правиме со материјализиран поглед:
    429 
    430 {{{
    431 CREATE MATERIALIZED VIEW artist_popularity_last_30_days_mv AS
    432 WITH streams_count AS (
    433     SELECT ss.song_id, COUNT(*) AS cnt
    434     FROM song_streams ss
    435     WHERE ss.streamed_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
    436     GROUP BY ss.song_id
    437 ),
    438 artist_listens AS (
    439     SELECT
    440         a.id AS artist_id,
    441         a.display_name AS artist_display_name,
    442         COALESCE(SUM(sc.cnt), 0) AS total_listens
    443     FROM artists a
    444     LEFT JOIN songs s ON s.owner_artist_id = a.id
    445     LEFT JOIN streams_count sc ON sc.song_id = s.id
    446     GROUP BY a.id, a.display_name
    447 )
    448 SELECT
    449     ROW_NUMBER() OVER (ORDER BY total_listens DESC) AS rank,
    450     artist_id,
    451     artist_display_name,
    452     total_listens
    453 FROM artist_listens;
    454 
    455 }}}
    456 
    457 ==== Време за извршување на прашалникот по додавање на материјализиран поглед
    458 
    459 {{{
    460 Seq Scan on artist_popularity_last_30_days_mv  (cost=0.00..2082.00 rows=2 width=31) (actual time=0.210..9.704 rows=41 loops=1)
    461   Filter: ((artist_display_name)::text = 'Rush'::text)
    462   Rows Removed by Filter: 99959
    463 Planning Time: 0.094 ms
    464 Execution Time: 9.731 ms
    465 }}}
    466 
    467 Со материјализиран поглед добиваме <10 ms за читање.
    468 
    469 === 5. Анализа на поглед 5, број на слушања (популарност) на песните за изминатите 30 дена
    470 
    471 Прашалник кој ќе го тестираме:
    472 
    473 {{{
    474 SELECT * FROM most_popular_songs_last_30_days WHERE rank=15;
    475 }}}
    476 
    477 ==== Време за извршување со креираниот индекс {{{idx_song_streams_streamed_at_song_id}}}
    478 
    479 **1682.017 ms**
    480 
    481 {{{
    482 Subquery Scan on most_popular_songs_last_30_days  (cost=96713.63..122558.33 rows=878 width=96) (actual time=1471.660..1677.911 rows=1 loops=1)
    483   Filter: (most_popular_songs_last_30_days.rank = 17)
    484   Rows Removed by Filter: 16
    485   ->  WindowAgg  (cost=96713.63..120364.14 rows=175535 width=96) (actual time=1471.640..1677.902 rows=17 loops=1)
    486         Run Condition: (row_number() OVER (?) <= 17)
    487         ->  Gather Merge  (cost=96713.50..117731.12 rows=175535 width=88) (actual time=1471.591..1677.843 rows=18 loops=1)
    488               Workers Planned: 4
    489               Workers Launched: 4
    490               ->  Sort  (cost=95713.44..95823.15 rows=43884 width=88) (actual time=1433.450..1433.565 rows=564 loops=5)
    491                     Sort Key: sc.total_streams DESC
    492                     Sort Method: quicksort  Memory: 9470kB
    493                     Worker 0:  Sort Method: quicksort  Memory: 9145kB
    494                     Worker 1:  Sort Method: quicksort  Memory: 2511kB
    495                     Worker 2:  Sort Method: quicksort  Memory: 2431kB
    496                     Worker 3:  Sort Method: quicksort  Memory: 9520kB
    497                     ->  Parallel Hash Join  (cost=49328.85..92329.67 rows=43884 width=88) (actual time=1114.045..1407.397 rows=60789 loops=5)
    498                           Hash Cond: (s.owner_artist_id = a.id)
    499                           ->  Hash Left Join  (cost=46637.31..89522.94 rows=43884 width=84) (actual time=1076.395..1343.191 rows=60789 loops=5)
    500                                 Hash Cond: (s.published_by_label_id = l.id)
    501                                 ->  Hash Join  (cost=43501.50..86090.08 rows=43884 width=48) (actual time=1071.308..1327.862 rows=60789 loops=5)
    502                                       Hash Cond: (s.id = sc.song_id)
    503                                       ->  Parallel Seq Scan on songs s  (cost=0.00..41308.08 rows=487808 width=40) (actual time=0.092..135.322 rows=390246 loops=5)
    504                                       ->  Hash  (cost=41307.31..41307.31 rows=175535 width=16) (actual time=1069.687..1069.689 rows=303945 loops=5)
    505                                             Buckets: 524288 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 18344kB
    506                                             ->  Subquery Scan on sc  (cost=37796.61..41307.31 rows=175535 width=16) (actual time=837.380..966.924 rows=303945 loops=5)
    507                                                   ->  HashAggregate  (cost=37796.61..39551.96 rows=175535 width=16) (actual time=837.372..937.795 rows=303945 loops=5)
    508                                                         Group Key: song_streams.song_id
    509                                                         Batches: 1  Memory Usage: 36881kB
    510                                                         Worker 0:  Batches: 1  Memory Usage: 36881kB
    511                                                         Worker 1:  Batches: 1  Memory Usage: 36881kB
    512                                                         Worker 2:  Batches: 1  Memory Usage: 36881kB
    513                                                         Worker 3:  Batches: 1  Memory Usage: 36881kB
    514                                                         ->  Index Only Scan using idx_song_streams_streamed_at_song_id on song_streams  (cost=0.44..32809.02 rows=997519 width=8) (actual time=0.137..407.430 rows=995226 loops=5)
    515                                                               Index Cond: (streamed_at >= (CURRENT_TIMESTAMP - '30 days'::interval))
    516                                                               Heap Fetches: 95
    517                                 ->  Hash  (cost=3131.12..3131.12 rows=375 width=48) (actual time=4.957..4.961 rows=375 loops=5)
    518                                       Buckets: 1024  Batches: 1  Memory Usage: 38kB
    519                                       ->  Nested Loop Left Join  (cost=13.86..3131.12 rows=375 width=48) (actual time=0.389..4.675 rows=375 loops=5)
    520                                             ->  Hash Left Join  (cost=13.44..21.18 rows=375 width=39) (actual time=0.317..0.558 rows=375 loops=5)
    521                                                   Hash Cond: (la.label_id = l.id)
    522                                                   ->  Seq Scan on label_admins la  (cost=0.00..6.75 rows=375 width=24) (actual time=0.068..0.157 rows=375 loops=5)
    523                                                   ->  Hash  (cost=8.75..8.75 rows=375 width=31) (actual time=0.214..0.215 rows=375 loops=5)
    524                                                         Buckets: 1024  Batches: 1  Memory Usage: 33kB
    525                                                         ->  Seq Scan on labels l  (cost=0.00..8.75 rows=375 width=31) (actual time=0.051..0.109 rows=375 loops=5)
    526                                             ->  Index Scan using users_pkey on users u  (cost=0.42..8.29 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=1875)
    527                                                   Index Cond: (id = la.user_id)
    528                           ->  Parallel Hash  (cost=1956.24..1956.24 rows=58824 width=20) (actual time=37.138..37.139 rows=20000 loops=5)
    529                                 Buckets: 131072  Batches: 1  Memory Usage: 6528kB
    530                                 ->  Parallel Seq Scan on artists a  (cost=0.00..1956.24 rows=58824 width=20) (actual time=27.866..30.351 rows=20000 loops=5)
    531 Planning Time: 1.492 ms
    532 JIT:
    533   Functions: 235
    534 "  Options: Inlining false, Optimization false, Expressions true, Deforming true"
    535 "  Timing: Generation 14.075 ms (Deform 6.062 ms), Inlining 0.000 ms, Optimization 5.950 ms, Emission 133.686 ms, Total 153.711 ms"
    536 Execution Time: 1682.017 ms
    537 
    538 }}}
    539 
    540 За дополнителна оптимизација креираме материјализиран поглед:
    541 
    542 {{{
    543 CREATE MATERIALIZED VIEW most_popular_songs_last_30_days_mv AS
    544 WITH stream_counts AS (
    545     SELECT
    546         song_id,
    547         COUNT(*) AS total_streams
    548     FROM song_streams
    549     WHERE streamed_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
    550     GROUP BY song_id
    551 )
    552 SELECT
    553     ROW_NUMBER() OVER (ORDER BY sc.total_streams DESC) AS rank,
    554     s.id AS song_id,
    555     s.title AS song_title,
    556     a.display_name AS artist_display_name,
    557     s.visibility AS song_visibility,
    558     l.name AS label_name,
    559     sc.total_streams
    560 FROM stream_counts sc
    561 JOIN songs s ON s.id = sc.song_id
    562 JOIN artists a ON s.owner_artist_id = a.id
    563 LEFT JOIN labels l ON l.id = s.published_by_label_id;
    564 
    565 }}}
    566 
    567 ==== Време за извршување на прашалникот по додавање на материјализиран поглед
    568 
    569 {{{
    570 Gather  (cost=1000.00..6167.16 rows=1 width=96) (actual time=0.381..671.251 rows=1 loops=1)
    571   Workers Planned: 2
    572   Workers Launched: 2
    573   ->  Parallel Seq Scan on most_popular_songs_last_30_days_mv  (cost=0.00..5167.06 rows=1 width=96) (actual time=417.275..638.156 rows=0 loops=3)
    574         Filter: (rank = 17)
    575         Rows Removed by Filter: 101315
    576 Planning Time: 0.218 ms
    577 Execution Time: 671.277 ms
    578 }}}
    579 
    580 Со материјализиран поглед добиваме <1s време на читање.
    581 
    582 
    583 === 6. Анализа на поглед 6, детален преглед за артистите групирани по издавачка куќа на која припаѓаат
    584 
    585 Го тестираме прашалникот:
    586 
    587 {{{ SELECT * FROM label_artists_info WHERE label_name='Piercing Abyss Records';}}}
    588 
    589 
    590 ==== Време за извршување без индекси
    591 
    592 **3197.076 ms**
    593 
    594 {{{
    595 Subquery Scan on label_artists_info  (cost=48955.19..49185.03 rows=1561 width=51) (actual time=3166.576..3196.926 rows=81 loops=1)
    596   ->  GroupAggregate  (cost=48955.19..49169.42 rows=1561 width=59) (actual time=3166.575..3196.914 rows=81 loops=1)
    597         Group Key: a.id
    598         ->  Gather Merge  (cost=48955.19..49142.10 rows=1561 width=59) (actual time=3166.542..3196.531 rows=1660 loops=1)
    599               Workers Planned: 4
    600               Workers Launched: 4
    601               ->  Sort  (cost=47955.14..47956.11 rows=390 width=59) (actual time=2548.301..2548.327 rows=332 loops=5)
    602 "                    Sort Key: a.id, s.id"
    603                     Sort Method: quicksort  Memory: 68kB
    604                     Worker 0:  Sort Method: quicksort  Memory: 35kB
    605                     Worker 1:  Sort Method: quicksort  Memory: 39kB
    606                     Worker 2:  Sort Method: quicksort  Memory: 44kB
    607                     Worker 3:  Sort Method: quicksort  Memory: 60kB
    608                     ->  Nested Loop Left Join  (cost=3303.17..47938.35 rows=390 width=59) (actual time=254.314..2547.203 rows=332 loops=5)
    609                           ->  Hash Join  (cost=3302.88..47724.69 rows=390 width=59) (actual time=202.108..1249.756 rows=328 loops=5)
    610                                 Hash Cond: (a.id = al.artist_id)
    611                                 ->  Parallel Hash Right Join  (cost=2691.54..45280.17 rows=487808 width=36) (actual time=42.618..1060.686 rows=390761 loops=5)
    612                                       Hash Cond: (s.owner_artist_id = a.id)
    613                                       ->  Parallel Seq Scan on songs s  (cost=0.00..41308.08 rows=487808 width=16) (actual time=0.371..871.469 rows=390246 loops=5)
    614                                       ->  Parallel Hash  (cost=1956.24..1956.24 rows=58824 width=28) (actual time=41.753..41.754 rows=20000 loops=5)
    615                                             Buckets: 131072  Batches: 1  Memory Usage: 7328kB
    616                                             ->  Parallel Seq Scan on artists a  (cost=0.00..1956.24 rows=58824 width=28) (actual time=3.800..28.812 rows=20000 loops=5)
    617                                 ->  Hash  (cost=610.34..610.34 rows=80 width=31) (actual time=152.360..152.362 rows=81 loops=5)
    618                                       Buckets: 1024  Batches: 1  Memory Usage: 14kB
    619                                       ->  Hash Join  (cost=9.70..610.34 rows=80 width=31) (actual time=51.681..152.281 rows=81 loops=5)
    620                                             Hash Cond: (al.label_id = l.id)
    621                                             ->  Seq Scan on artist_labels al  (cost=0.00..521.00 rows=30000 width=16) (actual time=11.763..115.238 rows=30000 loops=5)
    622                                             ->  Hash  (cost=9.69..9.69 rows=1 width=31) (actual time=33.047..33.048 rows=1 loops=5)
    623                                                   Buckets: 1024  Batches: 1  Memory Usage: 9kB
    624                                                   ->  Seq Scan on labels l  (cost=0.00..9.69 rows=1 width=31) (actual time=32.998..33.038 rows=1 loops=5)
    625                                                         Filter: ((name)::text = 'Piercing Abyss Records'::text)
    626                                                         Rows Removed by Filter: 374
    627                           ->  Index Scan using idx_follows_followed_user_id on follows f  (cost=0.29..0.47 rows=8 width=16) (actual time=3.764..3.959 rows=1 loops=1638)
    628                                 Index Cond: (followed_user_id = a.user_id)
    629 Planning Time: 1776.191 ms
    630 Execution Time: 3197.076 ms
    631 }}}
    632 
    633 За да го оптимизираме секвенцијалното скенирање на табелите {{{songs}}} и {{{artist_labels}}}, ги креираме индексите:
    634 
    635 {{{
    636 CREATE INDEX idx_songs_owner_artist_id ON songs(owner_artist_id);
    637 CREATE INDEX idx_artist_labels_label_id_artist_id ON artist_labels(label_id, artist_id);
    638 }}}
    639 
    640 ==== Време за извршување со индекси
    641 
    642 **5.213 ms**
    643 
    644 {{{
    645 Subquery Scan on label_artists_info  (cost=571.17..618.00 rows=1561 width=51) (actual time=4.634..5.127 rows=81 loops=1)
    646   ->  GroupAggregate  (cost=571.17..602.39 rows=1561 width=59) (actual time=4.633..5.116 rows=81 loops=1)
    647         Group Key: a.id
    648         ->  Sort  (cost=571.17..575.07 rows=1561 width=59) (actual time=4.614..4.708 rows=1660 loops=1)
    649 "              Sort Key: a.id, s.id"
    650               Sort Method: quicksort  Memory: 171kB
    651               ->  Nested Loop Left Join  (cost=6.04..488.37 rows=1561 width=59) (actual time=0.125..3.388 rows=1660 loops=1)
    652                     ->  Nested Loop Left Join  (cost=5.62..274.33 rows=80 width=51) (actual time=0.115..0.973 rows=83 loops=1)
    653                           ->  Nested Loop  (cost=5.32..230.51 rows=80 width=51) (actual time=0.105..0.685 rows=81 loops=1)
    654                                 ->  Nested Loop  (cost=4.91..175.24 rows=80 width=31) (actual time=0.092..0.265 rows=81 loops=1)
    655                                       ->  Seq Scan on labels l  (cost=0.00..9.69 rows=1 width=31) (actual time=0.051..0.075 rows=1 loops=1)
    656                                             Filter: ((name)::text = 'Piercing Abyss Records'::text)
    657                                             Rows Removed by Filter: 374
    658                                       ->  Bitmap Heap Scan on artist_labels al  (cost=4.91..164.75 rows=80 width=16) (actual time=0.038..0.174 rows=81 loops=1)
    659                                             Recheck Cond: (l.id = label_id)
    660                                             Heap Blocks: exact=72
    661                                             ->  Bitmap Index Scan on idx_artist_labels_label_id_artist_id  (cost=0.00..4.89 rows=80 width=0) (actual time=0.019..0.019 rows=81 loops=1)
    662                                                   Index Cond: (label_id = l.id)
    663                                 ->  Index Scan using artists_pkey on artists a  (cost=0.42..0.69 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=81)
    664                                       Index Cond: (id = al.artist_id)
    665                           ->  Index Scan using idx_follows_followed_user_id on follows f  (cost=0.29..0.47 rows=8 width=16) (actual time=0.003..0.003 rows=1 loops=81)
    666                                 Index Cond: (followed_user_id = a.user_id)
    667                     ->  Index Scan using idx_songs_owner_artist_id on songs s  (cost=0.43..2.42 rows=26 width=16) (actual time=0.004..0.026 rows=20 loops=83)
    668                           Index Cond: (owner_artist_id = a.id)
    669 Planning Time: 1.229 ms
    670 Execution Time: 5.213 ms
    671 }}}
    672 
    673 Планерот го користи и претходно креираниот индекс {{{idx_follows_followed_user_id}}}. Индексите го забрзаа извршувањето за речиси 100%, па заклучуваме дека нема потреба од дополнителна оптимизација.
    674 
    675 
    676 === 7. Анализа на поглед 7, детални информации за секоја песна
    677 
    678 Прашалник кој го тестираме:
    679 
    680 {{{
    681 SELECT * FROM songs_details WHERE title='Harmony';
    682 }}}
    683 
    684 ==== Време за извршување без индекси
    685 
    686 **93882.201 ms**
    687 
    688 {{{
    689 Gather  (cost=230200.46..235882.48 rows=2211 width=121) (actual time=3427.813..93875.377 rows=1683 loops=1)
     3Во оваа фаза беа анализирани и оптимизирани погледите дефинирани во системот за booking на артисти и бендови. Анализата беше направена со користење на EXPLAIN ANALYZE врз query-и базирани на реални сценарија кои би се користеле во самата апликација.
     4
     5Целта беше да се намали времето на извршување на query-ите преку соодветно индексирање и подобрување на execution plan-от на PostgreSQL.
     6
     7== 1. Анализа и оптимизација на vw_available_bookables ==
     8
     9Погледот {{{vw_available_bookables}}} се користи за пребарување на достапни артисти и бендови според град, жанр и статус на достапност.
     10
     11Прашалниците кои беа тестирани се следните:
     12
     13{{{
     14-- 1.1
     15SELECT *
     16FROM vw_available_bookables
     17WHERE city = 'Skopje'
     18AND status = 'AVAILABLE';
     19
     20-- 1.2
     21SELECT *
     22FROM vw_available_bookables
     23WHERE genre_name = 'Pop'
     24AND status = 'AVAILABLE';
     25
     26-- 1.3
     27SELECT *
     28FROM vw_available_bookables
     29WHERE city = 'Ohrid'
     30AND genre_name = 'Rock'
     31AND status = 'AVAILABLE';
     32}}}
     33
     34=== Време на извршување без индекси ===
     35
     36'''1.1 - 55.127 ms'''
     37
     38{{{
     39Gather  (cost=1037.34..13879.22 rows=5448 width=481) (actual time=8.888..52.020 rows=71472 loops=1)
    69040  Workers Planned: 3
    69141  Workers Launched: 3
    692   ->  Nested Loop Left Join  (cost=229200.46..234661.38 rows=713 width=121) (actual time=3393.680..41801.988 rows=421 loops=4)
    693         ->  Merge Left Join  (cost=229200.03..229252.13 rows=713 width=85) (actual time=3393.619..3396.837 rows=421 loops=4)
    694               Merge Cond: (s.id = pc.song_id)
    695               ->  Sort  (cost=225567.13..225568.91 rows=713 width=77) (actual time=3342.494..3342.739 rows=421 loops=4)
    696                     Sort Key: s.id
    697                     Sort Method: quicksort  Memory: 48kB
    698                     Worker 0:  Sort Method: quicksort  Memory: 44kB
    699                     Worker 1:  Sort Method: quicksort  Memory: 44kB
    700                     Worker 2:  Sort Method: quicksort  Memory: 72kB
    701                     ->  Nested Loop Left Join  (cost=209283.95..225533.34 rows=713 width=77) (actual time=3254.123..3342.167 rows=421 loops=4)
    702                           ->  Parallel Hash Right Join  (cost=209283.53..225211.18 rows=713 width=72) (actual time=3254.061..3337.679 rows=421 loops=4)
    703                                 Hash Cond: (at.song_id = s.id)
    704                                 ->  Parallel Seq Scan on album_tracks at  (cost=0.00..14292.12 rows=435812 width=16) (actual time=0.016..39.432 rows=337754 loops=4)
    705                                 ->  Parallel Hash  (cost=209276.62..209276.62 rows=553 width=64) (actual time=3253.381..3253.392 rows=421 loops=4)
    706                                       Buckets: 4096  Batches: 1  Memory Usage: 192kB
    707                                       ->  Hash Left Join  (cost=164727.43..209276.62 rows=553 width=64) (actual time=3174.557..3252.826 rows=421 loops=4)
    708                                             Hash Cond: (s.id = sc.song_id)
    709                                             ->  Hash Left Join  (cost=909.90..45457.64 rows=553 width=56) (actual time=52.511..130.411 rows=421 loops=4)
    710                                                   Hash Cond: (al.label_id = l.id)
    711                                                   ->  Hash Left Join  (cost=896.46..45442.74 rows=553 width=41) (actual time=52.297..129.994 rows=421 loops=4)
    712                                                         Hash Cond: (a.id = al.artist_id)
    713                                                         ->  Nested Loop Left Join  (cost=0.42..44542.96 rows=553 width=41) (actual time=41.010..118.277 rows=421 loops=4)
    714                                                               ->  Parallel Seq Scan on songs s  (cost=0.00..42527.60 rows=553 width=29) (actual time=40.894..112.485 rows=421 loops=4)
    715                                                                     Filter: ((title)::text = 'Harmony'::text)
    716                                                                     Rows Removed by Filter: 487388
    717                                                               ->  Index Scan using artists_pkey on artists a  (cost=0.42..3.64 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=1683)
    718                                                                     Index Cond: (id = s.owner_artist_id)
    719                                                         ->  Hash  (cost=521.02..521.02 rows=30002 width=16) (actual time=11.075..11.076 rows=30003 loops=4)
    720                                                               Buckets: 32768  Batches: 1  Memory Usage: 1663kB
    721                                                               ->  Seq Scan on artist_labels al  (cost=0.00..521.02 rows=30002 width=16) (actual time=0.053..4.694 rows=30003 loops=4)
    722                                                   ->  Hash  (cost=8.75..8.75 rows=375 width=31) (actual time=0.190..0.191 rows=375 loops=4)
    723                                                         Buckets: 1024  Batches: 1  Memory Usage: 33kB
    724                                                         ->  Seq Scan on labels l  (cost=0.00..8.75 rows=375 width=31) (actual time=0.030..0.089 rows=375 loops=4)
    725                                             ->  Hash  (cost=161618.58..161618.58 rows=175916 width=16) (actual time=3120.501..3120.503 rows=635893 loops=4)
    726                                                   Buckets: 1048576 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 38000kB
    727                                                   ->  Subquery Scan on sc  (cost=158100.26..161618.58 rows=175916 width=16) (actual time=2662.167..2922.708 rows=635893 loops=4)
    728                                                         ->  HashAggregate  (cost=158100.26..159859.42 rows=175916 width=16) (actual time=2662.160..2862.916 rows=635893 loops=4)
    729                                                               Group Key: song_streams.song_id
    730                                                               Batches: 1  Memory Usage: 65553kB
    731                                                               Worker 0:  Batches: 1  Memory Usage: 65553kB
    732                                                               Worker 1:  Batches: 1  Memory Usage: 65553kB
    733                                                               Worker 2:  Batches: 1  Memory Usage: 65553kB
    734                                                               ->  Seq Scan on song_streams  (cost=0.00..124221.84 rows=6775684 width=8) (actual time=0.040..834.432 rows=6775685 loops=4)
    735                           ->  Index Scan using albums_pkey on albums alb  (cost=0.42..0.45 rows=1 width=21) (actual time=0.010..0.010 rows=1 loops=1683)
    736                                 Index Cond: (id = at.album_id)
    737               ->  Sort  (cost=3632.91..3657.15 rows=9698 width=16) (actual time=51.065..52.559 rows=10235 loops=4)
    738                     Sort Key: pc.song_id
    739                     Sort Method: quicksort  Memory: 706kB
    740                     Worker 0:  Sort Method: quicksort  Memory: 706kB
    741                     Worker 1:  Sort Method: quicksort  Memory: 706kB
    742                     Worker 2:  Sort Method: quicksort  Memory: 706kB
    743                     ->  Subquery Scan on pc  (cost=2796.77..2990.73 rows=9698 width=16) (actual time=45.054..47.748 rows=10288 loops=4)
    744                           ->  HashAggregate  (cost=2796.77..2893.75 rows=9698 width=16) (actual time=45.048..46.739 rows=10288 loops=4)
    745                                 Group Key: playlist_tracks.song_id
    746                                 Batches: 1  Memory Usage: 1425kB
    747                                 Worker 0:  Batches: 1  Memory Usage: 1425kB
    748                                 Worker 1:  Batches: 1  Memory Usage: 1425kB
    749                                 Worker 2:  Batches: 1  Memory Usage: 1425kB
    750                                 ->  Seq Scan on playlist_tracks  (cost=0.00..2171.18 rows=125118 width=8) (actual time=0.038..14.065 rows=125118 loops=4)
    751         ->  Index Scan using idx_sag_mv_song_id on song_average_grade_mv sag  (cost=0.43..7.58 rows=1 width=24) (actual time=91.275..91.276 rows=1 loops=1683)
    752               Index Cond: (song_id = s.id)
    753 Planning Time: 2.976 ms
    754 JIT:
    755   Functions: 272
    756 "  Options: Inlining false, Optimization false, Expressions true, Deforming true"
    757 "  Timing: Generation 16.724 ms (Deform 7.604 ms), Inlining 0.000 ms, Optimization 6.390 ms, Emission 157.809 ms, Total 180.923 ms"
    758 Execution Time: 93882.201 ms
    759 }}}
    760 
    761 За оптимизирање на секвенцијалните скенирање на табелите {{{artist_labels}}}, {{{album_tracks}}} и {{{songs}}} ги креираме индексите:
    762 
    763 {{{
    764 CREATE INDEX idx_songs_title
    765 ON songs(title);
    766 
    767 CREATE INDEX idx_album_tracks_song_id
    768 ON album_tracks(song_id);
    769 
    770 CREATE INDEX idx_artist_labels_artist_id
    771 ON artist_labels(artist_id);
    772 }}}
    773 
    774 ==== Време за извршување со индекси
    775 
    776 **3599.404 ms**
    777 
    778 {{{
    779 Gather  (cost=188600.68..198746.59 rows=2211 width=121) (actual time=3136.649..3592.763 rows=1683 loops=1)
    780   Workers Planned: 1
    781   Workers Launched: 1
    782   ->  Nested Loop Left Join  (cost=187600.68..197525.49 rows=1301 width=121) (actual time=3114.199..3121.952 rows=842 loops=2)
    783         ->  Merge Left Join  (cost=187600.25..187655.31 rows=1301 width=85) (actual time=3114.146..3116.186 rows=842 loops=2)
    784               Merge Cond: (s.id = pc.song_id)
    785               ->  Sort  (cost=183967.34..183970.60 rows=1301 width=77) (actual time=3064.947..3065.100 rows=842 loops=2)
    786                     Sort Key: s.id
    787                     Sort Method: quicksort  Memory: 143kB
    788                     Worker 0:  Sort Method: quicksort  Memory: 40kB
    789                     ->  Hash Left Join  (cost=166553.22..183900.05 rows=1301 width=77) (actual time=3047.496..3064.384 rows=842 loops=2)
    790                           Hash Cond: (s.id = sc.song_id)
    791                           ->  Nested Loop Left Join  (cost=2735.67..20079.09 rows=1301 width=69) (actual time=64.333..80.679 rows=842 loops=2)
    792                                 ->  Nested Loop Left Join  (cost=2735.25..19491.26 rows=1301 width=64) (actual time=64.294..76.539 rows=842 loops=2)
    793                                       ->  Hash Left Join  (cost=2734.82..10204.79 rows=1301 width=56) (actual time=64.235..70.862 rows=842 loops=2)
    794                                             Hash Cond: (al.label_id = l.id)
    795                                             ->  Nested Loop Left Join  (cost=2721.39..10187.90 rows=1301 width=41) (actual time=64.055..70.410 rows=842 loops=2)
    796                                                   ->  Parallel Hash Left Join  (cost=2721.10..9762.21 rows=1301 width=41) (actual time=64.004..67.464 rows=842 loops=2)
    797                                                         Hash Cond: (s.owner_artist_id = a.id)
    798                                                         ->  Parallel Bitmap Heap Scan on songs s  (cost=29.56..7067.26 rows=1301 width=29) (actual time=0.629..2.990 rows=842 loops=2)
    799                                                               Recheck Cond: ((title)::text = 'Harmony'::text)
    800                                                               Heap Blocks: exact=1395
    801                                                               ->  Bitmap Index Scan on idx_songs_title  (cost=0.00..29.01 rows=2211 width=0) (actual time=0.323..0.324 rows=1683 loops=1)
    802                                                                     Index Cond: ((title)::text = 'Harmony'::text)
    803                                                         ->  Parallel Hash  (cost=1956.24..1956.24 rows=58824 width=20) (actual time=62.392..62.393 rows=50000 loops=2)
    804                                                               Buckets: 131072  Batches: 1  Memory Usage: 6496kB
    805                                                               ->  Parallel Seq Scan on artists a  (cost=0.00..1956.24 rows=58824 width=20) (actual time=38.029..44.369 rows=50000 loops=2)
    806                                                   ->  Index Scan using idx_artist_labels_artist_id on artist_labels al  (cost=0.29..0.32 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1683)
    807                                                         Index Cond: (artist_id = a.id)
    808                                             ->  Hash  (cost=8.75..8.75 rows=375 width=31) (actual time=0.155..0.155 rows=375 loops=2)
    809                                                   Buckets: 1024  Batches: 1  Memory Usage: 33kB
    810                                                   ->  Seq Scan on labels l  (cost=0.00..8.75 rows=375 width=31) (actual time=0.031..0.079 rows=375 loops=2)
    811                                       ->  Index Scan using idx_album_tracks_song_id on album_tracks at  (cost=0.43..7.13 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1683)
    812                                             Index Cond: (song_id = s.id)
    813                                 ->  Index Scan using albums_pkey on albums alb  (cost=0.42..0.45 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=1683)
    814                                       Index Cond: (id = at.album_id)
    815                           ->  Hash  (cost=161618.60..161618.60 rows=175916 width=16) (actual time=2982.071..2982.073 rows=635893 loops=2)
    816                                 Buckets: 1048576 (originally 262144)  Batches: 1 (originally 1)  Memory Usage: 38000kB
    817                                 ->  Subquery Scan on sc  (cost=158100.27..161618.60 rows=175916 width=16) (actual time=2526.994..2790.788 rows=635893 loops=2)
    818                                       ->  HashAggregate  (cost=158100.27..159859.43 rows=175916 width=16) (actual time=2526.987..2726.430 rows=635893 loops=2)
    819                                             Group Key: song_streams.song_id
    820                                             Batches: 1  Memory Usage: 65553kB
    821                                             Worker 0:  Batches: 1  Memory Usage: 65553kB
    822                                             ->  Seq Scan on song_streams  (cost=0.00..124221.85 rows=6775685 width=8) (actual time=0.049..708.679 rows=6775685 loops=2)
    823               ->  Sort  (cost=3632.91..3657.15 rows=9698 width=16) (actual time=49.155..49.933 rows=10235 loops=2)
    824                     Sort Key: pc.song_id
    825                     Sort Method: quicksort  Memory: 706kB
    826                     Worker 0:  Sort Method: quicksort  Memory: 706kB
    827                     ->  Subquery Scan on pc  (cost=2796.77..2990.73 rows=9698 width=16) (actual time=43.313..46.006 rows=10288 loops=2)
    828                           ->  HashAggregate  (cost=2796.77..2893.75 rows=9698 width=16) (actual time=43.307..44.952 rows=10288 loops=2)
    829                                 Group Key: playlist_tracks.song_id
    830                                 Batches: 1  Memory Usage: 1425kB
    831                                 Worker 0:  Batches: 1  Memory Usage: 1425kB
    832                                 ->  Seq Scan on playlist_tracks  (cost=0.00..2171.18 rows=125118 width=8) (actual time=0.039..13.544 rows=125118 loops=2)
    833         ->  Index Scan using idx_sag_mv_song_id on song_average_grade_mv sag  (cost=0.43..7.58 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=1683)
    834               Index Cond: (song_id = s.id)
    835 Planning Time: 3.163 ms
    836 JIT:
    837   Functions: 132
    838 "  Options: Inlining false, Optimization false, Expressions true, Deforming true"
    839 "  Timing: Generation 7.561 ms (Deform 3.504 ms), Inlining 0.000 ms, Optimization 2.789 ms, Emission 73.213 ms, Total 83.563 ms"
    840 Execution Time: 3599.404 ms
    841 }}}
    842 
    843 Бидејќи сепак имаме секвенцијално скенирање на табелата {{{song_streams}}} поради групирањето на слушања по песна, дополнително оптимизираме со материјализиран погледи:
    844 
    845 {{{
    846 create materialized view song_stream_counts_mv as
    847 select
    848     song_id,
    849     count(*) as streams
    850 from song_streams
    851 group by song_id;
    852 
    853 
    854 create materialized view song_playlist_counts_mv as
    855 select
    856     song_id,
    857     count(*) as saved_in_playlists
    858 from playlist_tracks
    859 group by song_id;
    860 
    861 
    862 create or replace view song_detailed_info_view_mvs as
    863 select
    864     s.title as title,
    865     a.display_name as artist_name,
    866     coalesce(l.name, 'SOLO') as label_name,
    867     coalesce(sc.streams, 0) as streams,
    868     coalesce(alb.title, 'SINGLE') as album_title,
    869     coalesce(pc.saved_in_playlists, 0) as saved_in_playlists,
    870     sag.num_reviews,
    871     ROUND(sag.avg_grade, 2) AS avg_grade
    872 from songs s
    873 left join artists a on a.id = s.owner_artist_id
    874 left join artist_labels al on al.artist_id = a.id
    875 left join labels l on l.id = al.label_id
    876 left join album_tracks at on at.song_id = s.id
    877 left join albums alb on alb.id = at.album_id
    878 left join song_stream_counts_mv sc on sc.song_id = s.id
    879 left join song_playlist_counts_mv pc on pc.song_id = s.id
    880 left join song_average_grade_mv sag on sag.song_id = s.id;
    881 }}}
    882 
    883 
    884 ==== Време за извршување на прашалникот по додавање на материјализирани погледи
    885 
    886 **333.811 ms**
    887 
    888 {{{
    889 Gather  (cost=25188.45..57377.30 rows=2211 width=145) (actual time=113.872..333.525 rows=1683 loops=1)
    890   Workers Planned: 3
    891   Workers Launched: 3
    892   ->  Parallel Hash Right Join  (cost=24188.45..56156.20 rows=713 width=145) (actual time=84.308..207.282 rows=421 loops=4)
    893         Hash Cond: (sag.song_id = s.id)
    894         ->  Parallel Seq Scan on song_average_grade_mv sag  (cost=0.00..29616.74 rows=625674 width=24) (actual time=0.016..50.181 rows=484897 loops=4)
    895         ->  Parallel Hash  (cost=24176.94..24176.94 rows=921 width=85) (actual time=83.322..83.338 rows=421 loops=4)
    896               Buckets: 4096  Batches: 1  Memory Usage: 256kB
    897               ->  Hash Left Join  (cost=10077.11..24176.94 rows=921 width=85) (actual time=20.990..64.447 rows=421 loops=4)
    898                     Hash Cond: (s.id = pc.song_id)
    899                     ->  Nested Loop Left Join  (cost=9789.63..23885.95 rows=921 width=77) (actual time=17.300..60.507 rows=421 loops=4)
    900                           ->  Nested Loop Left Join  (cost=9789.21..23469.82 rows=921 width=72) (actual time=17.283..57.817 rows=421 loops=4)
    901                                 ->  Hash Left Join  (cost=9788.78..16895.77 rows=921 width=64) (actual time=17.245..53.588 rows=421 loops=4)
    902                                       Hash Cond: (al.label_id = l.id)
    903                                       ->  Nested Loop Left Join  (cost=9775.35..16879.89 rows=921 width=49) (actual time=17.040..53.223 rows=421 loops=4)
    904                                             ->  Parallel Hash Left Join  (cost=9775.06..16578.54 rows=921 width=49) (actual time=16.984..51.378 rows=421 loops=4)
    905                                                   Hash Cond: (s.owner_artist_id = a.id)
    906                                                   ->  Parallel Hash Right Join  (cost=7083.52..13884.59 rows=921 width=37) (actual time=4.483..38.155 rows=421 loops=4)
    907                                                         Hash Cond: (sc.song_id = s.id)
    908                                                         ->  Parallel Seq Scan on song_stream_counts_mv sc  (cost=0.00..6105.55 rows=264955 width=16) (actual time=0.013..14.911 rows=158973 loops=4)
    909                                                         ->  Parallel Hash  (cost=7067.26..7067.26 rows=1301 width=29) (actual time=4.275..4.276 rows=421 loops=4)
    910                                                               Buckets: 4096  Batches: 1  Memory Usage: 160kB
    911                                                               ->  Parallel Bitmap Heap Scan on songs s  (cost=29.56..7067.26 rows=1301 width=29) (actual time=0.718..3.921 rows=421 loops=4)
    912                                                                     Recheck Cond: ((title)::text = 'Harmony'::text)
    913                                                                     Heap Blocks: exact=455
    914                                                                     ->  Bitmap Index Scan on idx_songs_title  (cost=0.00..29.01 rows=2211 width=0) (actual time=0.386..0.386 rows=1683 loops=1)
    915                                                                           Index Cond: ((title)::text = 'Harmony'::text)
    916                                                   ->  Parallel Hash  (cost=1956.24..1956.24 rows=58824 width=20) (actual time=12.017..12.018 rows=25000 loops=4)
    917                                                         Buckets: 131072  Batches: 1  Memory Usage: 6528kB
    918                                                         ->  Parallel Seq Scan on artists a  (cost=0.00..1956.24 rows=58824 width=20) (actual time=0.166..3.968 rows=25000 loops=4)
    919                                             ->  Index Scan using idx_artist_labels_artist_id on artist_labels al  (cost=0.29..0.32 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1683)
    920                                                   Index Cond: (artist_id = a.id)
    921                                       ->  Hash  (cost=8.75..8.75 rows=375 width=31) (actual time=0.179..0.180 rows=375 loops=4)
    922                                             Buckets: 1024  Batches: 1  Memory Usage: 33kB
    923                                             ->  Seq Scan on labels l  (cost=0.00..8.75 rows=375 width=31) (actual time=0.030..0.078 rows=375 loops=4)
    924                                 ->  Index Scan using idx_album_tracks_song_id on album_tracks at  (cost=0.43..7.13 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1683)
    925                                       Index Cond: (song_id = s.id)
    926                           ->  Index Scan using albums_pkey on albums alb  (cost=0.42..0.45 rows=1 width=21) (actual time=0.006..0.006 rows=1 loops=1683)
    927                                 Index Cond: (id = at.album_id)
    928                     ->  Hash  (cost=158.88..158.88 rows=10288 width=16) (actual time=3.568..3.568 rows=10288 loops=4)
    929                           Buckets: 16384  Batches: 1  Memory Usage: 611kB
    930                           ->  Seq Scan on song_playlist_counts_mv pc  (cost=0.00..158.88 rows=10288 width=16) (actual time=0.022..1.349 rows=10288 loops=4)
    931 Planning Time: 135.345 ms
    932 Execution Time: 333.811 ms
    933 }}}
    934 
    935 
    936 === 8. Анализа на поглед 8, историја на слушање песни од корисниците
    937 
    938 Прашалник кој го тестираме:
    939 
    940 {{{
    941 SELECT *
    942 FROM streams_history
    943 WHERE username='adriana_klein_511'
    944 ORDER BY streamed_at DESC;
    945 }}}
    946 
    947 ==== Време за извршување без дополнителни индекси
    948 
    949 **0.404 ms**
    950 
    951 {{{
    952 Sort  (cost=52.13..52.15 rows=7 width=58) (actual time=0.333..0.335 rows=15 loops=1)
    953   Sort Key: ss.streamed_at DESC
    954   Sort Method: quicksort  Memory: 26kB
    955   ->  Nested Loop  (cost=1.72..52.03 rows=7 width=58) (actual time=0.072..0.316 rows=15 loops=1)
    956         ->  Nested Loop  (cost=1.28..48.40 rows=7 width=62) (actual time=0.061..0.195 rows=15 loops=1)
    957               ->  Nested Loop  (cost=0.86..45.09 rows=7 width=49) (actual time=0.049..0.094 rows=15 loops=1)
    958                     ->  Index Scan using users_username_key on users u  (cost=0.42..8.44 rows=1 width=25) (actual time=0.029..0.030 rows=1 loops=1)
    959                           Index Cond: ((username)::text = 'adriana_klein_511'::text)
    960                     ->  Index Scan using idx_song_streams_user_id on song_streams ss  (cost=0.43..36.57 rows=8 width=32) (actual time=0.014..0.056 rows=15 loops=1)
    961                           Index Cond: (user_id = u.id)
    962               ->  Index Scan using songs_pkey on songs s  (cost=0.43..0.47 rows=1 width=21) (actual time=0.006..0.006 rows=1 loops=15)
    963                     Index Cond: (id = ss.song_id)
    964         ->  Index Scan using playback_sessions_pkey on playback_sessions ps  (cost=0.43..0.52 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=15)
    965               Index Cond: (id = ss.playback_session_id)
    966 Planning Time: 91.388 ms
    967 Execution Time: 0.404 ms
    968 }}}
    969 
    970 Бидејќи се корситат индексите креирани за примарните клучеви, како и индексот {{{ idx_song_streams_user_id }}}, нема потреба за дополнителна оптимизација на прашалникот
     42  ->  Hash Join  (cost=37.34..12334.42 rows=1757 width=481) (actual time=2.712..34.856 rows=17868 loops=4)
     43        Hash Cond: (av.bookable_id = b.bookable_id)
     44        ->  Parallel Seq Scan on availabilityslot av
     45              Filter: ((status)::text = 'AVAILABLE'::text)
     46Planning Time: 0.836 ms
     47Execution Time: 55.127 ms
     48}}}
     49
     50'''1.2 - 13.717 ms'''
     51
     52{{{
     53Gather  (cost=1042.46..13517.18 rows=1873 width=481) (actual time=9.626..13.638 rows=0 loops=1)
     54  ->  Hash Join  (cost=42.46..12329.88 rows=604 width=481)
     55        ->  Parallel Seq Scan on availabilityslot av
     56              Filter: ((status)::text = 'AVAILABLE'::text)
     57Planning Time: 1.078 ms
     58Execution Time: 13.717 ms
     59}}}
     60
     61'''1.3 - 13.899 ms'''
     62
     63{{{
     64Gather  (cost=1037.17..13320.92 rows=17 width=481) (actual time=9.876..13.817 rows=0 loops=1)
     65  ->  Hash Join  (cost=37.17..12319.22 rows=5 width=481)
     66        ->  Parallel Seq Scan on availabilityslot av
     67              Filter: ((status)::text = 'AVAILABLE'::text)
     68Planning Time: 1.040 ms
     69Execution Time: 13.899 ms
     70}}}
     71
     72При почетната анализа беше забележано дека PostgreSQL користи Parallel Sequential Scan врз табелата {{{AvailabilitySlot}}}, како и Sequential Scan врз {{{Bookable}}} и {{{Location}}}. Ова значеше дека системот обработува голем број редици за да ги пронајде потребните достапни артисти и бендови.
     73
     74За оптимизација беа додадени следните индекси:
     75
     76{{{
     77CREATE INDEX idx_availability_status
     78ON AvailabilitySlot(status);
     79
     80CREATE INDEX idx_availability_bookable
     81ON AvailabilitySlot(bookable_id);
     82
     83CREATE INDEX idx_bookable_location
     84ON Bookable(location_id);
     85
     86CREATE INDEX idx_location_city
     87ON Location(city);
     88
     89CREATE INDEX idx_bookablegenre_bookable
     90ON BookableGenre(bookable_id);
     91
     92CREATE INDEX idx_bookablegenre_genre
     93ON BookableGenre(genre_id);
     94
     95CREATE INDEX idx_genre_name
     96ON Genre(genre_name);
     97}}}
     98
     99=== Време на извршување со индекси ===
     100
     101'''1.1 - 77.491 ms'''
     102
     103{{{
     104Hash Left Join  (cost=58.20..2541.02 rows=23970 width=481) (actual time=0.959..74.797 rows=71472 loops=1)
     105  ->  Nested Loop
     106        ->  Index Scan using idx_availability_bookable on availabilityslot av
     107Planning Time: 1.861 ms
     108Execution Time: 77.491 ms
     109}}}
     110
     111'''1.2 - 0.426 ms'''
     112
     113{{{
     114Nested Loop  (cost=38.10..8550.23 rows=59922 width=481) (actual time=0.355..0.358 rows=0 loops=1)
     115  ->  Index Scan using idx_availability_bookable on availabilityslot av
     116Planning Time: 1.240 ms
     117Execution Time: 0.426 ms
     118}}}
     119
     120'''1.3 - 0.753 ms'''
     121
     122{{{
     123Nested Loop  (cost=3.17..365.38 rows=2397 width=481) (actual time=0.684..0.687 rows=0 loops=1)
     124  ->  Index Scan using idx_availability_bookable on availabilityslot av
     125Planning Time: 1.272 ms
     126Execution Time: 0.753 ms
     127}}}
     128
     129По оптимизацијата PostgreSQL започна да користи {{{Index Scan}}} и {{{Index Only Scan}}}, со што значително се намали времето на извршување кај query-ите што филтрираат по жанр и град.
     130
     131Најголемо подобрување беше забележано кај query-ите со филтрирање по жанр:
     132
     133 * од ~13 ms на ~0.4 ms
     134 * од ~13 ms на ~0.7 ms
     135
     136Првиот query и понатаму имаше поголемо време на извршување бидејќи враќа многу голем број резултати (~71,000 редици), но planner-от започна да користи индексно пребарување наместо Parallel Sequential Scan.
     137
     138== 2. Анализа и оптимизација на vw_client_booking_history ==
     139
     140Погледот {{{vw_client_booking_history}}} се користи за прикажување на историјата на booking-ите на клиентите, нивниот статус и информациите за плаќање.
     141
     142Прашалниците кои беа тестирани се следните:
     143
     144{{{
     145-- 2.1
     146SELECT *
     147FROM vw_client_booking_history
     148WHERE client_id = 1500;
     149
     150-- 2.2
     151SELECT *
     152FROM vw_client_booking_history
     153WHERE payment_status = 'PAID';
     154}}}
     155
     156=== Време на извршување без индекси ===
     157
     158'''2.1 - 478.399 ms'''
     159
     160{{{
     161Nested Loop  (cost=394513.00..438192.78 rows=3976 width=58) (actual time=434.407..443.008 rows=0 loops=1)
     162  ->  Gather
     163        ->  Parallel Hash Right Join
     164              ->  Parallel Seq Scan on payment p
     165              ->  Parallel Seq Scan on bookingrequest br
     166Planning Time: 44.765 ms
     167Execution Time: 478.399 ms
     168}}}
     169
     170'''2.2 - 159.853 ms'''
     171
     172{{{
     173Nested Loop  (cost=1001.86..42632.01 rows=1 width=58) (actual time=142.971..159.779 rows=0 loops=1)
     174  ->  Gather
     175        ->  Parallel Seq Scan on payment p
     176              Filter: ((payment_status)::text = 'PAID'::text)
     177Planning Time: 1.571 ms
     178Execution Time: 159.853 ms
     179}}}
     180
     181При почетната анализа беше забележано дека PostgreSQL користи Parallel Sequential Scan врз табелите {{{BookingRequest}}}, {{{Booking}}} и {{{Payment}}}. Ова предизвикуваше значително време на извршување, особено кај query-ите што пребаруваат според клиент или статус на плаќање.
     182
     183За оптимизација беа додадени следните индекси:
     184
     185{{{
     186CREATE INDEX idx_bookingrequest_client
     187ON BookingRequest(client_id);
     188
     189CREATE INDEX idx_offer_request
     190ON Offer(request_id);
     191
     192CREATE INDEX idx_offer_bookable
     193ON Offer(bookable_id);
     194
     195CREATE INDEX idx_booking_offer
     196ON Booking(offer_id);
     197
     198CREATE INDEX idx_booking_status
     199ON Booking(booking_status);
     200
     201CREATE INDEX idx_payment_booking
     202ON Payment(booking_id);
     203
     204CREATE INDEX idx_payment_status
     205ON Payment(payment_status);
     206
     207CREATE INDEX idx_bookable_id
     208ON Bookable(bookable_id);
     209}}}
     210
     211=== Време на извршување со индекси ===
     212
     213'''2.1 - 44.997 ms'''
     214
     215{{{
     216Nested Loop  (cost=1115.31..63410.15 rows=3975 width=58) (actual time=38.480..44.907 rows=0 loops=1)
     217  ->  Parallel Bitmap Heap Scan on bookingrequest br
     218        ->  Bitmap Index Scan on idx_bookingrequest_client
     219  ->  Index Scan using idx_offer_request on offer o
     220  ->  Index Scan using idx_booking_offer on booking bk
     221Planning Time: 2.529 ms
     222Execution Time: 44.997 ms
     223}}}
     224
     225'''2.2 - 0.114 ms'''
     226
     227{{{
     228Nested Loop  (cost=2.29..14.64 rows=1 width=58) (actual time=0.047..0.049 rows=0 loops=1)
     229  ->  Index Scan using idx_payment_status on payment p
     230Planning Time: 2.188 ms
     231Execution Time: 0.114 ms
     232}}}
     233
     234По оптимизацијата PostgreSQL започна да користи:
     235
     236 * {{{Bitmap Index Scan}}}
     237 * {{{Index Scan}}}
     238 * {{{Index Only Scan}}}
     239
     240Најголемо подобрување беше забележано кај query-от што пребарува според {{{payment_status}}}, каде времето на извршување се намали:
     241
     242 * од ~159 ms
     243 * на ~0.1 ms
     244
     245Исто така, query-от што пребарува според {{{client_id}}} се подобри:
     246
     247 * од ~478 ms
     248 * на ~44 ms