Changes between Version 2 and Version 3 of QueryOptimization-AdvDb


Ignore:
Timestamp:
05/09/26 14:33:45 (2 weeks ago)
Author:
231175
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization-AdvDb

    v2 v3  
    11= Оптимизација на прашалници
    22
    3 == 1. Анализа на поглед бр.1, превземање на сите запишани курсеви за корисник
    4 
    5 == 2. Анализа на поглед бр.2, превземање на детални информации за запишан курс
    6 
    7 == 3. Анализа на поглед бр.3, превземање на топ 10 најпопуларни курсеви според бројот на записи од страна на корисници
     3== 1. Анализа на поглед бр.1 - enrolled_courses: превземање на сите запишани курсеви за секој корисник
     4
     5За конкретен корисник:
     6{{{
     7select * from enrolled_courses where user_id = 1011;
     8}}}
     9
     10Со explain analyze добиваме Execution Time: 770.782 ms (~0.7s), што е прилично бавно и може да се подобри со индекси.
     11
     12{{{
     13Gather  (cost=157267.74..176759.03 rows=6 width=185) (actual time=760.556..764.927 rows=5 loops=1)
     14  Workers Planned: 3
     15  Workers Launched: 3
     16  ->  Nested Loop  (cost=156267.74..175000.17 rows=2 width=121) (actual time=596.006..606.610 rows=1 loops=4)
     17        ->  Parallel Hash Join  (cost=156267.60..174999.70 rows=4 width=129) (actual time=595.962..606.558 rows=2 loops=4)
     18              Hash Cond: (ct.course_id = c.id)
     19              ->  Parallel Seq Scan on course_translation ct  (cost=0.00..18490.16 rows=64516 width=40) (actual time=0.011..19.982 rows=50000 loops=4)
     20              ->  Parallel Hash  (cost=156267.57..156267.57 rows=2 width=105) (actual time=563.722..563.727 rows=1 loops=4)
     21                    Buckets: 1024  Batches: 1  Memory Usage: 72kB
     22                    ->  Nested Loop  (cost=0.71..156267.57 rows=2 width=105) (actual time=404.840..563.574 rows=1 loops=4)
     23                          ->  Nested Loop  (cost=0.42..156266.88 rows=2 width=25) (actual time=404.809..563.530 rows=1 loops=4)
     24                                ->  Parallel Seq Scan on enrollment e  (cost=0.00..156250.00 rows=2 width=16) (actual time=404.694..563.394 rows=1 loops=4)
     25                                      Filter: (user_id = 1011)
     26                                      Rows Removed by Filter: 2499999
     27                                ->  Index Scan using pk_course_version on course_version cv  (cost=0.42..8.44 rows=1 width=25) (actual time=0.046..0.046 rows=1 loops=5)
     28                                      Index Cond: (id = e.course_version_id)
     29                          ->  Index Scan using pk_course on course c  (cost=0.29..0.35 rows=1 width=80) (actual time=0.020..0.020 rows=1 loops=5)
     30                                Index Cond: (id = cv.course_id)
     31        ->  Memoize  (cost=0.14..0.16 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=10)
     32              Cache Key: ct.language_id
     33              Cache Mode: logical
     34              Worker 0:  Hits: 8  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     35              ->  Index Scan using pk_language on language l  (cost=0.13..0.15 rows=1 width=8) (actual time=0.073..0.073 rows=0 loops=2)
     36                    Index Cond: (id = ct.language_id)
     37                    Filter: ((language_code)::text = 'EN'::text)
     38                    Rows Removed by Filter: 0
     39  SubPlan 1
     40    ->  Aggregate  (cost=79.28..79.29 rows=1 width=32) (actual time=11.716..11.716 rows=1 loops=5)
     41          ->  Sort  (cost=79.27..79.27 rows=2 width=18) (actual time=11.696..11.697 rows=2 loops=5)
     42                Sort Key: st.name
     43                Sort Method: quicksort  Memory: 25kB
     44                ->  Nested Loop  (cost=10.51..79.26 rows=2 width=18) (actual time=0.110..11.683 rows=2 loops=5)
     45                      Join Filter: (s.id = cs.skill_id)
     46                      ->  Nested Loop  (cost=10.23..78.58 rows=2 width=34) (actual time=0.089..11.647 rows=2 loops=5)
     47                            Join Filter: (l_1.id = st.language_id)
     48                            Rows Removed by Join Filter: 2
     49                            ->  Seq Scan on language l_1  (cost=0.00..1.02 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=5)
     50                                  Filter: ((language_code)::text = 'EN'::text)
     51                                  Rows Removed by Filter: 1
     52                            ->  Hash Join  (cost=10.23..77.50 rows=4 width=42) (actual time=0.070..11.625 rows=4 loops=5)
     53                                  Hash Cond: (st.skill_id = cs.skill_id)
     54                                  ->  Seq Scan on skill_translation st  (cost=0.00..62.00 rows=2000 width=34) (actual time=0.008..11.284 rows=2000 loops=5)
     55                                  ->  Hash  (cost=10.21..10.21 rows=2 width=8) (actual time=0.048..0.048 rows=2 loops=5)
     56                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     57                                        ->  Index Only Scan using pk_course_skill on course_skill cs  (cost=0.42..10.21 rows=2 width=8) (actual time=0.038..0.039 rows=2 loops=5)
     58                                              Index Cond: (course_id = c.id)
     59                                              Heap Fetches: 10
     60                      ->  Index Only Scan using pk_skill on skill s  (cost=0.28..0.33 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=10)
     61                            Index Cond: (id = st.skill_id)
     62                            Heap Fetches: 10
     63  SubPlan 2
     64    ->  Aggregate  (cost=47.08..47.09 rows=1 width=32) (actual time=11.069..11.070 rows=1 loops=5)
     65          ->  Sort  (cost=47.07..47.07 rows=2 width=17) (actual time=11.058..11.059 rows=2 loops=5)
     66                Sort Key: tt.name
     67                Sort Method: quicksort  Memory: 25kB
     68                ->  Nested Loop  (cost=10.64..47.06 rows=2 width=17) (actual time=0.097..11.051 rows=2 loops=5)
     69                      Join Filter: (t.id = ct_1.topic_id)
     70                      ->  Nested Loop  (cost=10.37..46.37 rows=2 width=33) (actual time=0.084..11.028 rows=2 loops=5)
     71                            ->  Hash Join  (cost=10.23..45.88 rows=4 width=41) (actual time=0.063..10.994 rows=4 loops=5)
     72                                  Hash Cond: (tt.topic_id = ct_1.topic_id)
     73                                  ->  Seq Scan on topic_translation tt  (cost=0.00..33.00 rows=1000 width=33) (actual time=0.008..10.798 rows=1000 loops=5)
     74                                  ->  Hash  (cost=10.21..10.21 rows=2 width=8) (actual time=0.040..0.040 rows=2 loops=5)
     75                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     76                                        ->  Index Only Scan using pk_course_topic on course_topic ct_1  (cost=0.42..10.21 rows=2 width=8) (actual time=0.030..0.031 rows=2 loops=5)
     77                                              Index Cond: (course_id = c.id)
     78                                              Heap Fetches: 10
     79                            ->  Memoize  (cost=0.14..0.17 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=20)
     80                                  Cache Key: tt.language_id
     81                                  Cache Mode: logical
     82                                  Hits: 18  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     83                                  ->  Index Scan using pk_language on language l_2  (cost=0.13..0.16 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=2)
     84                                        Index Cond: (id = tt.language_id)
     85                                        Filter: ((language_code)::text = 'EN'::text)
     86                                        Rows Removed by Filter: 0
     87                      ->  Index Only Scan using pk_topic on topic t  (cost=0.27..0.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10)
     88                            Index Cond: (id = tt.topic_id)
     89                            Heap Fetches: 10
     90Planning Time: 2.845 ms
     91JIT:
     92  Functions: 189
     93"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
     94"  Timing: Generation 16.567 ms (Deform 9.447 ms), Inlining 0.000 ms, Optimization 6.809 ms, Emission 139.588 ms, Total 162.965 ms"
     95Execution Time: 770.782 ms
     96}}}
     97
     98Со додавање на индекси за надворешните клучеви и филтрирањето
     99
     100{{{
     101create index idx_enrollment_user_id on enrollment(user_id);
     102create index idx_enrollment_course_version_id on enrollment(course_version_id);
     103create index idx_course_version_course_id on course_version(course_id);
     104create index idx_course_translation_course_id on course_translation(course_id);
     105create index idx_course_translation_language_id on course_translation(language_id);
     106create index idx_language_language_code on language(language_code);
     107}}}
     108
     109Перформансите значително се подобруваат. Сега Execution Time: 5.147 ms
     110
     111{{{
     112Nested Loop  (cost=1.57..844.92 rows=6 width=185) (actual time=1.713..4.957 rows=5 loops=1)
     113  ->  Nested Loop  (cost=1.27..84.57 rows=6 width=57) (actual time=0.102..0.263 rows=5 loops=1)
     114        Join Filter: (ct.language_id = l.id)
     115        Rows Removed by Join Filter: 5
     116        ->  Seq Scan on language l  (cost=0.00..1.02 rows=1 width=8) (actual time=0.021..0.023 rows=1 loops=1)
     117              Filter: ((language_code)::text = 'EN'::text)
     118              Rows Removed by Filter: 1
     119        ->  Nested Loop  (cost=1.27..83.40 rows=12 width=65) (actual time=0.078..0.232 rows=10 loops=1)
     120              ->  Nested Loop  (cost=0.85..78.35 rows=6 width=25) (actual time=0.068..0.182 rows=5 loops=1)
     121                    ->  Index Scan using idx_enrollment_user_id on enrollment e  (cost=0.43..27.72 rows=6 width=16) (actual time=0.054..0.127 rows=5 loops=1)
     122                          Index Cond: (user_id = 1011)
     123                    ->  Index Scan using pk_course_version on course_version cv  (cost=0.42..8.44 rows=1 width=25) (actual time=0.007..0.007 rows=1 loops=5)
     124                          Index Cond: (id = e.course_version_id)
     125              ->  Index Scan using idx_course_translation_course_id on course_translation ct  (cost=0.42..0.82 rows=2 width=40) (actual time=0.005..0.008 rows=2 loops=5)
     126                    Index Cond: (course_id = cv.course_id)
     127  ->  Index Scan using pk_course on course c  (cost=0.29..0.35 rows=1 width=80) (actual time=0.004..0.004 rows=1 loops=5)
     128        Index Cond: (id = cv.course_id)
     129  SubPlan 1
     130    ->  Aggregate  (cost=79.28..79.29 rows=1 width=32) (actual time=0.601..0.602 rows=1 loops=5)
     131          ->  Sort  (cost=79.27..79.27 rows=2 width=18) (actual time=0.594..0.595 rows=2 loops=5)
     132                Sort Key: st.name
     133                Sort Method: quicksort  Memory: 25kB
     134                ->  Nested Loop  (cost=10.51..79.26 rows=2 width=18) (actual time=0.058..0.585 rows=2 loops=5)
     135                      Join Filter: (s.id = cs.skill_id)
     136                      ->  Nested Loop  (cost=10.23..78.58 rows=2 width=34) (actual time=0.047..0.563 rows=2 loops=5)
     137                            Join Filter: (l_1.id = st.language_id)
     138                            Rows Removed by Join Filter: 2
     139                            ->  Seq Scan on language l_1  (cost=0.00..1.02 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=5)
     140                                  Filter: ((language_code)::text = 'EN'::text)
     141                                  Rows Removed by Filter: 1
     142                            ->  Hash Join  (cost=10.23..77.50 rows=4 width=42) (actual time=0.037..0.549 rows=4 loops=5)
     143                                  Hash Cond: (st.skill_id = cs.skill_id)
     144                                  ->  Seq Scan on skill_translation st  (cost=0.00..62.00 rows=2000 width=34) (actual time=0.007..0.282 rows=2000 loops=5)
     145                                  ->  Hash  (cost=10.21..10.21 rows=2 width=8) (actual time=0.023..0.023 rows=2 loops=5)
     146                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     147                                        ->  Index Only Scan using pk_course_skill on course_skill cs  (cost=0.42..10.21 rows=2 width=8) (actual time=0.019..0.020 rows=2 loops=5)
     148                                              Index Cond: (course_id = c.id)
     149                                              Heap Fetches: 10
     150                      ->  Index Only Scan using pk_skill on skill s  (cost=0.28..0.33 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10)
     151                            Index Cond: (id = st.skill_id)
     152                            Heap Fetches: 10
     153  SubPlan 2
     154    ->  Aggregate  (cost=47.08..47.09 rows=1 width=32) (actual time=0.326..0.326 rows=1 loops=5)
     155          ->  Sort  (cost=47.07..47.07 rows=2 width=17) (actual time=0.321..0.321 rows=2 loops=5)
     156                Sort Key: tt.name
     157                Sort Method: quicksort  Memory: 25kB
     158                ->  Nested Loop  (cost=10.64..47.06 rows=2 width=17) (actual time=0.056..0.315 rows=2 loops=5)
     159                      Join Filter: (t.id = ct_1.topic_id)
     160                      ->  Nested Loop  (cost=10.37..46.37 rows=2 width=33) (actual time=0.050..0.302 rows=2 loops=5)
     161                            ->  Hash Join  (cost=10.23..45.88 rows=4 width=41) (actual time=0.038..0.287 rows=4 loops=5)
     162                                  Hash Cond: (tt.topic_id = ct_1.topic_id)
     163                                  ->  Seq Scan on topic_translation tt  (cost=0.00..33.00 rows=1000 width=33) (actual time=0.007..0.141 rows=1000 loops=5)
     164                                  ->  Hash  (cost=10.21..10.21 rows=2 width=8) (actual time=0.023..0.023 rows=2 loops=5)
     165                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
     166                                        ->  Index Only Scan using pk_course_topic on course_topic ct_1  (cost=0.42..10.21 rows=2 width=8) (actual time=0.020..0.020 rows=2 loops=5)
     167                                              Index Cond: (course_id = c.id)
     168                                              Heap Fetches: 10
     169                            ->  Memoize  (cost=0.14..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=20)
     170                                  Cache Key: tt.language_id
     171                                  Cache Mode: logical
     172                                  Hits: 18  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     173                                  ->  Index Scan using pk_language on language l_2  (cost=0.13..0.16 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=2)
     174                                        Index Cond: (id = tt.language_id)
     175                                        Filter: ((language_code)::text = 'EN'::text)
     176                                        Rows Removed by Filter: 0
     177                      ->  Index Only Scan using pk_topic on topic t  (cost=0.27..0.33 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=10)
     178                            Index Cond: (id = tt.topic_id)
     179                            Heap Fetches: 10
     180Planning Time: 4.031 ms
     181Execution Time: 5.147 ms
     182}}}
     183
     184
     185
     186== 2. Анализа на поглед бр.2 - enrolled_course_details: превземање на детални информации за запишан курс
     187
     188За конкретен корисник:
     189{{{
     190select * from enrolled_course_details where user_id = 1011;
     191}}}
     192
     193Со explain analyze добиваме Execution Time: 80219.048 ms (~80s), што е прилично бавно и може да се подобри со индекси..
     194
     195
     196{{{
     197Gather  (cost=157267.74..12708954.33 rows=6 width=652) (actual time=16970.606..80212.303 rows=5 loops=1)
     198  Workers Planned: 3
     199  Workers Launched: 3
     200  ->  Nested Loop  (cost=156267.74..175000.17 rows=2 width=628) (actual time=1086.263..1099.974 rows=1 loops=4)
     201        ->  Parallel Hash Join  (cost=156267.60..174999.70 rows=4 width=636) (actual time=1086.215..1099.918 rows=2 loops=4)
     202              Hash Cond: (ct.course_id = c.id)
     203              ->  Parallel Seq Scan on course_translation ct  (cost=0.00..18490.16 rows=64516 width=539) (actual time=0.058..46.789 rows=50000 loops=4)
     204              ->  Parallel Hash  (cost=156267.57..156267.57 rows=2 width=113) (actual time=1044.613..1044.617 rows=1 loops=4)
     205                    Buckets: 1024  Batches: 1  Memory Usage: 104kB
     206                    ->  Nested Loop  (cost=0.71..156267.57 rows=2 width=113) (actual time=888.499..1044.464 rows=1 loops=4)
     207                          ->  Nested Loop  (cost=0.42..156266.88 rows=2 width=33) (actual time=888.448..1044.403 rows=1 loops=4)
     208                                ->  Parallel Seq Scan on enrollment e  (cost=0.00..156250.00 rows=2 width=16) (actual time=888.259..1044.198 rows=1 loops=4)
     209                                      Filter: (user_id = 1011)
     210                                      Rows Removed by Filter: 2499999
     211                                ->  Index Scan using pk_course_version on course_version cv  (cost=0.42..8.44 rows=1 width=25) (actual time=0.093..0.094 rows=1 loops=5)
     212                                      Index Cond: (id = e.course_version_id)
     213                          ->  Index Scan using pk_course on course c  (cost=0.29..0.35 rows=1 width=80) (actual time=0.030..0.031 rows=1 loops=5)
     214                                Index Cond: (id = cv.course_id)
     215        ->  Memoize  (cost=0.14..0.16 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=10)
     216              Cache Key: ct.language_id
     217              Cache Mode: logical
     218              Worker 0:  Hits: 8  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     219              ->  Index Scan using pk_language on language l  (cost=0.13..0.15 rows=1 width=8) (actual time=0.083..0.083 rows=0 loops=2)
     220                    Index Cond: (id = ct.language_id)
     221                    Filter: ((language_code)::text = 'EN'::text)
     222                    Rows Removed by Filter: 0
     223  SubPlan 2
     224    ->  Aggregate  (cost=2088825.58..2088825.59 rows=1 width=32) (actual time=15814.049..15814.051 rows=1 loops=5)
     225          ->  Sort  (cost=64755.80..64755.81 rows=5 width=33) (actual time=496.073..496.080 rows=5 loops=5)
     226"                Sort Key: cm.""position"""
     227                Sort Method: quicksort  Memory: 25kB
     228                ->  Nested Loop  (cost=18870.20..64755.74 rows=5 width=33) (actual time=77.181..496.050 rows=5 loops=5)
     229                      ->  Hash Join  (cost=18870.06..64755.08 rows=10 width=41) (actual time=77.149..496.010 rows=10 loops=5)
     230                            Hash Cond: (cmt.course_module_id = cm.id)
     231                            ->  Seq Scan on course_module_translation cmt  (cost=0.00..40635.00 rows=2000000 width=37) (actual time=0.015..227.561 rows=2000000 loops=5)
     232                            ->  Hash  (cost=18870.00..18870.00 rows=5 width=12) (actual time=77.102..77.102 rows=5 loops=5)
     233                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     234                                  ->  Seq Scan on course_module cm  (cost=0.00..18870.00 rows=5 width=12) (actual time=0.023..77.091 rows=5 loops=5)
     235                                        Filter: (course_version_id = cv.id)
     236                                        Rows Removed by Filter: 999995
     237                      ->  Memoize  (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=50)
     238                            Cache Key: cmt.language_id
     239                            Cache Mode: logical
     240                            Hits: 48  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     241                            ->  Index Scan using pk_language on language l1  (cost=0.13..0.15 rows=1 width=8) (actual time=0.025..0.025 rows=0 loops=2)
     242                                  Index Cond: (id = cmt.language_id)
     243                                  Filter: ((language_code)::text = 'EN'::text)
     244                                  Rows Removed by Filter: 0
     245          SubPlan 1
     246            ->  Aggregate  (cost=404813.94..404813.95 rows=1 width=32) (actual time=3063.568..3063.568 rows=1 loops=25)
     247                  ->  Sort  (cost=404813.88..404813.90 rows=7 width=193) (actual time=3063.504..3063.505 rows=4 loops=25)
     248"                        Sort Key: cl.""position"""
     249                        Sort Method: quicksort  Memory: 25kB
     250                        ->  Nested Loop  (cost=108824.22..404813.79 rows=7 width=193) (actual time=2987.346..3063.480 rows=4 loops=25)
     251                              ->  Hash Join  (cost=108824.09..404813.02 rows=14 width=201) (actual time=2987.324..3063.453 rows=8 loops=25)
     252                                    Hash Cond: (clt.course_lecture_id = cl.id)
     253                                    ->  Seq Scan on course_lecture_translation clt  (cost=0.00..274988.72 rows=8000072 width=188) (actual time=0.028..1633.078 rows=8000000 loops=25)
     254                                    ->  Hash  (cost=108824.00..108824.00 rows=7 width=21) (actual time=470.933..470.933 rows=4 loops=25)
     255                                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
     256                                          ->  Seq Scan on course_lecture cl  (cost=0.00..108824.00 rows=7 width=21) (actual time=131.142..470.914 rows=4 loops=25)
     257                                                Filter: (course_module_id = cm.id)
     258                                                Rows Removed by Filter: 3999996
     259                              ->  Memoize  (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=200)
     260                                    Cache Key: clt.language_id
     261                                    Cache Mode: logical
     262                                    Hits: 198  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     263                                    ->  Index Scan using pk_language on language l2  (cost=0.13..0.15 rows=1 width=8) (actual time=0.028..0.029 rows=0 loops=2)
     264                                          Index Cond: (id = clt.language_id)
     265                                          Filter: ((language_code)::text = 'EN'::text)
     266                                          Rows Removed by Filter: 0
     267Planning Time: 2.190 ms
     268JIT:
     269  Functions: 190
     270"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
     271"  Timing: Generation 17.924 ms (Deform 10.521 ms), Inlining 420.064 ms, Optimization 1400.185 ms, Emission 954.207 ms, Total 2792.381 ms"
     272Execution Time: 80219.048 ms
     273
     274}}}
     275
     276Со додавање на индекси за надворешните клучеви и филтрирањето
     277
     278{{{
     279-- од поглед бр.1
     280create index idx_enrollment_user_id on enrollment(user_id);
     281create index idx_enrollment_course_version_id on enrollment(course_version_id);
     282create index idx_course_version_course_id on course_version(course_id);
     283create index idx_course_translation_course_id on course_translation(course_id);
     284create index idx_course_translation_language_id on course_translation(language_id);
     285create index idx_language_language_code on language(language_code);
     286
     287-- нови
     288create index idx_course_module_course_version_id on course_module(course_version_id);
     289create index idx_course_module_translation_course_module_id on course_module_translation(course_module_id);
     290create index idx_course_module_translation_language_id on course_module_translation(language_id);
     291create index idx_course_module_position on course_module(position);
     292
     293create index idx_course_lecture_position on course_lecture(position);
     294create index idx_course_lecture_translation_course_lecture_id on course_lecture_translation(course_lecture_id);
     295create index idx_course_lecture_translation_language_id on course_lecture_translation(language_id);
     296create index idx_course_lecture_course_module_id on course_lecture(course_module_id);
     297}}}
     298
     299Перформансите значително се подобруваат. Сега Execution Time: 2.383 ms
     300
     301{{{
     302Nested Loop  (cost=1.57..2514.26 rows=6 width=652) (actual time=0.630..2.227 rows=5 loops=1)
     303  ->  Nested Loop  (cost=1.27..84.57 rows=6 width=564) (actual time=0.064..0.138 rows=5 loops=1)
     304        Join Filter: (ct.language_id = l.id)
     305        Rows Removed by Join Filter: 5
     306        ->  Seq Scan on language l  (cost=0.00..1.02 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1)
     307              Filter: ((language_code)::text = 'EN'::text)
     308              Rows Removed by Filter: 1
     309        ->  Nested Loop  (cost=1.27..83.40 rows=12 width=572) (actual time=0.039..0.109 rows=10 loops=1)
     310              ->  Nested Loop  (cost=0.85..78.35 rows=6 width=33) (actual time=0.029..0.068 rows=5 loops=1)
     311                    ->  Index Scan using idx_enrollment_user_id on enrollment e  (cost=0.43..27.72 rows=6 width=16) (actual time=0.017..0.029 rows=5 loops=1)
     312                          Index Cond: (user_id = 1011)
     313                    ->  Index Scan using pk_course_version on course_version cv  (cost=0.42..8.44 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=5)
     314                          Index Cond: (id = e.course_version_id)
     315              ->  Index Scan using idx_course_translation_course_id on course_translation ct  (cost=0.42..0.82 rows=2 width=539) (actual time=0.005..0.006 rows=2 loops=5)
     316                    Index Cond: (course_id = cv.course_id)
     317  ->  Index Scan using pk_course on course c  (cost=0.29..0.35 rows=1 width=80) (actual time=0.004..0.004 rows=1 loops=5)
     318        Index Cond: (id = cv.course_id)
     319  SubPlan 2
     320    ->  Aggregate  (cost=404.59..404.60 rows=1 width=32) (actual time=0.409..0.409 rows=1 loops=5)
     321          ->  Sort  (cost=52.13..52.15 rows=5 width=33) (actual time=0.060..0.061 rows=5 loops=5)
     322"                Sort Key: cm.""position"""
     323                Sort Method: quicksort  Memory: 25kB
     324                ->  Nested Loop  (cost=0.85..52.07 rows=5 width=33) (actual time=0.027..0.047 rows=5 loops=5)
     325                      Join Filter: (cmt.language_id = l1.id)
     326                      Rows Removed by Join Filter: 5
     327                      ->  Seq Scan on language l1  (cost=0.00..1.02 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5)
     328                            Filter: ((language_code)::text = 'EN'::text)
     329                            Rows Removed by Filter: 1
     330                      ->  Nested Loop  (cost=0.85..50.92 rows=10 width=41) (actual time=0.021..0.039 rows=10 loops=5)
     331                            ->  Index Scan using idx_course_module_course_version_id on course_module cm  (cost=0.42..8.51 rows=5 width=12) (actual time=0.013..0.018 rows=5 loops=5)
     332                                  Index Cond: (course_version_id = cv.id)
     333                            ->  Index Scan using idx_course_module_translation_course_module_id on course_module_translation cmt  (cost=0.43..8.46 rows=2 width=37) (actual time=0.003..0.003 rows=2 loops=25)
     334                                  Index Cond: (course_module_id = cm.id)
     335          SubPlan 1
     336            ->  Aggregate  (cost=70.47..70.48 rows=1 width=32) (actual time=0.063..0.063 rows=1 loops=25)
     337                  ->  Sort  (cost=70.42..70.44 rows=7 width=193) (actual time=0.037..0.037 rows=4 loops=25)
     338"                        Sort Key: cl.""position"""
     339                        Sort Method: quicksort  Memory: 25kB
     340                        ->  Nested Loop  (cost=0.86..70.32 rows=7 width=193) (actual time=0.015..0.028 rows=4 loops=25)
     341                              Join Filter: (clt.language_id = l2.id)
     342                              Rows Removed by Join Filter: 4
     343                              ->  Seq Scan on language l2  (cost=0.00..1.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=25)
     344                                    Filter: ((language_code)::text = 'EN'::text)
     345                                    Rows Removed by Filter: 1
     346                              ->  Nested Loop  (cost=0.86..69.12 rows=14 width=201) (actual time=0.011..0.023 rows=8 loops=25)
     347                                    ->  Index Scan using idx_course_lecture_course_module_id on course_lecture cl  (cost=0.43..8.55 rows=7 width=21) (actual time=0.006..0.007 rows=4 loops=25)
     348                                          Index Cond: (course_module_id = cm.id)
     349                                    ->  Index Scan using idx_course_lecture_translation_course_lecture_id on course_lecture_translation clt  (cost=0.43..8.57 rows=8 width=188) (actual time=0.002..0.003 rows=2 loops=100)
     350                                          Index Cond: (course_lecture_id = cl.id)
     351Planning Time: 3.695 ms
     352Execution Time: 2.383 ms
     353}}}
     354
     355
     356== 3. Анализа на поглед бр.3 - best_selling_courses: превземање на топ 10 најпопуларни курсеви според бројот на записи од страна на корисници
     357
     358{{{
     359select * from best_selling_courses;
     360}}}
     361
     362Со explain analyze добиваме Execution Time: 21344.417 ms (~21s), што е прилично бавно и може да се подобри со индекси.
     363
     364{{{
     365Subquery Scan on best_selling_courses  (cost=2168880.56..2168880.68 rows=10 width=53) (actual time=20634.170..20634.199 rows=10 loops=1)
     366  ->  Limit  (cost=2168880.56..2168880.58 rows=10 width=61) (actual time=20634.117..20634.142 rows=10 loops=1)
     367        ->  Sort  (cost=2168880.56..2193880.56 rows=10000000 width=61) (actual time=20138.428..20138.452 rows=10 loops=1)
     368              Sort Key: (count(e.user_id)) DESC
     369              Sort Method: top-N heapsort  Memory: 26kB
     370              ->  HashAggregate  (cost=1637940.40..1952784.15 rows=10000000 width=61) (actual time=18065.219..20110.452 rows=100000 loops=1)
     371"                    Group Key: c.id, ct.id"
     372                    Planned Partitions: 256  Batches: 257  Memory Usage: 8209kB  Disk Usage: 768104kB
     373                    ->  Hash Join  (cost=41258.40..503565.40 rows=10000000 width=61) (actual time=536.919..9632.587 rows=10000000 loops=1)
     374                          Hash Cond: (e.course_version_id = cv.id)
     375                          ->  Seq Scan on enrollment e  (cost=0.00..225000.00 rows=10000000 width=16) (actual time=0.050..2026.103 rows=10000000 loops=1)
     376                          ->  Hash  (cost=36609.40..36609.40 rows=200000 width=61) (actual time=534.827..534.847 rows=200000 loops=1)
     377                                Buckets: 131072  Batches: 4  Memory Usage: 6028kB
     378                                ->  Hash Join  (cost=27168.54..36609.40 rows=200000 width=61) (actual time=233.822..434.399 rows=200000 loops=1)
     379                                      Hash Cond: (cv.course_id = c.id)
     380                                      ->  Hash Join  (cost=23515.54..32439.94 rows=196740 width=56) (actual time=187.289..324.064 rows=200000 loops=1)
     381                                            Hash Cond: (cv.course_id = ct.course_id)
     382                                            ->  Seq Scan on course_version cv  (cost=0.00..3471.00 rows=200000 width=16) (actual time=0.062..30.864 rows=200000 loops=1)
     383                                            ->  Hash  (cost=21483.54..21483.54 rows=100000 width=40) (actual time=186.172..186.178 rows=100000 loops=1)
     384                                                  Buckets: 131072  Batches: 2  Memory Usage: 4746kB
     385                                                  ->  Hash Join  (cost=1.04..21483.54 rows=100000 width=40) (actual time=0.100..145.784 rows=100000 loops=1)
     386                                                        Hash Cond: (ct.language_id = l.id)
     387                                                        ->  Seq Scan on course_translation ct  (cost=0.00..19845.00 rows=200000 width=48) (actual time=0.019..57.498 rows=200000 loops=1)
     388                                                        ->  Hash  (cost=1.02..1.02 rows=1 width=8) (actual time=0.046..0.048 rows=1 loops=1)
     389                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB
     390                                                              ->  Seq Scan on language l  (cost=0.00..1.02 rows=1 width=8) (actual time=0.035..0.037 rows=1 loops=1)
     391                                                                    Filter: ((language_code)::text = 'EN'::text)
     392                                                                    Rows Removed by Filter: 1
     393                                      ->  Hash  (cost=2403.00..2403.00 rows=100000 width=21) (actual time=46.286..46.288 rows=100000 loops=1)
     394                                            Buckets: 131072  Batches: 1  Memory Usage: 6493kB
     395                                            ->  Seq Scan on course c  (cost=0.00..2403.00 rows=100000 width=21) (actual time=0.039..23.232 rows=100000 loops=1)
     396Planning Time: 1.790 ms
     397JIT:
     398  Functions: 46
     399"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
     400"  Timing: Generation 4.220 ms (Deform 2.226 ms), Inlining 26.501 ms, Optimization 326.082 ms, Emission 212.054 ms, Total 568.857 ms"
     401Execution Time: 21344.417 ms
     402}}}
     403
     404Со додавање на индекси за надворешните клучеви и филтрирањето
     405
     406{{{
     407-- од поглед бр.1
     408create index idx_enrollment_user_id on enrollment(user_id);
     409create index idx_enrollment_course_version_id on enrollment(course_version_id);
     410create index idx_course_version_course_id on course_version(course_id);
     411create index idx_course_translation_course_id on course_translation(course_id);
     412create index idx_course_translation_language_id on course_translation(language_id);
     413create index idx_language_language_code on language(language_code);
     414
     415}}}
     416
     417Перформансите значително се подобруваат. Сега Execution Time: 9719.608 ms (~10s), што е подобро од верзијата без индекси ама сепак е споро. Причината за ваквото споро време е поради агрегатната фунцкија count(e.user_id) која се извршува цели 9 секунди.
     418{{{
     419->  GroupAggregate  (cost=1003.23..1404183.71 rows=10000000 width=61) (actual time=321.012..9312.891 rows=100000 loops=1)
     420}}}
     421
     422Ова може да се подобри со materialized view, каде во некои случаеви би добивале застарени податоци, меѓутоа тоа и не е дотолку битно бидејќи се работи за податоци кои не мора да се толку точни (еднаш еден курс ќе биде на прво место а друг пат на второ, тоа ниту на нас ниту на корисникот ништо не менува)
     423
     424{{{
     425Subquery Scan on best_selling_courses  (cost=1620280.12..1620280.24 rows=10 width=53) (actual time=9716.416..9716.569 rows=10 loops=1)
     426  ->  Limit  (cost=1620280.12..1620280.14 rows=10 width=61) (actual time=9716.357..9716.507 rows=10 loops=1)
     427        ->  Sort  (cost=1620280.12..1645280.12 rows=10000000 width=61) (actual time=9363.579..9363.728 rows=10 loops=1)
     428              Sort Key: (count(e.user_id)) DESC
     429              Sort Method: top-N heapsort  Memory: 26kB
     430              ->  GroupAggregate  (cost=1003.23..1404183.71 rows=10000000 width=61) (actual time=321.012..9312.891 rows=100000 loops=1)
     431"                    Group Key: c.id, ct.id"
     432                    ->  Nested Loop  (cost=1003.23..1229183.71 rows=10000000 width=61) (actual time=319.703..8521.227 rows=10000000 loops=1)
     433                          ->  Merge Join  (cost=1002.80..53219.71 rows=200000 width=61) (actual time=319.654..606.596 rows=200000 loops=1)
     434                                Merge Cond: (c.id = cv.course_id)
     435                                ->  Gather Merge  (cost=1002.17..44040.03 rows=100000 width=61) (actual time=319.556..451.264 rows=100000 loops=1)
     436                                      Workers Planned: 2
     437                                      Workers Launched: 2
     438                                      ->  Incremental Sort  (cost=2.14..31497.53 rows=41667 width=61) (actual time=180.863..379.512 rows=33333 loops=3)
     439"                                            Sort Key: c.id, ct.id"
     440                                            Presorted Key: c.id
     441                                            Full-sort Groups: 360  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
     442                                            Worker 0:  Full-sort Groups: 1383  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
     443                                            Worker 1:  Full-sort Groups: 1383  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
     444                                            ->  Merge Join  (cost=1.42..29622.51 rows=41667 width=61) (actual time=180.545..364.976 rows=33333 loops=3)
     445                                                  Merge Cond: (ct.course_id = c.id)
     446                                                  ->  Nested Loop  (cost=0.56..23753.52 rows=41667 width=40) (actual time=180.308..310.795 rows=33333 loops=3)
     447                                                        ->  Parallel Index Scan using idx_course_translation_course_id on course_translation ct  (cost=0.42..21669.75 rows=83333 width=48) (actual time=0.135..92.542 rows=66667 loops=3)
     448                                                        ->  Memoize  (cost=0.14..0.16 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=200000)
     449                                                              Cache Key: ct.language_id
     450                                                              Cache Mode: logical
     451                                                              Hits: 22982  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     452                                                              Worker 0:  Hits: 88506  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     453                                                              Worker 1:  Hits: 88506  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     454                                                              ->  Index Scan using pk_language on language l  (cost=0.13..0.15 rows=1 width=8) (actual time=0.052..0.052 rows=0 loops=6)
     455                                                                    Index Cond: (id = ct.language_id)
     456                                                                    Filter: ((language_code)::text = 'EN'::text)
     457                                                                    Rows Removed by Filter: 0
     458                                                  ->  Index Scan using pk_course on course c  (cost=0.29..5102.29 rows=100000 width=21) (actual time=0.063..38.005 rows=99647 loops=3)
     459                                ->  Index Scan using idx_course_version_course_id on course_version cv  (cost=0.42..6462.42 rows=200000 width=16) (actual time=0.034..81.090 rows=200000 loops=1)
     460                          ->  Index Scan using idx_enrollment_course_version_id on enrollment e  (cost=0.43..4.87 rows=101 width=16) (actual time=0.003..0.033 rows=50 loops=200000)
     461                                Index Cond: (course_version_id = cv.id)
     462Planning Time: 3.035 ms
     463JIT:
     464  Functions: 67
     465"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
     466"  Timing: Generation 5.474 ms (Deform 2.745 ms), Inlining 268.779 ms, Optimization 377.307 ms, Emission 247.104 ms, Total 898.663 ms"
     467Execution Time: 9719.608 ms
     468}}}
     469
     470