| Version 4 (modified by , 2 weeks ago) ( diff ) |
|---|
Оптимизација на прашалници
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), што е подобро од верзијата без индекси ама сепак е споро.
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
Причината за ваквото споро време е поради агрегатната фунцкија 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, каде во некои случаеви би добивале застарени податоци, меѓутоа тоа и не е дотолку битно бидејќи се работи за податоци кои не мора да се толку точни (еднаш еден курс ќе биде на прво место а друг пат на второ, тоа ниту на нас ниту на корисникот ништо не менува).
Note:
See TracWiki
for help on using the wiki.
