Changes between Version 11 and Version 12 of QueryOptimization


Ignore:
Timestamp:
06/14/26 16:01:09 (8 days ago)
Author:
231136
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v11 v12  
    11= Оптимизација на прашалници и погледи =
    22
    3 Во оваа фаза ќе ги анализираме погледите дефинирани во [[DatabaseCreation|Фаза 2]] преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
    4 
    5 === 1. Анализа на поглед 1, добивање на бројот на следбеници и бројот на профили кои ги следи даден корисник ===
     3Во оваа фаза ќе ги анализираме погледите дефинирани во [Фаза 2](https://develop.finki.ukim.mk/projects/finkwave/wiki/DatabaseCreation) преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
     4
     5== 1. Анализа на поглед 1, добивање на бројот на следбеници и бројот на профили кои ги следи даден корисник ==
    66
    77Прашалниците кои ќе ги тестираме се следните:
    … …  
    1414}}}
    1515
    16 ==== Време на извршување без индекси:
     16=== Време на извршување без индекси:
    1717
    1818**1A - 18.639 ms**
    … …  
    7070}}}
    7171
    72 
    73 ==== Време на извршување со индекс:
    74 
    75 **1A — 3.993 ms** (беше 18.639 ms)
     72=== Време на извршување со индекс:
     73
     74**1A - 3.993 ms** (беше 18.639 ms)
    7675
    7776{{{
    … …  
    9493}}}
    9594
    96 **1B — 1158.681 ms** (беше 6055.326 ms)
     95**1B - 1158.681 ms** (беше 6055.326 ms)
    9796
    9897{{{
    … …  
    123122}}}
    124123
    125 === 2. Анализа на поглед 2, најактивни корисници на платформата според бројот на слушања во изминатите 30 дена ===
     124=== Влијание на индексот врз insert/update
     125
     126Тестирани прашалници:
     127{{{
     128INSERT INTO follows (follower_user_id, followed_user_id) VALUES (185508, 1);
     129
     130UPDATE follows SET followed_user_id = 1 WHERE id = 1;
     131}}}
     132
     133**Без индекс - insert 1.120 ms, update 0.668 ms**
     134
     135{{{
     136 Insert on follows  (cost=0.00..0.02 rows=0 width=0) (actual time=0.332..0.332 rows=0 loops=1)
     137   ->  Result  (cost=0.00..0.02 rows=1 width=32) (actual time=0.049..0.049 rows=1 loops=1)
     138 Planning Time: 0.108 ms
     139 Trigger for constraint follows_follower_user_id_fkey: time=0.670 calls=1
     140 Trigger for constraint follows_followed_user_id_fkey: time=0.097 calls=1
     141 Execution Time: 1.120 ms
     142}}}
     143{{{
     144 Update on follows  (cost=0.41..8.43 rows=0 width=0) (actual time=0.182..0.182 rows=0 loops=1)
     145   ->  Index Scan using follows_pkey on follows  (cost=0.41..8.43 rows=1 width=14) (actual time=0.028..0.029 rows=1 loops=1)
     146         Index Cond: (id = 1)
     147 Planning Time: 0.378 ms
     148 Trigger for constraint follows_followed_user_id_fkey: time=0.410 calls=1
     149 Execution Time: 0.668 ms
     150}}}
     151
     152**Со индекс - insert 0.541 ms, update 0.761 ms**
     153
     154{{{
     155 Insert on follows  (cost=0.00..0.02 rows=0 width=0) (actual time=0.172..0.172 rows=0 loops=1)
     156   ->  Result  (cost=0.00..0.02 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)
     157 Planning Time: 0.072 ms
     158 Trigger for constraint follows_follower_user_id_fkey: time=0.326 calls=1
     159 Trigger for constraint follows_followed_user_id_fkey: time=0.030 calls=1
     160 Execution Time: 0.541 ms
     161}}}
     162{{{
     163 Update on follows  (cost=0.42..8.44 rows=0 width=0) (actual time=0.205..0.205 rows=0 loops=1)
     164   ->  Index Scan using follows_pkey on follows  (cost=0.42..8.44 rows=1 width=14) (actual time=0.028..0.029 rows=1 loops=1)
     165         Index Cond: (id = 1)
     166 Planning Time: 0.506 ms
     167 Trigger for constraint follows_followed_user_id_fkey: time=0.472 calls=1
     168 Execution Time: 0.761 ms
     169}}}
     170
     171
     172== 2. Анализа на поглед 2, најактивни корисници на платформата според бројот на слушања во изминатите 30 дена ==
    126173
    127174Прашалниците кои ќе ги тестираме се следните:
    … …  
    134181}}}
    135182
    136 ==== Време на извршување без индекси:
    137 
    138 
    139 **2A — 389.404 ms**
     183=== Време на извршување без индекси:
     184
     185
     186**2A - 389.404 ms**
    140187
    141188{{{
    … …  
    154201}}}
    155202
    156 **2B — 1976.733 ms**
     203**2B - 1976.733 ms**
    157204
    158205{{{
    … …  
    187234}}}
    188235
    189 ==== Време за извршување по додавање на индекс
    190 
    191 **2A — 0.453 ms** (was 389.404 ms)
     236=== Време за извршување по додавање на индекс
     237
     238**2A - 0.453 ms** (was 389.404 ms)
    192239
    193240{{{
    … …  
    2062532Б остана непроменето - бидејќи прашалникот треба да направи комплексна агрегација на големи табели нема баш некој конкретен индекс што може да ги подобри перформансите. Доколку овој прашалник се извршува често во апликацијата, јасно е дека тоа може да доведе до проблеми. Ова можеме да го решиме на повеќе начини: со менување на погледот во материјализиран поглед, со кеширање и слично. Првиот пристап (материјализирани погледи) како решение ќе го погледнеме понатаму во оптимизацијата на други погледи, а конкретно за овој поглед ќе одиме со вториот пристап, поточно со кеширање кое ќе биде имплементирано во самиот апликациски код.
    207254
    208 
    209 
    210 === 3. Анализа на поглед 3, рангирање на песни по нивните просечни оценки и бројот на вкупни оценки, соодветно ===
     255Индексот не го бришеме бидејќи ке биде корисен и за други прашалници понатаму низ анализата.
     256
     257=== Влијание на индексот врз insert/update
     258
     259Тестирани прашалници:
     260{{{
     261INSERT INTO song_streams (playback_session_id, song_id, streamed_at, user_id)
     262VALUES (362881, 518859, now(), 910877);
     263
     264UPDATE song_streams SET user_id = 910878 WHERE id = 1;
     265}}}
     266
     267**Без индекс - insert 1.343 ms, update 0.372 ms**
     268
     269{{{
     270 Insert on song_streams  (cost=0.00..0.02 rows=0 width=0) (actual time=0.390..0.391 rows=0 loops=1)
     271   ->  Result  (cost=0.00..0.02 rows=1 width=40) (actual time=0.056..0.057 rows=1 loops=1)
     272 Planning Time: 0.076 ms
     273 Trigger for constraint song_streams_playback_session_id_fkey: time=0.517 calls=1
     274 Trigger for constraint song_streams_song_id_fkey: time=0.412 calls=1
     275 Execution Time: 1.343 ms
     276}}}
     277{{{
     278 Update on song_streams  (cost=0.43..8.45 rows=0 width=0) (actual time=0.252..0.253 rows=0 loops=1)
     279   ->  Index Scan using song_streams_pkey on song_streams  (cost=0.43..8.45 rows=1 width=14) (actual time=0.037..0.042 rows=1 loops=1)
     280         Index Cond: (id = 1)
     281 Planning Time: 0.586 ms
     282 Execution Time: 0.372 ms
     283}}}
     284
     285**Со индекс - insert 0.632 ms, update 0.323 ms**
     286
     287{{{
     288 Insert on song_streams  (cost=0.00..0.02 rows=0 width=0) (actual time=0.198..0.198 rows=0 loops=1)
     289   ->  Result  (cost=0.00..0.02 rows=1 width=40) (actual time=0.030..0.030 rows=1 loops=1)
     290 Planning Time: 0.049 ms
     291 Trigger for constraint song_streams_playback_session_id_fkey: time=0.237 calls=1
     292 Trigger for constraint song_streams_song_id_fkey: time=0.184 calls=1
     293 Execution Time: 0.632 ms
     294}}}
     295{{{
     296 Update on song_streams  (cost=0.43..8.45 rows=0 width=0) (actual time=0.246..0.246 rows=0 loops=1)
     297   ->  Index Scan using song_streams_pkey on song_streams  (cost=0.43..8.45 rows=1 width=14) (actual time=0.040..0.042 rows=1 loops=1)
     298         Index Cond: (id = 1)
     299 Planning Time: 0.474 ms
     300 Execution Time: 0.323 ms
     301}}}
     302
     303== 3. Анализа на поглед 3, рангирање на песни по нивните просечни оценки и бројот на вкупни оценки, соодветно ==
    211304
    212305Прашалниците кои ќе ги тестираме се следните:
    … …  
    220313}}}
    221314
    222 ==== Време за извршување без индекси
    223 
    224 **3A — 705.179 ms**
     315=== Време за извршување без индекси
     316
     317**3A - 705.179 ms**
    225318
    226319{{{
    … …  
    243336}}}
    244337
    245 **3B — 20559.318 ms**
     338**3B - 20559.318 ms**
    246339
    247340{{{
    … …  
    280373CREATE INDEX idx_reviews_song_id_grade ON reviews(song_id, grade);
    281374}}}
    282 
    283 **3A — 0.630 ms** (was 705.179 ms)
     375**3A - 0.630 ms** (was 705.179 ms)
    284376
    285377{{{
    … …  
    302394Поради таа причина обичниот поглед во овој случај ќе го замениме со материјализиран поглед.
    303395
    304 ==== Време за извршување на прашалници по додавање на материјализиран поглед
     396=== Време за извршување на прашалници по додавање на материјализиран поглед
    305397
    306398**3A - 0.19 ms**
    … …  
    326418Исто така вреди да се напомене дека во апликацискиот код ќе треба да имплементираме логика за повремено ажурирање на овие погледи, користејќи `REFRESH MATERIALIZED VIEW`, и дека еден ваков refresh трае ~45 секунди.
    327419
    328 === 4. Анализа на поглед 4, број на слушања (популарност) на артисте за изминатите 30 дена
     420Бидејќи користиме материјализиран поглед, **индексот го бришеме**:
     421{{{
     422DROP INDEX idx_reviews_song_id_grade;
     423}}}
     424
     425
     426== 4. Анализа на поглед 4, број на слушања (популарност) на артисте за изминатите 30 дена
    329427
    330428Прашалник кој ќе го тестираме:
    … …  
    334432}}}
    335433
    336 ==== Време на извршување без индекси:
     434=== Време на извршување без индекси:
    337435
    338436**5295.544 ms**
    339437
    340 {{{ 
     438{{{
    341439 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)
    342440   Filter: ((artist_popularity_last_30_days.artist_display_name)::text = 'Rush'::text)
    … …  
    381479}}}
    382480
    383 Најбавните делови се секвенцијално скенирање на табелите {{{song_streams}}} и {{{songs}}}, што можеме да го оптимизираме со индекс:
     481Најбавните делови се секвенцијално скенирање на табелите }}}song_streams}}} и }}}songs}}}, што можеме да го оптимизираме со индекс:
    384482
    385483{{{
    … …  
    388486}}}
    389487
    390 ==== Време на извршување со индекси:
     488=== Време на извршување со индекси:
    391489
    392490**2923.180 ms**
    … …  
    426524}}}
    427525
    428 Сега планерот го користи креираниот индекс за табелата {{{song_streams}}}, но сепак табелата {{{songs}}} треба секвенцијално да се скенира за да се пресмета статистиката за артистите. Дополнителна оптимизација правиме со материјализиран поглед:
     526Сега планерот го користи креираниот индекс за табелата }}}song_streams}}}, но сепак табелата }}}songs}}} треба секвенцијално да се скенира за да се пресмета статистиката за артистите. Oптимизација правиме со материјализиран поглед, а индексите од горе ги бришеме:
    429527
    430528{{{
    … …  
    453551FROM artist_listens;
    454552
    455 }}}
    456 
    457 ==== Време за извршување на прашалникот по додавање на материјализиран поглед
     553DROP INDEX idx_song_streams_streamed_at_song_id;
     554DROP INDEX idx_songs_owner_artist_id;
     555
     556}}}
     557
     558=== Време за извршување на прашалникот по додавање на материјализиран поглед
    458559
    459560{{{
    … …  
    465566}}}
    466567
    467 Со материјализиран поглед добиваме <10 ms за читање.
    468 
    469 === 5. Анализа на поглед 5, број на слушања (популарност) на песните за изминатите 30 дена
     568Со материјализиран поглед добиваме <10 ms за читање.
     569Материјализираните погледи немаат никакво влијание на перформансите за запишување и ажурирање.
     570
     571== 5. Анализа на поглед 5, број на слушања (популарност) на песните за изминатите 30 дена
    470572
    471573Прашалник кој ќе го тестираме:
    … …  
    475577}}}
    476578
    477 ==== Време за извршување со креираниот индекс {{{idx_song_streams_streamed_at_song_id}}}
     579=== Време за извршување со креираниот индекс }}}idx_song_streams_streamed_at_song_id}}}
    478580
    479581**1682.017 ms**
    … …  
    498600                          Hash Cond: (s.owner_artist_id = a.id)
    499601                          ->  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)
     602                                Hash Cond: (s.published_by_label_id = la.id)
    501603                                ->  Hash Join  (cost=43501.50..86090.08 rows=43884 width=48) (actual time=1071.308..1327.862 rows=60789 loops=5)
    502604                                      Hash Cond: (s.id = sc.song_id)
    … …  
    535637"  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"
    536638Execution Time: 1682.017 ms
    537 
    538639}}}
    539640
    … …  
    556657    a.display_name AS artist_display_name,
    557658    s.visibility AS song_visibility,
     659    u.username AS label_admin_username,
    558660    l.name AS label_name,
    559661    sc.total_streams
    … …  
    561663JOIN songs s ON s.id = sc.song_id
    562664JOIN 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 ==== Време за извршување на прашалникот по додавање на материјализиран поглед
     665LEFT JOIN label_admins la ON s.published_by_label_id = la.id
     666LEFT JOIN labels l ON l.id = la.label_id
     667LEFT JOIN users u ON u.id = la.user_id;
     668}}}
     669
     670=== Време за извршување на прашалникот по додавање на материјализиран поглед
    568671
    569672{{{
    … …  
    581684
    582685
    583 === 6. Анализа на поглед 6, детален преглед за артистите групирани по издавачка куќа на која припаѓаат
     686== 6. Анализа на поглед 6, детален преглед за артистите групирани по издавачка куќа на која припаѓаат
    584687
    585688Го тестираме прашалникот:
    586689
    587 {{{ SELECT * FROM label_artists_info WHERE label_name='Piercing Abyss Records';}}}
    588 
    589 
    590 ==== Време за извршување без индекси
     690{{{
     691SELECT * FROM label_artists_info WHERE label_name='Piercing Abyss Records';
     692}}}
     693
     694
     695=== Време за извршување без индекси
    591696
    592697**3197.076 ms**
    … …  
    631736}}}
    632737
    633 За да го оптимизираме секвенцијалното скенирање на табелите {{{songs}}} и {{{artist_labels}}}, ги креираме индексите:
     738За да го оптимизираме секвенцијалното скенирање на табелите }}}songs}}} и }}}artist_labels}}}, ги креираме индексите:
    634739
    635740{{{
    … …  
    638743}}}
    639744
    640 ==== Време за извршување со индекси
     745=== Време за извршување со индекси
    641746
    642747**5.213 ms**
    … …  
    671776}}}
    672777
    673 Планерот го користи и претходно креираниот индекс {{{idx_follows_followed_user_id}}}. Индексите го забрзаа извршувањето за речиси 100%, па заклучуваме дека нема потреба од дополнителна оптимизација.
    674 
    675 
    676 === 7. Анализа на поглед 7, детални информации за секоја песна
     778Планерот го користи и претходно креираниот индекс }}}idx_follows_followed_user_id}}}. Индексите го забрзаа извршувањето за речиси 100%, па заклучуваме дека нема потреба од дополнителна оптимизација.
     779
     780=== Влијание на индексот врз insert/update
     781
     782Тестирани прашалници:
     783{{{
     784INSERT INTO songs (title, visibility, owner_artist_id, published_by_artist_id, genre)
     785VALUES ('benchmark', 'PUBLIC', 494, 494, 'rock');
     786
     787UPDATE songs SET owner_artist_id = (owner_artist_id % 100000) + 1 WHERE id = 1;
     788}}}
     789
     790**Без индекс - insert 1.138 ms, update 0.883 ms**
     791
     792{{{
     793 Insert on songs  (cost=0.00..0.01 rows=0 width=0) (actual time=0.482..0.482 rows=0 loops=1)
     794   ->  Result  (cost=0.00..0.01 rows=1 width=1202) (actual time=0.044..0.045 rows=1 loops=1)
     795 Planning Time: 0.084 ms
     796 Trigger for constraint songs_owner_artist_id_fkey: time=0.524 calls=1
     797 Trigger for constraint songs_published_by_artist_id_fkey: time=0.081 calls=1
     798 Trigger for constraint songs_published_by_label_id_fkey: time=0.024 calls=1
     799 Execution Time: 1.138 ms
     800}}}
     801{{{
     802 Update on songs  (cost=0.43..8.45 rows=0 width=0) (actual time=0.357..0.357 rows=0 loops=1)
     803   ->  Index Scan using songs_pkey on songs  (cost=0.43..8.45 rows=1 width=14) (actual time=0.032..0.035 rows=1 loops=1)
     804         Index Cond: (id = 1)
     805 Planning Time: 0.481 ms
     806 Trigger for constraint songs_owner_artist_id_fkey: time=0.431 calls=1
     807 Execution Time: 0.883 ms
     808}}}
     809
     810**Со индекс - insert 0.560 ms, update 0.814 ms**
     811
     812{{{
     813 Insert on songs  (cost=0.00..0.01 rows=0 width=0) (actual time=0.205..0.205 rows=0 loops=1)
     814   ->  Result  (cost=0.00..0.01 rows=1 width=1202) (actual time=0.018..0.019 rows=1 loops=1)
     815 Planning Time: 0.043 ms
     816 Trigger for constraint songs_owner_artist_id_fkey: time=0.240 calls=1
     817 Trigger for constraint songs_published_by_artist_id_fkey: time=0.079 calls=1
     818 Trigger for constraint songs_published_by_label_id_fkey: time=0.021 calls=1
     819 Execution Time: 0.560 ms
     820}}}
     821{{{
     822 Update on songs  (cost=0.43..8.45 rows=0 width=0) (actual time=0.342..0.343 rows=0 loops=1)
     823   ->  Index Scan using songs_pkey on songs  (cost=0.43..8.45 rows=1 width=14) (actual time=0.031..0.034 rows=1 loops=1)
     824         Index Cond: (id = 1)
     825 Planning Time: 0.483 ms
     826 Trigger for constraint songs_owner_artist_id_fkey: time=0.386 calls=1
     827 Execution Time: 0.814 ms
     828}}}
     829
     830=== Влијание на индексот врз insert/update
     831
     832Тестирани прашалници:
     833{{{
     834INSERT INTO artist_labels (artist_id, label_id, active, start_date)
     835VALUES (494, 1, true, DATE '2020-01-01');
     836
     837UPDATE artist_labels SET label_id = (label_id % 375) + 1 WHERE id = 1;
     838}}}
     839
     840**Без индекс - insert 1.219 ms, update 0.608 ms**
     841
     842{{{
     843 Insert on artist_labels  (cost=0.00..0.01 rows=0 width=0) (actual time=0.330..0.331 rows=0 loops=1)
     844   ->  Result  (cost=0.00..0.01 rows=1 width=33) (actual time=0.040..0.041 rows=1 loops=1)
     845 Planning Time: 0.082 ms
     846 Trigger for constraint artist_labels_artist_id_fkey: time=0.601 calls=1
     847 Trigger for constraint artist_labels_label_id_fkey: time=0.267 calls=1
     848 Execution Time: 1.219 ms
     849}}}
     850{{{
     851 Update on artist_labels  (cost=0.41..8.44 rows=0 width=0) (actual time=0.176..0.177 rows=0 loops=1)
     852   ->  Index Scan using artist_labels_pkey on artist_labels  (cost=0.41..8.44 rows=1 width=14) (actual time=0.030..0.031 rows=1 loops=1)
     853         Index Cond: (id = 1)
     854 Planning Time: 0.476 ms
     855 Trigger for constraint artist_labels_label_id_fkey: time=0.342 calls=1
     856 Execution Time: 0.608 ms
     857}}}
     858
     859**Со индекс - insert 1.334 ms, update 0.596 ms**
     860
     861{{{
     862 Insert on artist_labels  (cost=0.00..0.01 rows=0 width=0) (actual time=0.384..0.385 rows=0 loops=1)
     863   ->  Result  (cost=0.00..0.01 rows=1 width=33) (actual time=0.036..0.037 rows=1 loops=1)
     864 Planning Time: 0.078 ms
     865 Trigger for constraint artist_labels_artist_id_fkey: time=0.655 calls=1
     866 Trigger for constraint artist_labels_label_id_fkey: time=0.276 calls=1
     867 Execution Time: 1.334 ms
     868}}}
     869{{{
     870 Update on artist_labels  (cost=0.41..8.44 rows=0 width=0) (actual time=0.182..0.182 rows=0 loops=1)
     871   ->  Index Scan using artist_labels_pkey on artist_labels  (cost=0.41..8.44 rows=1 width=14) (actual time=0.029..0.030 rows=1 loops=1)
     872         Index Cond: (id = 1)
     873 Planning Time: 0.538 ms
     874 Trigger for constraint artist_labels_label_id_fkey: time=0.318 calls=1
     875 Execution Time: 0.596 ms
     876}}}
     877
     878
     879== 7. Анализа на поглед 7, детални информации за секоја песна
    677880
    678881Прашалник кој го тестираме:
    … …  
    682885}}}
    683886
    684 ==== Време за извршување без индекси
     887=== Време за извршување без индекси
    685888
    686889**93882.201 ms**
    … …  
    759962}}}
    760963
    761 За оптимизирање на секвенцијалните скенирање на табелите {{{artist_labels}}}, {{{album_tracks}}} и {{{songs}}} ги креираме индексите:
     964За оптимизирање на секвенцијалните скенирање на табелите }}}artist_labels}}}, }}}album_tracks}}} и }}}songs}}} ги креираме индексите:
    762965
    763966{{{
    … …  
    772975}}}
    773976
    774 ==== Време за извршување со индекси
     977=== Време за извршување со индекси
    775978
    776979**3599.404 ms**
    … …  
    8411044}}}
    8421045
    843 Бидејќи сепак имаме секвенцијално скенирање на табелата {{{song_streams}}} поради групирањето на слушања по песна, дополнително оптимизираме со материјализиран погледи:
    844 
    845 {{{
    846 create materialized view song_stream_counts_mv as
    847 select
     1046Бидејќи сепак имаме секвенцијално скенирање на табелата }}}song_streams}}} поради групирањето на слушања по песна и ова не може да се оптимизира со индекси, оптимизираме со материјализиран погледи, а индексите од горе ги бришеме:
     1047
     1048{{{
     1049CREATE MATERIALIZED VIEW song_stream_counts_mv AS
     1050WITH live AS (
     1051    SELECT ss.song_id, count(*) AS streams
     1052    FROM song_streams ss
     1053    WHERE ss.streamed_at >= COALESCE(
     1054              (SELECT max(partition_month) + interval '1 month'
     1055               FROM song_stream_sealed_partitions),
     1056              timestamp '-infinity'
     1057          )
     1058    GROUP BY ss.song_id
     1059)
     1060SELECT song_id, SUM(streams) AS streams
     1061FROM (
     1062    SELECT song_id, streams FROM song_stream_counts_archive
     1063    UNION ALL
     1064    SELECT song_id, streams FROM live
     1065) t
     1066GROUP BY song_id;
     1067
     1068CREATE MATERIALIZED VIEW song_playlist_counts_mv AS
     1069SELECT
    8481070    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,
     1071    COUNT(*) AS saved_in_playlists
     1072FROM playlist_tracks
     1073GROUP BY song_id;
     1074
     1075
     1076CREATE OR REPLACE VIEW songs_details AS
     1077SELECT
     1078    s.title AS title,
     1079    a.display_name AS artist_name,
     1080    COALESCE(l.name, 'SOLO') AS label_name,
     1081    COALESCE(sc.streams, 0) AS streams,
     1082    COALESCE(alb.title, 'SINGLE') AS album_title,
     1083    COALESCE(pc.saved_in_playlists, 0) AS saved_in_playlists,
    8701084    sag.num_reviews,
    8711085    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 ==== Време за извршување на прашалникот по додавање на материјализирани погледи
     1086FROM songs s
     1087LEFT JOIN artists a ON a.id = s.owner_artist_id
     1088LEFT JOIN artist_labels al ON al.artist_id = a.id
     1089LEFT JOIN labels l ON l.id = al.label_id
     1090LEFT JOIN album_tracks at ON at.song_id = s.id
     1091LEFT JOIN albums alb ON alb.id = at.album_id
     1092LEFT JOIN song_stream_counts_mv sc ON sc.song_id = s.id
     1093LEFT JOIN song_playlist_counts_mv pc ON pc.song_id = s.id
     1094LEFT JOIN song_average_grade_mv sag ON sag.song_id = s.id;
     1095
     1096drop index idx_songs_title;
     1097
     1098drop index idx_album_tracks_song_id;
     1099
     1100drop index idx_artist_labels_artist_id;
     1101}}}
     1102
     1103
     1104=== Време за извршување на прашалникот по додавање на материјализирани погледи
    8851105
    8861106**333.811 ms**
    … …  
    9331153}}}
    9341154
    935 
    936 === 8. Анализа на поглед 8, историја на слушање песни од корисниците
     1155=== Влијание на индексот врз insert/update
     1156
     1157Тестирани прашалници:
     1158{{{
     1159INSERT INTO artist_labels (artist_id, label_id, active, start_date)
     1160VALUES (494, 1, true, DATE '2020-01-01');
     1161
     1162UPDATE artist_labels SET artist_id = (artist_id % 100000) + 1 WHERE id = 1;
     1163}}}
     1164
     1165
     1166== 8. Анализа на поглед 8, историја на слушање песни од корисниците
    9371167
    9381168Прашалник кој го тестираме:
    … …  
    9451175}}}
    9461176
    947 ==== Време за извршување без дополнителни индекси
     1177=== Време за извршување без дополнителни индекси
    9481178
    9491179**0.404 ms**
    … …  
    9681198}}}
    9691199
    970 Бидејќи се корситат индексите креирани за примарните клучеви, како и индексот {{{ idx_song_streams_user_id }}}, нема потреба за дополнителна оптимизација на прашалникот
     1200Бидејќи се корситат индексите креирани за примарните клучеви, како и индексот }}}idx_song_streams_user_id }}}, нема потреба за дополнителна оптимизација на прашалникот
     1201
     1202