= Оптимизација на прашалници == 1. Анализа на поглед бр.1 - enrolled_courses: превземање на сите запишани курсеви за секој корисник За конкретен корисник: {{{ select * from enrolled_courses where user_id = 1011; }}} Со explain analyze добиваме Execution Time: 770.782 ms (~0.7s), што е прилично бавно и може да се подобри со индекси. {{{ Gather (cost=157267.74..176759.03 rows=6 width=185) (actual time=760.556..764.927 rows=5 loops=1) Workers Planned: 3 Workers Launched: 3 -> Nested Loop (cost=156267.74..175000.17 rows=2 width=121) (actual time=596.006..606.610 rows=1 loops=4) -> Parallel Hash Join (cost=156267.60..174999.70 rows=4 width=129) (actual time=595.962..606.558 rows=2 loops=4) Hash Cond: (ct.course_id = c.id) -> 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) -> Parallel Hash (cost=156267.57..156267.57 rows=2 width=105) (actual time=563.722..563.727 rows=1 loops=4) Buckets: 1024 Batches: 1 Memory Usage: 72kB -> Nested Loop (cost=0.71..156267.57 rows=2 width=105) (actual time=404.840..563.574 rows=1 loops=4) -> Nested Loop (cost=0.42..156266.88 rows=2 width=25) (actual time=404.809..563.530 rows=1 loops=4) -> 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) Filter: (user_id = 1011) Rows Removed by Filter: 2499999 -> 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) Index Cond: (id = e.course_version_id) -> 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) Index Cond: (id = cv.course_id) -> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=10) Cache Key: ct.language_id Cache Mode: logical Worker 0: Hits: 8 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = ct.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 SubPlan 1 -> Aggregate (cost=79.28..79.29 rows=1 width=32) (actual time=11.716..11.716 rows=1 loops=5) -> Sort (cost=79.27..79.27 rows=2 width=18) (actual time=11.696..11.697 rows=2 loops=5) Sort Key: st.name Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=10.51..79.26 rows=2 width=18) (actual time=0.110..11.683 rows=2 loops=5) Join Filter: (s.id = cs.skill_id) -> Nested Loop (cost=10.23..78.58 rows=2 width=34) (actual time=0.089..11.647 rows=2 loops=5) Join Filter: (l_1.id = st.language_id) Rows Removed by Join Filter: 2 -> 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) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Hash Join (cost=10.23..77.50 rows=4 width=42) (actual time=0.070..11.625 rows=4 loops=5) Hash Cond: (st.skill_id = cs.skill_id) -> 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) -> Hash (cost=10.21..10.21 rows=2 width=8) (actual time=0.048..0.048 rows=2 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> 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) Index Cond: (course_id = c.id) Heap Fetches: 10 -> 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) Index Cond: (id = st.skill_id) Heap Fetches: 10 SubPlan 2 -> Aggregate (cost=47.08..47.09 rows=1 width=32) (actual time=11.069..11.070 rows=1 loops=5) -> Sort (cost=47.07..47.07 rows=2 width=17) (actual time=11.058..11.059 rows=2 loops=5) Sort Key: tt.name Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=10.64..47.06 rows=2 width=17) (actual time=0.097..11.051 rows=2 loops=5) Join Filter: (t.id = ct_1.topic_id) -> Nested Loop (cost=10.37..46.37 rows=2 width=33) (actual time=0.084..11.028 rows=2 loops=5) -> Hash Join (cost=10.23..45.88 rows=4 width=41) (actual time=0.063..10.994 rows=4 loops=5) Hash Cond: (tt.topic_id = ct_1.topic_id) -> 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) -> Hash (cost=10.21..10.21 rows=2 width=8) (actual time=0.040..0.040 rows=2 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> 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) Index Cond: (course_id = c.id) Heap Fetches: 10 -> Memoize (cost=0.14..0.17 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=20) Cache Key: tt.language_id Cache Mode: logical Hits: 18 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = tt.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 -> 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) Index Cond: (id = tt.topic_id) Heap Fetches: 10 Planning Time: 2.845 ms JIT: Functions: 189 " Options: Inlining false, Optimization false, Expressions true, Deforming true" " 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" Execution Time: 770.782 ms }}} Со додавање на индекси за надворешните клучеви и филтрирањето {{{ create index idx_enrollment_user_id on enrollment(user_id); create index idx_enrollment_course_version_id on enrollment(course_version_id); create index idx_course_version_course_id on course_version(course_id); create index idx_course_translation_course_id on course_translation(course_id); create index idx_course_translation_language_id on course_translation(language_id); create index idx_language_language_code on language(language_code); }}} Перформансите значително се подобруваат. Сега Execution Time: 5.147 ms {{{ Nested Loop (cost=1.57..844.92 rows=6 width=185) (actual time=1.713..4.957 rows=5 loops=1) -> Nested Loop (cost=1.27..84.57 rows=6 width=57) (actual time=0.102..0.263 rows=5 loops=1) Join Filter: (ct.language_id = l.id) Rows Removed by Join Filter: 5 -> Seq Scan on language l (cost=0.00..1.02 rows=1 width=8) (actual time=0.021..0.023 rows=1 loops=1) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Nested Loop (cost=1.27..83.40 rows=12 width=65) (actual time=0.078..0.232 rows=10 loops=1) -> Nested Loop (cost=0.85..78.35 rows=6 width=25) (actual time=0.068..0.182 rows=5 loops=1) -> 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) Index Cond: (user_id = 1011) -> 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) Index Cond: (id = e.course_version_id) -> 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) Index Cond: (course_id = cv.course_id) -> 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) Index Cond: (id = cv.course_id) SubPlan 1 -> Aggregate (cost=79.28..79.29 rows=1 width=32) (actual time=0.601..0.602 rows=1 loops=5) -> Sort (cost=79.27..79.27 rows=2 width=18) (actual time=0.594..0.595 rows=2 loops=5) Sort Key: st.name Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=10.51..79.26 rows=2 width=18) (actual time=0.058..0.585 rows=2 loops=5) Join Filter: (s.id = cs.skill_id) -> Nested Loop (cost=10.23..78.58 rows=2 width=34) (actual time=0.047..0.563 rows=2 loops=5) Join Filter: (l_1.id = st.language_id) Rows Removed by Join Filter: 2 -> 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) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Hash Join (cost=10.23..77.50 rows=4 width=42) (actual time=0.037..0.549 rows=4 loops=5) Hash Cond: (st.skill_id = cs.skill_id) -> 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) -> Hash (cost=10.21..10.21 rows=2 width=8) (actual time=0.023..0.023 rows=2 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> 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) Index Cond: (course_id = c.id) Heap Fetches: 10 -> 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) Index Cond: (id = st.skill_id) Heap Fetches: 10 SubPlan 2 -> Aggregate (cost=47.08..47.09 rows=1 width=32) (actual time=0.326..0.326 rows=1 loops=5) -> Sort (cost=47.07..47.07 rows=2 width=17) (actual time=0.321..0.321 rows=2 loops=5) Sort Key: tt.name Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=10.64..47.06 rows=2 width=17) (actual time=0.056..0.315 rows=2 loops=5) Join Filter: (t.id = ct_1.topic_id) -> Nested Loop (cost=10.37..46.37 rows=2 width=33) (actual time=0.050..0.302 rows=2 loops=5) -> Hash Join (cost=10.23..45.88 rows=4 width=41) (actual time=0.038..0.287 rows=4 loops=5) Hash Cond: (tt.topic_id = ct_1.topic_id) -> 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) -> Hash (cost=10.21..10.21 rows=2 width=8) (actual time=0.023..0.023 rows=2 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> 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) Index Cond: (course_id = c.id) Heap Fetches: 10 -> Memoize (cost=0.14..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=20) Cache Key: tt.language_id Cache Mode: logical Hits: 18 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = tt.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 -> 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) Index Cond: (id = tt.topic_id) Heap Fetches: 10 Planning Time: 4.031 ms Execution Time: 5.147 ms }}} == 2. Анализа на поглед бр.2 - enrolled_course_details: превземање на детални информации за запишан курс За конкретен корисник: {{{ select * from enrolled_course_details where user_id = 1011; }}} Со explain analyze добиваме Execution Time: 80219.048 ms (~80s), што е прилично бавно и може да се подобри со индекси.. {{{ Gather (cost=157267.74..12708954.33 rows=6 width=652) (actual time=16970.606..80212.303 rows=5 loops=1) Workers Planned: 3 Workers Launched: 3 -> Nested Loop (cost=156267.74..175000.17 rows=2 width=628) (actual time=1086.263..1099.974 rows=1 loops=4) -> Parallel Hash Join (cost=156267.60..174999.70 rows=4 width=636) (actual time=1086.215..1099.918 rows=2 loops=4) Hash Cond: (ct.course_id = c.id) -> 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) -> Parallel Hash (cost=156267.57..156267.57 rows=2 width=113) (actual time=1044.613..1044.617 rows=1 loops=4) Buckets: 1024 Batches: 1 Memory Usage: 104kB -> Nested Loop (cost=0.71..156267.57 rows=2 width=113) (actual time=888.499..1044.464 rows=1 loops=4) -> Nested Loop (cost=0.42..156266.88 rows=2 width=33) (actual time=888.448..1044.403 rows=1 loops=4) -> 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) Filter: (user_id = 1011) Rows Removed by Filter: 2499999 -> 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) Index Cond: (id = e.course_version_id) -> 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) Index Cond: (id = cv.course_id) -> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=10) Cache Key: ct.language_id Cache Mode: logical Worker 0: Hits: 8 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = ct.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 SubPlan 2 -> Aggregate (cost=2088825.58..2088825.59 rows=1 width=32) (actual time=15814.049..15814.051 rows=1 loops=5) -> Sort (cost=64755.80..64755.81 rows=5 width=33) (actual time=496.073..496.080 rows=5 loops=5) " Sort Key: cm.""position""" Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=18870.20..64755.74 rows=5 width=33) (actual time=77.181..496.050 rows=5 loops=5) -> Hash Join (cost=18870.06..64755.08 rows=10 width=41) (actual time=77.149..496.010 rows=10 loops=5) Hash Cond: (cmt.course_module_id = cm.id) -> 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) -> Hash (cost=18870.00..18870.00 rows=5 width=12) (actual time=77.102..77.102 rows=5 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> 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) Filter: (course_version_id = cv.id) Rows Removed by Filter: 999995 -> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=50) Cache Key: cmt.language_id Cache Mode: logical Hits: 48 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = cmt.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 SubPlan 1 -> Aggregate (cost=404813.94..404813.95 rows=1 width=32) (actual time=3063.568..3063.568 rows=1 loops=25) -> Sort (cost=404813.88..404813.90 rows=7 width=193) (actual time=3063.504..3063.505 rows=4 loops=25) " Sort Key: cl.""position""" Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=108824.22..404813.79 rows=7 width=193) (actual time=2987.346..3063.480 rows=4 loops=25) -> Hash Join (cost=108824.09..404813.02 rows=14 width=201) (actual time=2987.324..3063.453 rows=8 loops=25) Hash Cond: (clt.course_lecture_id = cl.id) -> 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) -> Hash (cost=108824.00..108824.00 rows=7 width=21) (actual time=470.933..470.933 rows=4 loops=25) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> 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) Filter: (course_module_id = cm.id) Rows Removed by Filter: 3999996 -> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=200) Cache Key: clt.language_id Cache Mode: logical Hits: 198 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = clt.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 Planning Time: 2.190 ms JIT: Functions: 190 " Options: Inlining true, Optimization true, Expressions true, Deforming true" " 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" Execution Time: 80219.048 ms }}} Со додавање на индекси за надворешните клучеви и филтрирањето {{{ -- од поглед бр.1 create index idx_enrollment_user_id on enrollment(user_id); create index idx_enrollment_course_version_id on enrollment(course_version_id); create index idx_course_version_course_id on course_version(course_id); create index idx_course_translation_course_id on course_translation(course_id); create index idx_course_translation_language_id on course_translation(language_id); create index idx_language_language_code on language(language_code); -- нови create index idx_course_module_course_version_id on course_module(course_version_id); create index idx_course_module_translation_course_module_id on course_module_translation(course_module_id); create index idx_course_module_translation_language_id on course_module_translation(language_id); create index idx_course_module_position on course_module(position); create index idx_course_lecture_position on course_lecture(position); create index idx_course_lecture_translation_course_lecture_id on course_lecture_translation(course_lecture_id); create index idx_course_lecture_translation_language_id on course_lecture_translation(language_id); create index idx_course_lecture_course_module_id on course_lecture(course_module_id); }}} Перформансите значително се подобруваат. Сега Execution Time: 2.383 ms {{{ Nested Loop (cost=1.57..2514.26 rows=6 width=652) (actual time=0.630..2.227 rows=5 loops=1) -> Nested Loop (cost=1.27..84.57 rows=6 width=564) (actual time=0.064..0.138 rows=5 loops=1) Join Filter: (ct.language_id = l.id) Rows Removed by Join Filter: 5 -> Seq Scan on language l (cost=0.00..1.02 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Nested Loop (cost=1.27..83.40 rows=12 width=572) (actual time=0.039..0.109 rows=10 loops=1) -> Nested Loop (cost=0.85..78.35 rows=6 width=33) (actual time=0.029..0.068 rows=5 loops=1) -> 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) Index Cond: (user_id = 1011) -> 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) Index Cond: (id = e.course_version_id) -> 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) Index Cond: (course_id = cv.course_id) -> 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) Index Cond: (id = cv.course_id) SubPlan 2 -> Aggregate (cost=404.59..404.60 rows=1 width=32) (actual time=0.409..0.409 rows=1 loops=5) -> Sort (cost=52.13..52.15 rows=5 width=33) (actual time=0.060..0.061 rows=5 loops=5) " Sort Key: cm.""position""" Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.85..52.07 rows=5 width=33) (actual time=0.027..0.047 rows=5 loops=5) Join Filter: (cmt.language_id = l1.id) Rows Removed by Join Filter: 5 -> Seq Scan on language l1 (cost=0.00..1.02 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Nested Loop (cost=0.85..50.92 rows=10 width=41) (actual time=0.021..0.039 rows=10 loops=5) -> 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) Index Cond: (course_version_id = cv.id) -> 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) Index Cond: (course_module_id = cm.id) SubPlan 1 -> Aggregate (cost=70.47..70.48 rows=1 width=32) (actual time=0.063..0.063 rows=1 loops=25) -> Sort (cost=70.42..70.44 rows=7 width=193) (actual time=0.037..0.037 rows=4 loops=25) " Sort Key: cl.""position""" Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.86..70.32 rows=7 width=193) (actual time=0.015..0.028 rows=4 loops=25) Join Filter: (clt.language_id = l2.id) Rows Removed by Join Filter: 4 -> Seq Scan on language l2 (cost=0.00..1.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=25) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Nested Loop (cost=0.86..69.12 rows=14 width=201) (actual time=0.011..0.023 rows=8 loops=25) -> 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) Index Cond: (course_module_id = cm.id) -> 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) Index Cond: (course_lecture_id = cl.id) Planning Time: 3.695 ms Execution Time: 2.383 ms }}} == 3. Анализа на поглед бр.3 - best_selling_courses: превземање на топ 10 најпопуларни курсеви според бројот на записи од страна на корисници {{{ select * from best_selling_courses; }}} Со explain analyze добиваме Execution Time: 21344.417 ms (~21s), што е прилично бавно и може да се подобри со индекси. {{{ Subquery Scan on best_selling_courses (cost=2168880.56..2168880.68 rows=10 width=53) (actual time=20634.170..20634.199 rows=10 loops=1) -> Limit (cost=2168880.56..2168880.58 rows=10 width=61) (actual time=20634.117..20634.142 rows=10 loops=1) -> Sort (cost=2168880.56..2193880.56 rows=10000000 width=61) (actual time=20138.428..20138.452 rows=10 loops=1) Sort Key: (count(e.user_id)) DESC Sort Method: top-N heapsort Memory: 26kB -> HashAggregate (cost=1637940.40..1952784.15 rows=10000000 width=61) (actual time=18065.219..20110.452 rows=100000 loops=1) " Group Key: c.id, ct.id" Planned Partitions: 256 Batches: 257 Memory Usage: 8209kB Disk Usage: 768104kB -> Hash Join (cost=41258.40..503565.40 rows=10000000 width=61) (actual time=536.919..9632.587 rows=10000000 loops=1) Hash Cond: (e.course_version_id = cv.id) -> Seq Scan on enrollment e (cost=0.00..225000.00 rows=10000000 width=16) (actual time=0.050..2026.103 rows=10000000 loops=1) -> Hash (cost=36609.40..36609.40 rows=200000 width=61) (actual time=534.827..534.847 rows=200000 loops=1) Buckets: 131072 Batches: 4 Memory Usage: 6028kB -> Hash Join (cost=27168.54..36609.40 rows=200000 width=61) (actual time=233.822..434.399 rows=200000 loops=1) Hash Cond: (cv.course_id = c.id) -> Hash Join (cost=23515.54..32439.94 rows=196740 width=56) (actual time=187.289..324.064 rows=200000 loops=1) Hash Cond: (cv.course_id = ct.course_id) -> 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) -> Hash (cost=21483.54..21483.54 rows=100000 width=40) (actual time=186.172..186.178 rows=100000 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 4746kB -> Hash Join (cost=1.04..21483.54 rows=100000 width=40) (actual time=0.100..145.784 rows=100000 loops=1) Hash Cond: (ct.language_id = l.id) -> 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) -> Hash (cost=1.02..1.02 rows=1 width=8) (actual time=0.046..0.048 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on language l (cost=0.00..1.02 rows=1 width=8) (actual time=0.035..0.037 rows=1 loops=1) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 1 -> Hash (cost=2403.00..2403.00 rows=100000 width=21) (actual time=46.286..46.288 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 6493kB -> Seq Scan on course c (cost=0.00..2403.00 rows=100000 width=21) (actual time=0.039..23.232 rows=100000 loops=1) Planning Time: 1.790 ms JIT: Functions: 46 " Options: Inlining true, Optimization true, Expressions true, Deforming true" " 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" Execution Time: 21344.417 ms }}} Со додавање на индекси за надворешните клучеви и филтрирањето {{{ -- од поглед бр.1 create index idx_enrollment_user_id on enrollment(user_id); create index idx_enrollment_course_version_id on enrollment(course_version_id); create index idx_course_version_course_id on course_version(course_id); create index idx_course_translation_course_id on course_translation(course_id); create index idx_course_translation_language_id on course_translation(language_id); create index idx_language_language_code on language(language_code); }}} Перформансите значително се подобруваат. Сега Execution Time: 9719.608 ms (~10s), што е подобро од верзијата без индекси ама сепак е споро. Причината за ваквото споро време е поради агрегатната фунцкија count(e.user_id) која се извршува цели 9 секунди. {{{ -> GroupAggregate (cost=1003.23..1404183.71 rows=10000000 width=61) (actual time=321.012..9312.891 rows=100000 loops=1) }}} Ова може да се подобри со materialized view, каде во некои случаеви би добивале застарени податоци, меѓутоа тоа и не е дотолку битно бидејќи се работи за податоци кои не мора да се толку точни (еднаш еден курс ќе биде на прво место а друг пат на второ, тоа ниту на нас ниту на корисникот ништо не менува) {{{ Subquery Scan on best_selling_courses (cost=1620280.12..1620280.24 rows=10 width=53) (actual time=9716.416..9716.569 rows=10 loops=1) -> Limit (cost=1620280.12..1620280.14 rows=10 width=61) (actual time=9716.357..9716.507 rows=10 loops=1) -> Sort (cost=1620280.12..1645280.12 rows=10000000 width=61) (actual time=9363.579..9363.728 rows=10 loops=1) Sort Key: (count(e.user_id)) DESC Sort Method: top-N heapsort Memory: 26kB -> GroupAggregate (cost=1003.23..1404183.71 rows=10000000 width=61) (actual time=321.012..9312.891 rows=100000 loops=1) " Group Key: c.id, ct.id" -> Nested Loop (cost=1003.23..1229183.71 rows=10000000 width=61) (actual time=319.703..8521.227 rows=10000000 loops=1) -> Merge Join (cost=1002.80..53219.71 rows=200000 width=61) (actual time=319.654..606.596 rows=200000 loops=1) Merge Cond: (c.id = cv.course_id) -> Gather Merge (cost=1002.17..44040.03 rows=100000 width=61) (actual time=319.556..451.264 rows=100000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Incremental Sort (cost=2.14..31497.53 rows=41667 width=61) (actual time=180.863..379.512 rows=33333 loops=3) " Sort Key: c.id, ct.id" Presorted Key: c.id Full-sort Groups: 360 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB Worker 0: Full-sort Groups: 1383 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB Worker 1: Full-sort Groups: 1383 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB -> Merge Join (cost=1.42..29622.51 rows=41667 width=61) (actual time=180.545..364.976 rows=33333 loops=3) Merge Cond: (ct.course_id = c.id) -> Nested Loop (cost=0.56..23753.52 rows=41667 width=40) (actual time=180.308..310.795 rows=33333 loops=3) -> 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) -> Memoize (cost=0.14..0.16 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=200000) Cache Key: ct.language_id Cache Mode: logical Hits: 22982 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB Worker 0: Hits: 88506 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB Worker 1: Hits: 88506 Misses: 2 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> 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) Index Cond: (id = ct.language_id) Filter: ((language_code)::text = 'EN'::text) Rows Removed by Filter: 0 -> 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) -> 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) -> 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) Index Cond: (course_version_id = cv.id) Planning Time: 3.035 ms JIT: Functions: 67 " Options: Inlining true, Optimization true, Expressions true, Deforming true" " 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" Execution Time: 9719.608 ms }}}