Index: sql/perf/after_dossier_one.txt =================================================================== --- sql/perf/after_dossier_one.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_dossier_one.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,118 @@ +######## dossier_one ######## +Sort (cost=260.44..260.44 rows=1 width=167) (actual time=0.869..0.872 rows=1.00 loops=1) + Sort Key: (COALESCE((sum(s.awarded_credits)), '0'::bigint)) DESC, (COALESCE((round(avg(((ps.grade)::text)::integer), 2)), '0'::numeric)) DESC, u.index + Sort Method: quicksort Memory: 25kB + Buffers: shared hit=188 read=4 + -> Nested Loop Left Join (cost=23.45..260.43 rows=1 width=167) (actual time=0.841..0.846 rows=1.00 loops=1) + Buffers: shared hit=179 read=4 + -> Nested Loop Left Join (cost=19.14..254.96 rows=1 width=113) (actual time=0.729..0.733 rows=1.00 loops=1) + Buffers: shared hit=175 read=4 + -> Nested Loop Left Join (cost=14.54..203.24 rows=1 width=105) (actual time=0.300..0.303 rows=1.00 loops=1) + Buffers: shared hit=161 + -> Nested Loop Left Join (cost=9.64..116.19 rows=1 width=97) (actual time=0.246..0.249 rows=1.00 loops=1) + Buffers: shared hit=95 + -> Nested Loop Left Join (cost=5.33..97.71 rows=1 width=81) (actual time=0.231..0.233 rows=1.00 loops=1) + Buffers: shared hit=89 + -> Index Scan using users_pkey on users u (cost=0.28..8.30 rows=1 width=33) (actual time=0.013..0.014 rows=1.00 loops=1) + Index Cond: (id = 2500) + Filter: (role = 'student'::user_role) + Index Searches: 1 + Buffers: shared hit=3 + -> GroupAggregate (cost=5.05..89.39 rows=1 width=52) (actual time=0.216..0.217 rows=1.00 loops=1) + Buffers: shared hit=86 + -> Nested Loop (cost=5.05..89.13 rows=14 width=16) (actual time=0.078..0.175 rows=8.00 loops=1) + Buffers: shared hit=82 + -> Nested Loop (cost=4.90..86.81 rows=14 width=16) (actual time=0.074..0.160 rows=8.00 loops=1) + Buffers: shared hit=66 + -> Nested Loop (cost=4.61..80.15 rows=20 width=12) (actual time=0.055..0.102 rows=20.00 loops=1) + Buffers: shared hit=18 + -> Bitmap Heap Scan on enrolled_semesters es (cost=4.32..18.43 rows=4 width=8) (actual time=0.039..0.052 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.018..0.018 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> Index Scan using semesters_subjects_enrolled_semesters_id_subjects_id_key on semesters_subjects ss (cost=0.29..15.38 rows=5 width=12) (actual time=0.011..0.011 rows=5.00 loops=4) + Index Cond: (enrolled_semesters_id = es.id) + Index Searches: 4 + Buffers: shared hit=12 + -> Index Scan using passed_subjects_enrolled_id_key on passed_subjects ps (cost=0.29..0.33 rows=1 width=12) (actual time=0.003..0.003 rows=0.40 loops=20) + Index Cond: (enrolled_id = ss.id) + Index Searches: 20 + Buffers: shared hit=48 + -> Index Scan using subjects_pkey on subjects s (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1.00 loops=8) + Index Cond: (id = ss.subjects_id) + Index Searches: 8 + Buffers: shared hit=16 + -> GroupAggregate (cost=4.32..18.46 rows=1 width=20) (actual time=0.015..0.015 rows=1.00 loops=1) + Buffers: shared hit=6 + -> Bitmap Heap Scan on enrolled_semesters es_1 (cost=4.32..18.43 rows=4 width=16) (actual time=0.012..0.013 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.005..0.005 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> GroupAggregate (cost=4.90..87.03 rows=1 width=12) (actual time=0.053..0.054 rows=1.00 loops=1) + Buffers: shared hit=66 + -> Nested Loop Left Join (cost=4.90..87.01 rows=1 width=8) (actual time=0.027..0.052 rows=12.00 loops=1) + Filter: (ps_1.id IS NULL) + Rows Removed by Filter: 8 + Buffers: shared hit=66 + -> Nested Loop (cost=4.61..80.15 rows=20 width=8) (actual time=0.014..0.023 rows=20.00 loops=1) + Buffers: shared hit=18 + -> Bitmap Heap Scan on enrolled_semesters es_2 (cost=4.32..18.43 rows=4 width=8) (actual time=0.010..0.012 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.006..0.006 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> Index Scan using semesters_subjects_enrolled_semesters_id_subjects_id_key on semesters_subjects ss_1 (cost=0.29..15.38 rows=5 width=8) (actual time=0.002..0.002 rows=5.00 loops=4) + Index Cond: (enrolled_semesters_id = es_2.id) + Index Searches: 4 + Buffers: shared hit=12 + -> Index Scan using passed_subjects_enrolled_id_key on passed_subjects ps_1 (cost=0.29..0.33 rows=1 width=8) (actual time=0.001..0.001 rows=0.40 loops=20) + Index Cond: (enrolled_id = ss_1.id) + Index Searches: 20 + Buffers: shared hit=48 + -> GroupAggregate (cost=4.60..51.70 rows=1 width=12) (actual time=0.428..0.428 rows=1.00 loops=1) + Buffers: shared hit=14 read=4 + -> Nested Loop (cost=4.60..51.68 rows=4 width=8) (actual time=0.299..0.426 rows=4.00 loops=1) + Buffers: shared hit=14 read=4 + -> Bitmap Heap Scan on enrolled_semesters es_3 (cost=4.32..18.43 rows=4 width=8) (actual time=0.058..0.061 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.047..0.047 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> Index Scan using ix_payment_enrollment on payment p (cost=0.29..8.30 rows=1 width=8) (actual time=0.090..0.090 rows=1.00 loops=4) + Index Cond: (enrollment_id = es_3.id) + Index Searches: 4 + Buffers: shared hit=8 read=4 + -> GroupAggregate (cost=4.31..5.44 rows=1 width=20) (actual time=0.105..0.106 rows=1.00 loops=1) + Buffers: shared hit=4 + -> Hash Join (cost=4.31..5.42 rows=1 width=12) (actual time=0.103..0.105 rows=1.00 loops=1) + Hash Cond: (d.id = ud.document_id) + Buffers: shared hit=4 + -> Seq Scan on documents d (cost=0.00..1.08 rows=8 width=8) (actual time=0.009..0.009 rows=8.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=4.30..4.30 rows=1 width=8) (actual time=0.088..0.088 rows=1.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=3 + -> Index Only Scan using user_documents_pkey on user_documents ud (cost=0.28..4.30 rows=1 width=8) (actual time=0.080..0.081 rows=1.00 loops=1) + Index Cond: (user_id = 2500) + Heap Fetches: 0 + Index Searches: 1 + Buffers: shared hit=3 +Planning: + Buffers: shared hit=563 +Planning Time: 17.012 ms +Execution Time: 1.067 ms +-- OK explain_dossier_one.sql Index: sql/perf/after_gradebook.txt =================================================================== --- sql/perf/after_gradebook.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_gradebook.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,49 @@ +######## gradebook WITH index ######## +Sort (cost=2048.55..2050.22 rows=666 width=73) (actual time=7.428..7.447 rows=684.00 loops=1) + Sort Key: s.name, u.surname + Sort Method: quicksort Memory: 81kB + Buffers: shared hit=2031 read=2 + -> Nested Loop Left Join (cost=1042.40..2017.32 rows=666 width=73) (actual time=4.026..6.081 rows=684.00 loops=1) + Buffers: shared hit=2028 read=2 + -> Hash Join (cost=1042.11..1185.19 rows=666 width=52) (actual time=4.009..4.875 rows=684.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Buffers: shared hit=659 read=2 + -> Hash Join (cost=1032.36..1173.67 rows=666 width=38) (actual time=3.929..4.688 rows=684.00 loops=1) + Hash Cond: (u.id = es.user_id) + Buffers: shared hit=656 read=2 + -> Seq Scan on users u (cost=0.00..119.20 rows=4120 width=33) (actual time=0.006..0.274 rows=4120.00 loops=1) + Buffers: shared hit=78 + -> Hash (cost=1024.04..1024.04 rows=666 width=13) (actual time=3.903..3.903 rows=684.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 41kB + Buffers: shared hit=578 read=2 + -> Hash Join (cost=563.39..1024.04 rows=666 width=13) (actual time=1.575..3.817 rows=684.00 loops=1) + Hash Cond: (es.id = ss.enrolled_semesters_id) + Buffers: shared hit=578 read=2 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.007..0.856 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=555.06..555.06 rows=666 width=13) (actual time=1.558..1.559 rows=684.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 41kB + Buffers: shared hit=424 read=2 + -> Bitmap Heap Scan on semesters_subjects ss (cost=9.45..555.06 rows=666 width=13) (actual time=0.108..1.480 rows=684.00 loops=1) + Recheck Cond: (professor_id = 47) + Heap Blocks: exact=424 + Buffers: shared hit=424 read=2 + -> Bitmap Index Scan on ix_semesters_subjects_professor (cost=0.00..9.29 rows=666 width=0) (actual time=0.070..0.070 rows=684.00 loops=1) + Index Cond: (professor_id = 47) + Index Searches: 1 + Buffers: shared read=2 + -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.074..0.074 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 25kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=22) (actual time=0.012..0.037 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Index Only Scan using ix_passed_subjects_enrolled_grade on passed_subjects ps (cost=0.29..1.24 rows=1 width=8) (actual time=0.001..0.001 rows=0.68 loops=684) + Index Cond: (enrolled_id = ss.id) + Heap Fetches: 0 + Index Searches: 684 + Buffers: shared hit=1369 +Planning: + Buffers: shared hit=373 read=1 +Planning Time: 11.015 ms +Execution Time: 7.615 ms +-- OK gradebook.sql Index: sql/perf/after_rep_busiest_professor.txt =================================================================== --- sql/perf/after_rep_busiest_professor.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_rep_busiest_professor.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,62 @@ +######## rep_busiest_professor ######## +Sort (cost=11894.77..11895.22 rows=181 width=68) (actual time=115.101..115.107 rows=20.00 loops=1) + Sort Key: a.year, (CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END) + Sort Method: quicksort Memory: 26kB + Buffers: shared hit=1082, temp read=282 written=283 + CTE professor_load + -> GroupAggregate (cost=10376.15..11600.15 rows=2400 width=32) (actual time=83.574..107.280 rows=2400.00 loops=1) + Group Key: es.semester_id, ss.professor_id + Buffers: shared hit=1021, temp read=282 written=283 + -> Sort (cost=10376.15..10576.15 rows=80000 width=20) (actual time=83.558..93.708 rows=80000.00 loops=1) + Sort Key: es.semester_id, ss.professor_id, ss.subjects_id + Sort Method: external merge Disk: 2256kB + Buffers: shared hit=1021, temp read=282 written=283 + -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=14.299..47.876 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1021 + -> Hash Join (cost=514.00..2034.06 rows=80000 width=16) (actual time=3.780..23.831 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=16) (actual time=0.009..4.194 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.736..3.736 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.009..2.092 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=10.421..10.421 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=8) (actual time=0.012..4.723 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Nested Loop Left Join (cost=79.73..287.83 rows=181 width=68) (actual time=108.848..115.089 rows=20.00 loops=1) + Buffers: shared hit=1082, temp read=282 written=283 + -> Hash Right Join (cost=79.45..204.42 rows=181 width=36) (actual time=108.755..114.922 rows=20.00 loops=1) + Hash Cond: (pl.semester_id = a.id) + Buffers: shared hit=1022, temp read=282 written=283 + -> Hash Anti Join (cost=78.00..198.12 rows=1811 width=32) (actual time=108.727..114.880 rows=20.00 loops=1) + Hash Cond: (pl.semester_id = pl1.semester_id) + Join Filter: ((pl.enrolled_students < pl1.enrolled_students) OR ((pl.enrolled_students = pl1.enrolled_students) AND (pl.graded_students < pl1.graded_students)) OR ((pl.enrolled_students = pl1.enrolled_students) AND (pl.graded_students = pl1.graded_students) AND (pl.professor_id > pl1.professor_id))) + Rows Removed by Join Filter: 40595 + Buffers: shared hit=1021, temp read=282 written=283 + -> CTE Scan on professor_load pl (cost=0.00..48.00 rows=2400 width=32) (actual time=83.576..83.800 rows=2400.00 loops=1) + Storage: Memory Maximum Storage: 164kB + Buffers: shared hit=1021, temp read=282 written=283 + -> Hash (cost=48.00..48.00 rows=2400 width=24) (actual time=24.861..24.861 rows=2400.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 173kB + -> CTE Scan on professor_load pl1 (cost=0.00..48.00 rows=2400 width=24) (actual time=0.001..24.500 rows=2400.00 loops=1) + Storage: Memory Maximum Storage: 164kB + -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.023..0.023 rows=20.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.014..0.016 rows=20.00 loops=1) + Buffers: shared hit=1 + -> Index Scan using users_pkey on users u (cost=0.28..0.45 rows=1 width=26) (actual time=0.005..0.005 rows=1.00 loops=20) + Index Cond: (id = pl.professor_id) + Index Searches: 20 + Buffers: shared hit=60 +Planning: + Buffers: shared hit=340 +Planning Time: 11.753 ms +Execution Time: 124.648 ms +-- OK explain_rep_busiest_professor.sql Index: sql/perf/after_rep_semester_growth.txt =================================================================== --- sql/perf/after_rep_semester_growth.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_rep_semester_growth.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,59 @@ +######## rep_semester_growth ######## +Sort (cost=14562.60..14562.77 rows=67 width=156) (actual time=130.220..130.225 rows=20.00 loops=1) + Sort Key: cur.chrono + Sort Method: quicksort Memory: 26kB + Buffers: shared hit=1026, temp read=361 written=362 + CTE semester_stats + -> GroupAggregate (cost=13146.60..14346.95 rows=20 width=40) (actual time=112.102..129.943 rows=20.00 loops=1) + Group Key: a.id, (((a.year * 10) + CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END)) + Buffers: shared hit=1022, temp read=361 written=362 + -> Sort (cost=13146.60..13346.60 rows=80000 width=28) (actual time=111.166..118.496 rows=80000.00 loops=1) + Sort Key: a.id, (((a.year * 10) + CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END)), es.id + Sort Method: external merge Disk: 2888kB + Buffers: shared hit=1022, temp read=361 written=362 + -> Hash Left Join (cost=2132.45..4717.02 rows=80000 width=28) (actual time=16.506..66.606 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1022 + -> Hash Right Join (cost=515.45..2290.01 rows=80000 width=20) (actual time=3.651..37.227 rows=80000.00 loops=1) + Hash Cond: (es.semester_id = a.id) + Buffers: shared hit=665 + -> Hash Right Join (cost=514.00..2034.06 rows=80000 width=12) (actual time=3.628..24.767 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.010..4.829 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.567..3.567 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.010..2.060 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.016..0.016 rows=20.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.011..0.012 rows=20.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=12.745..12.745 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=8) (actual time=0.010..5.858 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Nested Loop Left Join (cost=0.00..213.62 rows=67 width=156) (actual time=130.017..130.212 rows=20.00 loops=1) + Join Filter: ((prev.chrono < cur.chrono) AND (NOT EXISTS(SubPlan 2))) + Rows Removed by Join Filter: 381 + Buffers: shared hit=1026, temp read=361 written=362 + -> CTE Scan on semester_stats cur (cost=0.00..0.40 rows=20 width=36) (actual time=112.105..112.106 rows=20.00 loops=1) + Storage: Memory Maximum Storage: 18kB + Buffers: shared hit=1022, temp read=336 written=362 + -> CTE Scan on semester_stats prev (cost=0.00..0.40 rows=20 width=20) (actual time=0.000..0.045 rows=20.00 loops=20) + Storage: Memory Maximum Storage: 18kB + SubPlan 2 + -> CTE Scan on semester_stats mid (cost=0.00..0.50 rows=1 width=0) (actual time=0.090..0.090 rows=0.90 loops=190) + Filter: ((chrono < cur.chrono) AND (chrono > prev.chrono)) + Rows Removed by Filter: 8 + Storage: Memory Maximum Storage: 18kB + Buffers: temp read=25 +Planning: + Buffers: shared hit=314 +Planning Time: 11.066 ms +Execution Time: 138.077 ms +-- OK explain_rep_semester_growth.sql Index: sql/perf/after_rep_student_dossier.txt =================================================================== --- sql/perf/after_rep_student_dossier.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_rep_student_dossier.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,143 @@ +######## rep_student_dossier ######## +Sort (cost=10637.89..10647.89 rows=4000 width=167) (actual time=176.610..176.798 rows=4000.00 loops=1) + Sort Key: (COALESCE(ps.credits, '0'::bigint)) DESC, (COALESCE(ps.average_grade, '0'::numeric)) DESC, u.index + Sort Method: quicksort Memory: 597kB + Buffers: shared hit=74402 + -> Hash Left Join (cost=10176.52..10398.57 rows=4000 width=167) (actual time=152.524..158.937 rows=4000.00 loops=1) + Hash Cond: (u.id = doc.user_id) + Buffers: shared hit=74393 + -> Hash Left Join (cost=9941.27..10112.82 rows=4000 width=113) (actual time=148.736..153.409 rows=4000.00 loops=1) + Hash Cond: (u.id = pay.user_id) + Buffers: shared hit=74374 + -> Hash Left Join (cost=8928.26..9089.30 rows=4000 width=105) (actual time=138.645..142.335 rows=4000.00 loops=1) + Hash Cond: (u.id = pen.user_id) + Buffers: shared hit=74133 + -> Hash Left Join (cost=5790.89..5941.41 rows=4000 width=97) (actual time=77.269..80.169 rows=4000.00 loops=1) + Hash Cond: (u.id = sem.user_id) + Buffers: shared hit=1266 + -> Hash Left Join (cost=5226.89..5366.90 rows=4000 width=81) (actual time=72.908..74.881 rows=4000.00 loops=1) + Hash Cond: (u.id = ps.user_id) + Buffers: shared hit=1112 + -> Seq Scan on users u (cost=0.00..129.50 rows=4000 width=33) (actual time=0.037..1.039 rows=4000.00 loops=1) + Filter: (role = 'student'::user_role) + Rows Removed by Filter: 120 + Buffers: shared hit=78 + -> Hash (cost=5176.89..5176.89 rows=4000 width=52) (actual time=72.852..72.861 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 267kB + Buffers: shared hit=1034 + -> Subquery Scan on ps (cost=5076.89..5176.89 rows=4000 width=52) (actual time=70.784..72.327 rows=4000.00 loops=1) + Buffers: shared hit=1034 + -> HashAggregate (cost=5076.89..5136.89 rows=4000 width=52) (actual time=70.783..72.102 rows=4000.00 loops=1) + Group Key: es.user_id + Batches: 1 Memory Usage: 1169kB + Buffers: shared hit=1034 + -> Hash Join (cost=2140.75..3956.89 rows=56000 width=16) (actual time=15.224..56.130 rows=56000.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Buffers: shared hit=1024 + -> Hash Join (cost=2131.00..3798.06 rows=56000 width=16) (actual time=15.136..47.841 rows=56000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=1021 + -> Hash Join (cost=1617.00..3137.01 rows=56000 width=16) (actual time=11.114..34.024 rows=56000.00 loops=1) + Hash Cond: (ss.id = ps_1.enrolled_id) + Buffers: shared hit=867 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.013..4.720 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=10.920..10.921 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2919kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps_1 (cost=0.00..917.00 rows=56000 width=12) (actual time=0.012..5.112 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.863..3.868 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.044..1.950 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.081..0.081 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 20kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=8) (actual time=0.011..0.044 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Hash (cost=514.00..514.00 rows=4000 width=20) (actual time=4.346..4.347 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 236kB + Buffers: shared hit=154 + -> Subquery Scan on sem (cost=434.00..514.00 rows=4000 width=20) (actual time=3.189..3.878 rows=4000.00 loops=1) + Buffers: shared hit=154 + -> HashAggregate (cost=434.00..474.00 rows=4000 width=20) (actual time=3.188..3.625 rows=4000.00 loops=1) + Group Key: es_1.user_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es_1 (cost=0.00..314.00 rows=16000 width=16) (actual time=0.025..0.546 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=3137.36..3137.36 rows=1 width=12) (actual time=61.368..61.370 rows=2000.00 loops=1) + Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB + Buffers: shared hit=72867 + -> Subquery Scan on pen (cost=3137.33..3137.36 rows=1 width=12) (actual time=57.936..61.155 rows=2000.00 loops=1) + Buffers: shared hit=72867 + -> GroupAggregate (cost=3137.33..3137.35 rows=1 width=12) (actual time=57.935..61.026 rows=2000.00 loops=1) + Group Key: es_2.user_id + Buffers: shared hit=72867 + -> Sort (cost=3137.33..3137.34 rows=1 width=8) (actual time=57.929..58.645 rows=24000.00 loops=1) + Sort Key: es_2.user_id + Sort Method: quicksort Memory: 1331kB + Buffers: shared hit=72867 + -> Nested Loop (cost=1617.29..3137.32 rows=1 width=8) (actual time=9.638..54.328 rows=24000.00 loops=1) + Buffers: shared hit=72867 + -> Hash Left Join (cost=1617.00..3137.01 rows=1 width=8) (actual time=9.623..26.891 rows=24000.00 loops=1) + Hash Cond: (ss_1.id = ps_2.enrolled_id) + Filter: (ps_2.id IS NULL) + Rows Removed by Filter: 56000 + Buffers: shared hit=867 + -> Seq Scan on semesters_subjects ss_1 (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.010..3.379 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.527..9.528 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps_2 (cost=0.00..917.00 rows=56000 width=8) (actual time=0.008..3.817 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Index Scan using enrolled_semesters_pkey on enrolled_semesters es_2 (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=1.00 loops=24000) + Index Cond: (id = ss_1.enrolled_semesters_id) + Index Searches: 24000 + Buffers: shared hit=72000 + -> Hash (cost=963.01..963.01 rows=4000 width=12) (actual time=10.082..10.084 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 204kB + Buffers: shared hit=241 + -> Subquery Scan on pay (cost=883.01..963.01 rows=4000 width=12) (actual time=8.528..9.462 rows=4000.00 loops=1) + Buffers: shared hit=241 + -> HashAggregate (cost=883.01..923.01 rows=4000 width=12) (actual time=8.527..9.090 rows=4000.00 loops=1) + Group Key: es_3.user_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=241 + -> Hash Join (cost=514.00..803.01 rows=16000 width=8) (actual time=2.543..6.386 rows=16000.00 loops=1) + Hash Cond: (p.enrollment_id = es_3.id) + Buffers: shared hit=241 + -> Seq Scan on payment p (cost=0.00..247.00 rows=16000 width=8) (actual time=0.028..0.788 rows=16000.00 loops=1) + Buffers: shared hit=87 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=2.503..2.503 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es_3 (cost=0.00..314.00 rows=16000 width=8) (actual time=0.007..1.132 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=185.24..185.24 rows=4000 width=20) (actual time=3.710..3.712 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 236kB + Buffers: shared hit=19 + -> Subquery Scan on doc (cost=105.24..185.24 rows=4000 width=20) (actual time=2.236..3.114 rows=4000.00 loops=1) + Buffers: shared hit=19 + -> HashAggregate (cost=105.24..145.24 rows=4000 width=20) (actual time=2.235..2.795 rows=4000.00 loops=1) + Group Key: ud.user_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=19 + -> Hash Join (cost=1.18..75.24 rows=4000 width=12) (actual time=0.219..1.218 rows=4000.00 loops=1) + Hash Cond: (ud.document_id = d.id) + Buffers: shared hit=19 + -> Seq Scan on user_documents ud (cost=0.00..58.00 rows=4000 width=8) (actual time=0.071..0.301 rows=4000.00 loops=1) + Buffers: shared hit=18 + -> Hash (cost=1.08..1.08 rows=8 width=8) (actual time=0.042..0.043 rows=8.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on documents d (cost=0.00..1.08 rows=8 width=8) (actual time=0.034..0.035 rows=8.00 loops=1) + Buffers: shared hit=1 +Planning: + Buffers: shared hit=550 read=8 +Planning Time: 21.114 ms +Execution Time: 179.370 ms +-- OK explain_rep_student_dossier.sql Index: sql/perf/after_rep_subject_pass_rate.txt =================================================================== --- sql/perf/after_rep_subject_pass_rate.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_rep_subject_pass_rate.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,94 @@ +######## rep_subject_pass_rate ######## +Sort (cost=12503.55..12505.05 rows=600 width=146) (actual time=154.481..154.698 rows=6000.00 loops=1) + Sort Key: a.year, a.type, (round(((100.0 * ((count(ps.id)))::numeric) / ((count(ss.id)))::numeric), 1)) DESC, s.code + Sort Method: quicksort Memory: 747kB + Buffers: shared hit=2577 + -> Hash Left Join (cost=12288.43..12475.86 rows=600 width=146) (actual time=131.644..140.919 rows=6000.00 loops=1) + Hash Cond: (ss.subjects_id = sa.subject_id) + Buffers: shared hit=2566 + -> Hash Left Join (cost=8300.17..8471.00 rows=600 width=86) (actual time=96.448..102.055 rows=6000.00 loops=1) + Hash Cond: ((ss.subjects_id = sps.subject_id) AND (es.semester_id = sps.semester_id)) + Buffers: shared hit=1699 + -> Hash Join (cost=5672.27..5839.95 rows=600 width=82) (actual time=69.495..73.840 rows=6000.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Buffers: shared hit=1035 + -> Hash Join (cost=5662.52..5828.60 rows=600 width=64) (actual time=69.356..72.693 rows=6000.00 loops=1) + Hash Cond: (es.semester_id = a.id) + Buffers: shared hit=1032 + -> HashAggregate (cost=5661.07..5751.07 rows=6000 width=56) (actual time=69.338..71.731 rows=6000.00 loops=1) + Group Key: ss.subjects_id, es.semester_id + Batches: 1 Memory Usage: 1169kB + Buffers: shared hit=1031 + -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=15.669..48.617 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1021 + -> Hash Join (cost=514.00..2034.06 rows=80000 width=12) (actual time=4.269..23.426 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.007..4.131 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=4.199..4.200 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.011..2.333 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=11.266..11.266 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2919kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=12) (actual time=0.014..5.114 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.014..0.015 rows=20.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.009..0.011 rows=20.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.080..0.080 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 25kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=22) (actual time=0.017..0.043 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Hash (cost=2537.91..2537.91 rows=6000 width=16) (actual time=26.921..26.922 rows=6000.00 loops=1) + Buckets: 8192 Batches: 1 Memory Usage: 346kB + Buffers: shared hit=664 + -> Subquery Scan on sps (cost=2417.91..2537.91 rows=6000 width=16) (actual time=25.227..26.223 rows=6000.00 loops=1) + Buffers: shared hit=664 + -> HashAggregate (cost=2417.91..2477.91 rows=6000 width=16) (actual time=25.226..25.820 rows=6000.00 loops=1) + Group Key: ss_1.subjects_id, es_1.semester_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=664 + -> Hash Join (cost=514.00..1978.01 rows=58653 width=12) (actual time=2.774..16.440 rows=58801.00 loops=1) + Hash Cond: (ss_1.enrolled_semesters_id = es_1.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss_1 (cost=0.00..1310.00 rows=58653 width=12) (actual time=0.017..5.812 rows=58801.00 loops=1) + Filter: signature + Rows Removed by Filter: 21199 + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=2.702..2.703 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es_1 (cost=0.00..314.00 rows=16000 width=8) (actual time=0.007..1.336 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=3984.51..3984.51 rows=300 width=36) (actual time=35.177..35.178 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 21kB + Buffers: shared hit=867 + -> Subquery Scan on sa (cost=3977.01..3984.51 rows=300 width=36) (actual time=34.994..35.139 rows=300.00 loops=1) + Buffers: shared hit=867 + -> HashAggregate (cost=3977.01..3981.51 rows=300 width=36) (actual time=34.993..35.095 rows=300.00 loops=1) + Group Key: ss_2.subjects_id + Batches: 1 Memory Usage: 80kB + Buffers: shared hit=867 + -> Hash Join (cost=1617.00..3137.01 rows=56000 width=8) (actual time=9.870..24.436 rows=56000.00 loops=1) + Hash Cond: (ss_2.id = ps_1.enrolled_id) + Buffers: shared hit=867 + -> Seq Scan on semesters_subjects ss_2 (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.015..2.704 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.613..9.613 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps_1 (cost=0.00..917.00 rows=56000 width=8) (actual time=0.012..3.997 rows=56000.00 loops=1) + Buffers: shared hit=357 +Planning: + Buffers: shared hit=416 read=8 +Planning Time: 11.797 ms +Execution Time: 156.015 ms +-- OK explain_rep_subject_pass_rate.sql Index: sql/perf/after_rep_top_student_per_major.txt =================================================================== --- sql/perf/after_rep_top_student_per_major.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/after_rep_top_student_per_major.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,69 @@ +######## rep_top_student_per_major ######## +Sort (cost=6208.33..6208.56 rows=91 width=95) (actual time=93.283..93.287 rows=6.00 loops=1) + Sort Key: m.name + Sort Method: quicksort Memory: 25kB + Buffers: shared hit=1056 + CTE standing + -> HashAggregate (cost=5883.79..5943.79 rows=4000 width=56) (actual time=81.507..82.943 rows=4000.00 loops=1) + Group Key: es.user_id, es.major_id + Batches: 1 Memory Usage: 1169kB + Buffers: shared hit=1034 + -> Hash Left Join (cost=2140.75..4083.79 rows=80000 width=20) (actual time=15.695..62.979 rows=80000.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Join Filter: (ps.id IS NOT NULL) + Rows Removed by Join Filter: 24000 + Buffers: shared hit=1024 + -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=15.614..51.151 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1021 + -> Hash Right Join (cost=514.00..2034.06 rows=80000 width=16) (actual time=3.462..24.820 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.011..4.913 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=12) (actual time=3.390..3.391 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 816kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=12) (actual time=0.018..1.826 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=12.055..12.055 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2919kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=12) (actual time=0.011..5.684 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.075..0.076 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 20kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=8) (actual time=0.019..0.044 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Nested Loop (cost=131.42..261.59 rows=91 width=95) (actual time=86.807..93.255 rows=6.00 loops=1) + Buffers: shared hit=1053 + -> Hash Anti Join (cost=131.13..225.46 rows=91 width=60) (actual time=86.753..93.190 rows=6.00 loops=1) + Hash Cond: (st.major_id = st1.major_id) + Join Filter: ((st.credits < st1.credits) OR ((st.credits = st1.credits) AND (st.average_grade < st1.average_grade)) OR ((st.credits = st1.credits) AND (st.average_grade = st1.average_grade) AND (st.user_id > st1.user_id))) + Rows Removed by Join Filter: 28423 + Buffers: shared hit=1035 + -> Hash Join (cost=1.14..91.86 rows=120 width=64) (actual time=81.558..82.640 rows=4000.00 loops=1) + Hash Cond: (st.major_id = m.id) + Buffers: shared hit=1035 + -> CTE Scan on standing st (cost=0.00..80.00 rows=4000 width=56) (actual time=81.512..81.779 rows=4000.00 loops=1) + Storage: Memory Maximum Storage: 251kB + Buffers: shared hit=1034 + -> Hash (cost=1.06..1.06 rows=6 width=12) (actual time=0.037..0.038 rows=6.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on major m (cost=0.00..1.06 rows=6 width=12) (actual time=0.033..0.033 rows=6.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=80.00..80.00 rows=4000 width=48) (actual time=2.977..2.977 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 267kB + -> CTE Scan on standing st1 (cost=0.00..80.00 rows=4000 width=48) (actual time=0.001..2.425 rows=4000.00 loops=1) + Storage: Memory Maximum Storage: 251kB + -> Index Scan using users_pkey on users u (cost=0.28..0.39 rows=1 width=33) (actual time=0.009..0.009 rows=1.00 loops=6) + Index Cond: (id = st.user_id) + Index Searches: 6 + Buffers: shared hit=18 +Planning: + Buffers: shared hit=412 +Planning Time: 13.507 ms +Execution Time: 93.983 ms +-- OK explain_rep_top_student_per_major.sql Index: sql/perf/before_dossier_one.txt =================================================================== --- sql/perf/before_dossier_one.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_dossier_one.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,120 @@ +######## dossier_one ######## +Sort (cost=516.25..516.25 rows=1 width=167) (actual time=1.977..1.979 rows=1.00 loops=1) + Sort Key: (COALESCE((sum(s.awarded_credits)), '0'::bigint)) DESC, (COALESCE((round(avg(((ps.grade)::text)::integer), 2)), '0'::numeric)) DESC, u.index + Sort Method: quicksort Memory: 25kB + Buffers: shared hit=267 + -> Nested Loop Left Join (cost=37.34..516.24 rows=1 width=167) (actual time=1.948..1.952 rows=1.00 loops=1) + Buffers: shared hit=258 + -> Nested Loop Left Join (cost=33.03..510.77 rows=1 width=113) (actual time=1.856..1.859 rows=1.00 loops=1) + Buffers: shared hit=254 + -> Nested Loop Left Join (cost=14.54..203.24 rows=1 width=105) (actual time=0.302..0.305 rows=1.00 loops=1) + Buffers: shared hit=161 + -> Nested Loop Left Join (cost=9.64..116.19 rows=1 width=97) (actual time=0.254..0.256 rows=1.00 loops=1) + Buffers: shared hit=95 + -> Nested Loop Left Join (cost=5.33..97.71 rows=1 width=81) (actual time=0.234..0.236 rows=1.00 loops=1) + Buffers: shared hit=89 + -> Index Scan using users_pkey on users u (cost=0.28..8.30 rows=1 width=33) (actual time=0.013..0.014 rows=1.00 loops=1) + Index Cond: (id = 2500) + Filter: (role = 'student'::user_role) + Index Searches: 1 + Buffers: shared hit=3 + -> GroupAggregate (cost=5.05..89.39 rows=1 width=52) (actual time=0.219..0.220 rows=1.00 loops=1) + Buffers: shared hit=86 + -> Nested Loop (cost=5.05..89.13 rows=14 width=16) (actual time=0.074..0.179 rows=8.00 loops=1) + Buffers: shared hit=82 + -> Nested Loop (cost=4.90..86.81 rows=14 width=16) (actual time=0.069..0.163 rows=8.00 loops=1) + Buffers: shared hit=66 + -> Nested Loop (cost=4.61..80.15 rows=20 width=12) (actual time=0.053..0.104 rows=20.00 loops=1) + Buffers: shared hit=18 + -> Bitmap Heap Scan on enrolled_semesters es (cost=4.32..18.43 rows=4 width=8) (actual time=0.037..0.051 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.016..0.017 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> Index Scan using semesters_subjects_enrolled_semesters_id_subjects_id_key on semesters_subjects ss (cost=0.29..15.38 rows=5 width=12) (actual time=0.012..0.012 rows=5.00 loops=4) + Index Cond: (enrolled_semesters_id = es.id) + Index Searches: 4 + Buffers: shared hit=12 + -> Index Scan using passed_subjects_enrolled_id_key on passed_subjects ps (cost=0.29..0.33 rows=1 width=12) (actual time=0.003..0.003 rows=0.40 loops=20) + Index Cond: (enrolled_id = ss.id) + Index Searches: 20 + Buffers: shared hit=48 + -> Index Scan using subjects_pkey on subjects s (cost=0.15..0.17 rows=1 width=8) (actual time=0.002..0.002 rows=1.00 loops=8) + Index Cond: (id = ss.subjects_id) + Index Searches: 8 + Buffers: shared hit=16 + -> GroupAggregate (cost=4.32..18.46 rows=1 width=20) (actual time=0.019..0.019 rows=1.00 loops=1) + Buffers: shared hit=6 + -> Bitmap Heap Scan on enrolled_semesters es_1 (cost=4.32..18.43 rows=4 width=16) (actual time=0.015..0.016 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.007..0.007 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> GroupAggregate (cost=4.90..87.03 rows=1 width=12) (actual time=0.047..0.048 rows=1.00 loops=1) + Buffers: shared hit=66 + -> Nested Loop Left Join (cost=4.90..87.01 rows=1 width=8) (actual time=0.021..0.046 rows=12.00 loops=1) + Filter: (ps_1.id IS NULL) + Rows Removed by Filter: 8 + Buffers: shared hit=66 + -> Nested Loop (cost=4.61..80.15 rows=20 width=8) (actual time=0.016..0.024 rows=20.00 loops=1) + Buffers: shared hit=18 + -> Bitmap Heap Scan on enrolled_semesters es_2 (cost=4.32..18.43 rows=4 width=8) (actual time=0.013..0.015 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.008..0.008 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> Index Scan using semesters_subjects_enrolled_semesters_id_subjects_id_key on semesters_subjects ss_1 (cost=0.29..15.38 rows=5 width=8) (actual time=0.001..0.002 rows=5.00 loops=4) + Index Cond: (enrolled_semesters_id = es_2.id) + Index Searches: 4 + Buffers: shared hit=12 + -> Index Scan using passed_subjects_enrolled_id_key on passed_subjects ps_1 (cost=0.29..0.33 rows=1 width=8) (actual time=0.001..0.001 rows=0.40 loops=20) + Index Cond: (enrolled_id = ss_1.id) + Index Searches: 20 + Buffers: shared hit=48 + -> GroupAggregate (cost=18.48..307.51 rows=1 width=12) (actual time=1.553..1.553 rows=1.00 loops=1) + Buffers: shared hit=93 + -> Hash Join (cost=18.48..307.49 rows=4 width=8) (actual time=0.186..1.552 rows=4.00 loops=1) + Hash Cond: (p.enrollment_id = es_3.id) + Buffers: shared hit=93 + -> Seq Scan on payment p (cost=0.00..247.00 rows=16000 width=8) (actual time=0.011..0.624 rows=16000.00 loops=1) + Buffers: shared hit=87 + -> Hash (cost=18.43..18.43 rows=4 width=8) (actual time=0.016..0.017 rows=4.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=6 + -> Bitmap Heap Scan on enrolled_semesters es_3 (cost=4.32..18.43 rows=4 width=8) (actual time=0.012..0.013 rows=4.00 loops=1) + Recheck Cond: (user_id = 2500) + Heap Blocks: exact=4 + Buffers: shared hit=6 + -> Bitmap Index Scan on enrolled_semesters_user_id_semester_id_key (cost=0.00..4.32 rows=4 width=0) (actual time=0.006..0.006 rows=4.00 loops=1) + Index Cond: (user_id = 2500) + Index Searches: 1 + Buffers: shared hit=2 + -> GroupAggregate (cost=4.31..5.44 rows=1 width=20) (actual time=0.086..0.086 rows=1.00 loops=1) + Buffers: shared hit=4 + -> Hash Join (cost=4.31..5.42 rows=1 width=12) (actual time=0.084..0.085 rows=1.00 loops=1) + Hash Cond: (d.id = ud.document_id) + Buffers: shared hit=4 + -> Seq Scan on documents d (cost=0.00..1.08 rows=8 width=8) (actual time=0.007..0.007 rows=8.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=4.30..4.30 rows=1 width=8) (actual time=0.073..0.073 rows=1.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=3 + -> Index Only Scan using user_documents_pkey on user_documents ud (cost=0.28..4.30 rows=1 width=8) (actual time=0.069..0.069 rows=1.00 loops=1) + Index Cond: (user_id = 2500) + Heap Fetches: 0 + Index Searches: 1 + Buffers: shared hit=3 +Planning: + Buffers: shared hit=453 +Planning Time: 12.801 ms +Execution Time: 2.190 ms +-- OK explain_dossier_one.sql Index: sql/perf/before_gradebook.txt =================================================================== --- sql/perf/before_gradebook.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_gradebook.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,45 @@ +######## gradebook WITHOUT index ######## +Sort (cost=3025.52..3027.18 rows=664 width=73) (actual time=16.122..16.143 rows=684.00 loops=1) + Sort Key: s.name, u.surname + Sort Method: quicksort Memory: 81kB + Buffers: shared hit=1965 read=152 + -> Hash Join (cost=1699.04..2994.40 rows=664 width=73) (actual time=7.662..14.333 rows=684.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Buffers: shared hit=1962 read=152 + -> Hash Join (cost=1689.29..2979.56 rows=664 width=38) (actual time=7.580..13.939 rows=684.00 loops=1) + Hash Cond: (es.user_id = u.id) + Buffers: shared hit=1959 read=152 + -> Nested Loop Left Join (cost=1518.59..2807.12 rows=664 width=13) (actual time=6.304..12.389 rows=684.00 loops=1) + Buffers: shared hit=1881 read=152 + -> Hash Join (cost=1518.30..1978.94 rows=664 width=13) (actual time=6.270..9.008 rows=684.00 loops=1) + Hash Cond: (es.id = ss.enrolled_semesters_id) + Buffers: shared hit=664 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.029..1.111 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=1510.00..1510.00 rows=664 width=13) (actual time=6.211..6.212 rows=684.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 41kB + Buffers: shared hit=510 + -> Seq Scan on semesters_subjects ss (cost=0.00..1510.00 rows=664 width=13) (actual time=0.038..6.061 rows=684.00 loops=1) + Filter: (professor_id = 47) + Rows Removed by Filter: 79316 + Buffers: shared hit=510 + -> Index Only Scan using ix_passed_subjects_enrolled_grade on passed_subjects ps (cost=0.29..1.25 rows=1 width=8) (actual time=0.005..0.005 rows=0.68 loops=684) + Index Cond: (enrolled_id = ss.id) + Heap Fetches: 0 + Index Searches: 684 + Buffers: shared hit=1217 read=152 + -> Hash (cost=119.20..119.20 rows=4120 width=33) (actual time=1.225..1.225 rows=4120.00 loops=1) + Buckets: 8192 Batches: 1 Memory Usage: 336kB + Buffers: shared hit=78 + -> Seq Scan on users u (cost=0.00..119.20 rows=4120 width=33) (actual time=0.013..0.614 rows=4120.00 loops=1) + Buffers: shared hit=78 + -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.073..0.074 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 25kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=22) (actual time=0.011..0.035 rows=300.00 loops=1) + Buffers: shared hit=3 +Planning: + Buffers: shared hit=361 +Planning Time: 10.951 ms +Execution Time: 16.332 ms +-- OK gradebook.sql Index: sql/perf/before_rep_busiest_professor.txt =================================================================== --- sql/perf/before_rep_busiest_professor.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_rep_busiest_professor.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,62 @@ +######## rep_busiest_professor ######## +Sort (cost=11894.77..11895.22 rows=181 width=68) (actual time=114.617..114.621 rows=20.00 loops=1) + Sort Key: a.year, (CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END) + Sort Method: quicksort Memory: 26kB + Buffers: shared hit=1082, temp read=282 written=283 + CTE professor_load + -> GroupAggregate (cost=10376.15..11600.15 rows=2400 width=32) (actual time=89.755..109.549 rows=2400.00 loops=1) + Group Key: es.semester_id, ss.professor_id + Buffers: shared hit=1021, temp read=282 written=283 + -> Sort (cost=10376.15..10576.15 rows=80000 width=20) (actual time=89.732..98.078 rows=80000.00 loops=1) + Sort Key: es.semester_id, ss.professor_id, ss.subjects_id + Sort Method: external merge Disk: 2256kB + Buffers: shared hit=1021, temp read=282 written=283 + -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=16.393..52.460 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1021 + -> Hash Join (cost=514.00..2034.06 rows=80000 width=16) (actual time=3.912..25.189 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=16) (actual time=0.009..4.753 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.839..3.840 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.010..2.149 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=12.348..12.349 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=8) (actual time=0.013..5.624 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Nested Loop Left Join (cost=79.73..287.83 rows=181 width=68) (actual time=110.637..114.608 rows=20.00 loops=1) + Buffers: shared hit=1082, temp read=282 written=283 + -> Hash Right Join (cost=79.45..204.42 rows=181 width=36) (actual time=110.600..114.535 rows=20.00 loops=1) + Hash Cond: (pl.semester_id = a.id) + Buffers: shared hit=1022, temp read=282 written=283 + -> Hash Anti Join (cost=78.00..198.12 rows=1811 width=32) (actual time=110.572..114.498 rows=20.00 loops=1) + Hash Cond: (pl.semester_id = pl1.semester_id) + Join Filter: ((pl.enrolled_students < pl1.enrolled_students) OR ((pl.enrolled_students = pl1.enrolled_students) AND (pl.graded_students < pl1.graded_students)) OR ((pl.enrolled_students = pl1.enrolled_students) AND (pl.graded_students = pl1.graded_students) AND (pl.professor_id > pl1.professor_id))) + Rows Removed by Join Filter: 40595 + Buffers: shared hit=1021, temp read=282 written=283 + -> CTE Scan on professor_load pl (cost=0.00..48.00 rows=2400 width=32) (actual time=89.757..89.907 rows=2400.00 loops=1) + Storage: Memory Maximum Storage: 164kB + Buffers: shared hit=1021, temp read=282 written=283 + -> Hash (cost=48.00..48.00 rows=2400 width=24) (actual time=20.631..20.631 rows=2400.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 173kB + -> CTE Scan on professor_load pl1 (cost=0.00..48.00 rows=2400 width=24) (actual time=0.001..20.388 rows=2400.00 loops=1) + Storage: Memory Maximum Storage: 164kB + -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.022..0.022 rows=20.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.015..0.017 rows=20.00 loops=1) + Buffers: shared hit=1 + -> Index Scan using users_pkey on users u (cost=0.28..0.45 rows=1 width=26) (actual time=0.003..0.003 rows=1.00 loops=20) + Index Cond: (id = pl.professor_id) + Index Searches: 20 + Buffers: shared hit=60 +Planning: + Buffers: shared hit=276 +Planning Time: 10.957 ms +Execution Time: 121.914 ms +-- OK explain_rep_busiest_professor.sql Index: sql/perf/before_rep_semester_growth.txt =================================================================== --- sql/perf/before_rep_semester_growth.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_rep_semester_growth.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,59 @@ +######## rep_semester_growth ######## +Sort (cost=14550.91..14551.08 rows=67 width=156) (actual time=117.230..117.233 rows=20.00 loops=1) + Sort Key: cur.chrono + Sort Method: quicksort Memory: 26kB + Buffers: shared hit=1026, temp read=361 written=362 + CTE semester_stats + -> GroupAggregate (cost=13136.41..14335.26 rows=20 width=40) (actual time=101.227..116.955 rows=20.00 loops=1) + Group Key: a.id, (((a.year * 10) + CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END)) + Buffers: shared hit=1022, temp read=361 written=362 + -> Sort (cost=13136.41..13336.16 rows=79900 width=28) (actual time=100.465..106.806 rows=80000.00 loops=1) + Sort Key: a.id, (((a.year * 10) + CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END)), es.id + Sort Method: external merge Disk: 2888kB + Buffers: shared hit=1022, temp read=361 written=362 + -> Hash Left Join (cost=2132.45..4715.69 rows=79900 width=28) (actual time=13.233..57.501 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1022 + -> Hash Right Join (cost=515.45..2289.69 rows=79900 width=20) (actual time=3.240..33.159 rows=80000.00 loops=1) + Hash Cond: (es.semester_id = a.id) + Buffers: shared hit=665 + -> Hash Right Join (cost=514.00..2034.06 rows=79900 width=12) (actual time=3.221..21.944 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.009..4.461 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.159..3.159 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.010..1.817 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.015..0.016 rows=20.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.010..0.011 rows=20.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.895..9.895 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=8) (actual time=0.009..4.658 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Nested Loop Left Join (cost=0.00..213.62 rows=67 width=156) (actual time=117.030..117.221 rows=20.00 loops=1) + Join Filter: ((prev.chrono < cur.chrono) AND (NOT EXISTS(SubPlan 2))) + Rows Removed by Join Filter: 381 + Buffers: shared hit=1026, temp read=361 written=362 + -> CTE Scan on semester_stats cur (cost=0.00..0.40 rows=20 width=36) (actual time=101.230..101.231 rows=20.00 loops=1) + Storage: Memory Maximum Storage: 18kB + Buffers: shared hit=1022, temp read=336 written=362 + -> CTE Scan on semester_stats prev (cost=0.00..0.40 rows=20 width=20) (actual time=0.000..0.039 rows=20.00 loops=20) + Storage: Memory Maximum Storage: 18kB + SubPlan 2 + -> CTE Scan on semester_stats mid (cost=0.00..0.50 rows=1 width=0) (actual time=0.079..0.079 rows=0.90 loops=190) + Filter: ((chrono < cur.chrono) AND (chrono > prev.chrono)) + Rows Removed by Filter: 8 + Storage: Memory Maximum Storage: 18kB + Buffers: temp read=25 +Planning: + Buffers: shared hit=245 +Planning Time: 7.402 ms +Execution Time: 124.040 ms +-- OK explain_rep_semester_growth.sql Index: sql/perf/before_rep_student_dossier.txt =================================================================== --- sql/perf/before_rep_student_dossier.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_rep_student_dossier.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,143 @@ +######## rep_student_dossier ######## +Sort (cost=10637.88..10647.88 rows=4000 width=167) (actual time=186.541..186.663 rows=4000.00 loops=1) + Sort Key: (COALESCE(ps.credits, '0'::bigint)) DESC, (COALESCE(ps.average_grade, '0'::numeric)) DESC, u.index + Sort Method: quicksort Memory: 597kB + Buffers: shared hit=74402 + -> Hash Left Join (cost=10176.50..10398.56 rows=4000 width=167) (actual time=161.352..166.897 rows=4000.00 loops=1) + Hash Cond: (u.id = doc.user_id) + Buffers: shared hit=74393 + -> Hash Left Join (cost=9941.26..10112.81 rows=4000 width=113) (actual time=158.207..162.271 rows=4000.00 loops=1) + Hash Cond: (u.id = pay.user_id) + Buffers: shared hit=74374 + -> Hash Left Join (cost=8928.25..9089.28 rows=4000 width=105) (actual time=147.952..151.215 rows=4000.00 loops=1) + Hash Cond: (u.id = pen.user_id) + Buffers: shared hit=74133 + -> Hash Left Join (cost=5790.87..5941.40 rows=4000 width=97) (actual time=85.625..88.142 rows=4000.00 loops=1) + Hash Cond: (u.id = sem.user_id) + Buffers: shared hit=1266 + -> Hash Left Join (cost=5226.87..5366.89 rows=4000 width=81) (actual time=80.270..81.958 rows=4000.00 loops=1) + Hash Cond: (u.id = ps.user_id) + Buffers: shared hit=1112 + -> Seq Scan on users u (cost=0.00..129.50 rows=4000 width=33) (actual time=0.030..0.858 rows=4000.00 loops=1) + Filter: (role = 'student'::user_role) + Rows Removed by Filter: 120 + Buffers: shared hit=78 + -> Hash (cost=5176.87..5176.87 rows=4000 width=52) (actual time=80.229..80.233 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 267kB + Buffers: shared hit=1034 + -> Subquery Scan on ps (cost=5076.87..5176.87 rows=4000 width=52) (actual time=77.647..79.556 rows=4000.00 loops=1) + Buffers: shared hit=1034 + -> HashAggregate (cost=5076.87..5136.87 rows=4000 width=52) (actual time=77.646..79.272 rows=4000.00 loops=1) + Group Key: es.user_id + Batches: 1 Memory Usage: 1169kB + Buffers: shared hit=1034 + -> Hash Join (cost=2140.75..3956.87 rows=56000 width=16) (actual time=18.728..62.203 rows=56000.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Buffers: shared hit=1024 + -> Hash Join (cost=2131.00..3798.04 rows=56000 width=16) (actual time=18.661..53.692 rows=56000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=1021 + -> Hash Join (cost=1617.00..3137.01 rows=56000 width=16) (actual time=13.980..38.382 rows=56000.00 loops=1) + Hash Cond: (ss.id = ps_1.enrolled_id) + Buffers: shared hit=867 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.032..5.444 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=13.597..13.598 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2919kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps_1 (cost=0.00..917.00 rows=56000 width=12) (actual time=0.042..6.293 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=4.624..4.625 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.009..2.305 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.061..0.061 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 20kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=8) (actual time=0.009..0.034 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Hash (cost=514.00..514.00 rows=4000 width=20) (actual time=5.335..5.336 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 236kB + Buffers: shared hit=154 + -> Subquery Scan on sem (cost=434.00..514.00 rows=4000 width=20) (actual time=3.543..4.578 rows=4000.00 loops=1) + Buffers: shared hit=154 + -> HashAggregate (cost=434.00..474.00 rows=4000 width=20) (actual time=3.543..4.248 rows=4000.00 loops=1) + Group Key: es_1.user_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es_1 (cost=0.00..314.00 rows=16000 width=16) (actual time=0.029..0.677 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=3137.36..3137.36 rows=1 width=12) (actual time=62.317..62.320 rows=2000.00 loops=1) + Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB + Buffers: shared hit=72867 + -> Subquery Scan on pen (cost=3137.33..3137.36 rows=1 width=12) (actual time=59.122..62.107 rows=2000.00 loops=1) + Buffers: shared hit=72867 + -> GroupAggregate (cost=3137.33..3137.35 rows=1 width=12) (actual time=59.121..62.000 rows=2000.00 loops=1) + Group Key: es_2.user_id + Buffers: shared hit=72867 + -> Sort (cost=3137.33..3137.34 rows=1 width=8) (actual time=59.115..59.766 rows=24000.00 loops=1) + Sort Key: es_2.user_id + Sort Method: quicksort Memory: 1331kB + Buffers: shared hit=72867 + -> Nested Loop (cost=1617.29..3137.32 rows=1 width=8) (actual time=9.689..55.136 rows=24000.00 loops=1) + Buffers: shared hit=72867 + -> Hash Left Join (cost=1617.00..3137.01 rows=1 width=8) (actual time=9.673..27.475 rows=24000.00 loops=1) + Hash Cond: (ss_1.id = ps_2.enrolled_id) + Filter: (ps_2.id IS NULL) + Rows Removed by Filter: 56000 + Buffers: shared hit=867 + -> Seq Scan on semesters_subjects ss_1 (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.008..3.543 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.557..9.558 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps_2 (cost=0.00..917.00 rows=56000 width=8) (actual time=0.013..3.823 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Index Scan using enrolled_semesters_pkey on enrolled_semesters es_2 (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=1.00 loops=24000) + Index Cond: (id = ss_1.enrolled_semesters_id) + Index Searches: 24000 + Buffers: shared hit=72000 + -> Hash (cost=963.01..963.01 rows=4000 width=12) (actual time=10.248..10.250 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 204kB + Buffers: shared hit=241 + -> Subquery Scan on pay (cost=883.01..963.01 rows=4000 width=12) (actual time=9.040..9.722 rows=4000.00 loops=1) + Buffers: shared hit=241 + -> HashAggregate (cost=883.01..923.01 rows=4000 width=12) (actual time=9.039..9.466 rows=4000.00 loops=1) + Group Key: es_3.user_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=241 + -> Hash Join (cost=514.00..803.01 rows=16000 width=8) (actual time=2.494..6.802 rows=16000.00 loops=1) + Hash Cond: (p.enrollment_id = es_3.id) + Buffers: shared hit=241 + -> Seq Scan on payment p (cost=0.00..247.00 rows=16000 width=8) (actual time=0.028..0.794 rows=16000.00 loops=1) + Buffers: shared hit=87 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=2.457..2.457 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es_3 (cost=0.00..314.00 rows=16000 width=8) (actual time=0.007..1.064 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=185.24..185.24 rows=4000 width=20) (actual time=3.117..3.118 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 236kB + Buffers: shared hit=19 + -> Subquery Scan on doc (cost=105.24..185.24 rows=4000 width=20) (actual time=1.968..2.671 rows=4000.00 loops=1) + Buffers: shared hit=19 + -> HashAggregate (cost=105.24..145.24 rows=4000 width=20) (actual time=1.967..2.401 rows=4000.00 loops=1) + Group Key: ud.user_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=19 + -> Hash Join (cost=1.18..75.24 rows=4000 width=12) (actual time=0.033..1.051 rows=4000.00 loops=1) + Hash Cond: (ud.document_id = d.id) + Buffers: shared hit=19 + -> Seq Scan on user_documents ud (cost=0.00..58.00 rows=4000 width=8) (actual time=0.013..0.274 rows=4000.00 loops=1) + Buffers: shared hit=18 + -> Hash (cost=1.08..1.08 rows=8 width=8) (actual time=0.012..0.012 rows=8.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on documents d (cost=0.00..1.08 rows=8 width=8) (actual time=0.007..0.008 rows=8.00 loops=1) + Buffers: shared hit=1 +Planning: + Buffers: shared hit=453 +Planning Time: 16.655 ms +Execution Time: 188.547 ms +-- OK explain_rep_student_dossier.sql Index: sql/perf/before_rep_subject_pass_rate.txt =================================================================== --- sql/perf/before_rep_subject_pass_rate.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_rep_subject_pass_rate.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,94 @@ +######## rep_subject_pass_rate ######## +Sort (cost=12506.58..12508.08 rows=600 width=146) (actual time=144.585..144.826 rows=6000.00 loops=1) + Sort Key: a.year, a.type, (round(((100.0 * ((count(ps.id)))::numeric) / ((count(ss.id)))::numeric), 1)) DESC, s.code + Sort Method: quicksort Memory: 747kB + Buffers: shared hit=2577 + -> Hash Left Join (cost=12291.46..12478.89 rows=600 width=146) (actual time=121.918..131.279 rows=6000.00 loops=1) + Hash Cond: (ss.subjects_id = sa.subject_id) + Buffers: shared hit=2566 + -> Hash Left Join (cost=8303.20..8474.03 rows=600 width=86) (actual time=87.591..93.282 rows=6000.00 loops=1) + Hash Cond: ((ss.subjects_id = sps.subject_id) AND (es.semester_id = sps.semester_id)) + Buffers: shared hit=1699 + -> Hash Join (cost=5672.27..5839.95 rows=600 width=82) (actual time=60.453..64.892 rows=6000.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Buffers: shared hit=1035 + -> Hash Join (cost=5662.52..5828.60 rows=600 width=64) (actual time=60.342..63.712 rows=6000.00 loops=1) + Hash Cond: (es.semester_id = a.id) + Buffers: shared hit=1032 + -> HashAggregate (cost=5661.07..5751.07 rows=6000 width=56) (actual time=60.314..62.727 rows=6000.00 loops=1) + Group Key: ss.subjects_id, es.semester_id + Batches: 1 Memory Usage: 1169kB + Buffers: shared hit=1031 + -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=14.127..42.476 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1021 + -> Hash Join (cost=514.00..2034.06 rows=80000 width=12) (actual time=4.015..21.140 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.007..3.658 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.885..3.886 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.011..2.191 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=10.032..10.037 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2919kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=12) (actual time=0.011..4.648 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.020..0.021 rows=20.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.013..0.014 rows=20.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.106..0.107 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 25kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=22) (actual time=0.022..0.058 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Hash (cost=2540.93..2540.93 rows=6000 width=16) (actual time=27.095..27.097 rows=6000.00 loops=1) + Buckets: 8192 Batches: 1 Memory Usage: 346kB + Buffers: shared hit=664 + -> Subquery Scan on sps (cost=2420.93..2540.93 rows=6000 width=16) (actual time=24.841..25.970 rows=6000.00 loops=1) + Buffers: shared hit=664 + -> HashAggregate (cost=2420.93..2480.93 rows=6000 width=16) (actual time=24.840..25.553 rows=6000.00 loops=1) + Group Key: ss_1.subjects_id, es_1.semester_id + Batches: 1 Memory Usage: 409kB + Buffers: shared hit=664 + -> Hash Join (cost=514.00..1978.79 rows=58952 width=12) (actual time=2.722..16.183 rows=58801.00 loops=1) + Hash Cond: (ss_1.enrolled_semesters_id = es_1.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss_1 (cost=0.00..1310.00 rows=58952 width=12) (actual time=0.013..5.469 rows=58801.00 loops=1) + Filter: signature + Rows Removed by Filter: 21199 + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=2.674..2.675 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 753kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es_1 (cost=0.00..314.00 rows=16000 width=8) (actual time=0.007..1.310 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=3984.51..3984.51 rows=300 width=36) (actual time=34.300..34.301 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 21kB + Buffers: shared hit=867 + -> Subquery Scan on sa (cost=3977.01..3984.51 rows=300 width=36) (actual time=34.162..34.262 rows=300.00 loops=1) + Buffers: shared hit=867 + -> HashAggregate (cost=3977.01..3981.51 rows=300 width=36) (actual time=34.162..34.248 rows=300.00 loops=1) + Group Key: ss_2.subjects_id + Batches: 1 Memory Usage: 80kB + Buffers: shared hit=867 + -> Hash Join (cost=1617.00..3137.01 rows=56000 width=8) (actual time=9.343..23.919 rows=56000.00 loops=1) + Hash Cond: (ss_2.id = ps_1.enrolled_id) + Buffers: shared hit=867 + -> Seq Scan on semesters_subjects ss_2 (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.018..2.738 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.245..9.245 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2700kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps_1 (cost=0.00..917.00 rows=56000 width=8) (actual time=0.008..3.894 rows=56000.00 loops=1) + Buffers: shared hit=357 +Planning: + Buffers: shared hit=355 +Planning Time: 11.195 ms +Execution Time: 146.018 ms +-- OK explain_rep_subject_pass_rate.sql Index: sql/perf/before_rep_top_student_per_major.txt =================================================================== --- sql/perf/before_rep_top_student_per_major.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/before_rep_top_student_per_major.txt (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,69 @@ +######## rep_top_student_per_major ######## +Sort (cost=6208.33..6208.56 rows=91 width=95) (actual time=106.513..106.518 rows=6.00 loops=1) + Sort Key: m.name + Sort Method: quicksort Memory: 25kB + Buffers: shared hit=1056 + CTE standing + -> HashAggregate (cost=5883.79..5943.79 rows=4000 width=56) (actual time=92.287..93.895 rows=4000.00 loops=1) + Group Key: es.user_id, es.major_id + Batches: 1 Memory Usage: 1169kB + Buffers: shared hit=1034 + -> Hash Left Join (cost=2140.75..4083.79 rows=80000 width=20) (actual time=16.730..71.128 rows=80000.00 loops=1) + Hash Cond: (ss.subjects_id = s.id) + Join Filter: (ps.id IS NOT NULL) + Rows Removed by Join Filter: 24000 + Buffers: shared hit=1024 + -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=16.601..57.953 rows=80000.00 loops=1) + Hash Cond: (ss.id = ps.enrolled_id) + Buffers: shared hit=1021 + -> Hash Right Join (cost=514.00..2034.06 rows=80000 width=16) (actual time=4.275..27.868 rows=80000.00 loops=1) + Hash Cond: (ss.enrolled_semesters_id = es.id) + Buffers: shared hit=664 + -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.013..5.272 rows=80000.00 loops=1) + Buffers: shared hit=510 + -> Hash (cost=314.00..314.00 rows=16000 width=12) (actual time=4.208..4.209 rows=16000.00 loops=1) + Buckets: 16384 Batches: 1 Memory Usage: 816kB + Buffers: shared hit=154 + -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=12) (actual time=0.020..2.209 rows=16000.00 loops=1) + Buffers: shared hit=154 + -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=12.206..12.207 rows=56000.00 loops=1) + Buckets: 65536 Batches: 1 Memory Usage: 2919kB + Buffers: shared hit=357 + -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=12) (actual time=0.012..5.719 rows=56000.00 loops=1) + Buffers: shared hit=357 + -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.126..0.126 rows=300.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 20kB + Buffers: shared hit=3 + -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=8) (actual time=0.037..0.065 rows=300.00 loops=1) + Buffers: shared hit=3 + -> Nested Loop (cost=131.42..261.59 rows=91 width=95) (actual time=98.002..106.481 rows=6.00 loops=1) + Buffers: shared hit=1053 + -> Hash Anti Join (cost=131.13..225.46 rows=91 width=60) (actual time=97.975..106.434 rows=6.00 loops=1) + Hash Cond: (st.major_id = st1.major_id) + Join Filter: ((st.credits < st1.credits) OR ((st.credits = st1.credits) AND (st.average_grade < st1.average_grade)) OR ((st.credits = st1.credits) AND (st.average_grade = st1.average_grade) AND (st.user_id > st1.user_id))) + Rows Removed by Join Filter: 28423 + Buffers: shared hit=1035 + -> Hash Join (cost=1.14..91.86 rows=120 width=64) (actual time=92.353..93.570 rows=4000.00 loops=1) + Hash Cond: (st.major_id = m.id) + Buffers: shared hit=1035 + -> CTE Scan on standing st (cost=0.00..80.00 rows=4000 width=56) (actual time=92.293..92.600 rows=4000.00 loops=1) + Storage: Memory Maximum Storage: 251kB + Buffers: shared hit=1034 + -> Hash (cost=1.06..1.06 rows=6 width=12) (actual time=0.051..0.051 rows=6.00 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=1 + -> Seq Scan on major m (cost=0.00..1.06 rows=6 width=12) (actual time=0.045..0.046 rows=6.00 loops=1) + Buffers: shared hit=1 + -> Hash (cost=80.00..80.00 rows=4000 width=48) (actual time=3.222..3.222 rows=4000.00 loops=1) + Buckets: 4096 Batches: 1 Memory Usage: 267kB + -> CTE Scan on standing st1 (cost=0.00..80.00 rows=4000 width=48) (actual time=0.001..2.644 rows=4000.00 loops=1) + Storage: Memory Maximum Storage: 251kB + -> Index Scan using users_pkey on users u (cost=0.28..0.39 rows=1 width=33) (actual time=0.006..0.006 rows=1.00 loops=6) + Index Cond: (id = st.user_id) + Index Searches: 6 + Buffers: shared hit=18 +Planning: + Buffers: shared hit=335 +Planning Time: 10.504 ms +Execution Time: 107.303 ms +-- OK explain_rep_top_student_per_major.sql Index: sql/perf/indexes.sql =================================================================== --- sql/perf/indexes.sql (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/indexes.sql (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,20 @@ +SET search_path TO perf; + +-- Foreign keys with no supporting index. PostgreSQL indexes primary keys and +-- UNIQUE constraints automatically, but never the referencing side of a +-- foreign key, and a composite index only helps queries that use its +-- leading column. +CREATE INDEX IF NOT EXISTS ix_semesters_subjects_subject ON semesters_subjects (subjects_id); +CREATE INDEX IF NOT EXISTS ix_semesters_subjects_professor ON semesters_subjects (professor_id); +CREATE INDEX IF NOT EXISTS ix_enrolled_semesters_semester ON enrolled_semesters (semester_id); +CREATE INDEX IF NOT EXISTS ix_enrolled_semesters_major ON enrolled_semesters (major_id); +CREATE INDEX IF NOT EXISTS ix_payment_enrollment ON payment (enrollment_id); +CREATE INDEX IF NOT EXISTS ix_major_subjects_subject ON major_subjects (subject_id); +CREATE INDEX IF NOT EXISTS ix_professour_subjects_semester ON professour_subjects (active_semester_id, subject_id); +CREATE INDEX IF NOT EXISTS ix_user_documents_document ON user_documents (document_id); +CREATE INDEX IF NOT EXISTS ix_token_user ON token (user_id); + +-- Supports the very common "this student's grades" path without touching the heap. +CREATE INDEX IF NOT EXISTS ix_passed_subjects_enrolled_grade ON passed_subjects (enrolled_id, grade); + +ANALYZE; Index: sql/perf/perf_data.sql =================================================================== --- sql/perf/perf_data.sql (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/perf_data.sql (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,128 @@ +-- Bulk test data for the Phase 9 performance analysis. +-- Generated in the `perf` schema so the official `project` data is untouched. +SET search_path TO perf; + +-- ---------- faculties of scale ---------- +INSERT INTO major (id, name) +SELECT g, 'Major ' || g FROM generate_series(1, 6) g; + +INSERT INTO active_semesters (id, year, type) +SELECT row_number() OVER (), y, t +FROM generate_series(2016, 2025) y +CROSS JOIN (VALUES ('winter'::semester_type), ('summer'::semester_type)) AS s(t); + +-- 300 subjects +INSERT INTO subjects (id, name, code, awarded_credits, dependency_credit) +SELECT g, + 'Subject ' || g, + 'S' || lpad(g::TEXT, 5, '0'), + 6, + CASE WHEN g % 3 = 0 THEN 6 ELSE 0 END +FROM generate_series(1, 300) g; + +-- every subject offered by two programmes +INSERT INTO major_subjects (major_id, subject_id, mandatory_semester) +SELECT 1 + (g % 6), g, 1 + (g % 8) FROM generate_series(1, 300) g +UNION +SELECT 1 + ((g + 1) % 6), g, 1 + (g % 8) FROM generate_series(1, 300) g; + +-- ---------- people ---------- +-- 120 professors +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) +SELECT g, + 'Prof' || g, lpad(g::TEXT, 13, '0'), 'Surname' || g, NULL, + '1975-01-01'::TIMESTAMP, + 'prof' || g || '@finki.ukim.mk', 'hash', 'prof', NULL, NULL +FROM generate_series(1, 120) g; + +-- 4000 students +INSERT INTO users (id, name, embg, surname, "index", bday, email, password, role, quota, enrollment_year) +SELECT 1000 + g, + 'Student' || g, lpad((1000 + g)::TEXT, 13, '0'), 'Student' || g, + lpad((200000 + g)::TEXT, 6, '0'), + '2003-01-01'::TIMESTAMP, + 'student' || g || '@students.finki.ukim.mk', 'hash', 'student', + CASE WHEN g % 2 = 0 THEN 'drzavna'::quota_type ELSE 'privatna'::quota_type END, + 2018 + (g % 7) +FROM generate_series(1, 4000) g; + +INSERT INTO contact (user_id, city, municipality, address, number) +SELECT 1000 + g, 'Skopje', 'Centar', 'Street ' || g, '070' || lpad(g::TEXT, 6, '0') +FROM generate_series(1, 4000) g; + +INSERT INTO high_school (user_id, gpa, type) +SELECT 1000 + g, 3.0 + (g % 20) / 10.0, + CASE WHEN g % 2 = 0 THEN 'gimnazija'::hs_type ELSE 'strucen'::hs_type END +FROM generate_series(1, 4000) g; + +-- ---------- teaching schedule ---------- +-- every (semester, subject) pair taught by one professor +INSERT INTO professour_subjects (prof_id, active_semester_id, subject_id) +SELECT 1 + ((s.id * 7 + sub.id) % 120), s.id, sub.id +FROM active_semesters s CROSS JOIN subjects sub; + +-- ---------- enrolments ---------- +-- each student enrols in 4 consecutive semesters +INSERT INTO enrolled_semesters (user_id, quota, major_id, semester_id, created_at, last_change, completed) +SELECT 1000 + g, + CASE WHEN g % 2 = 0 THEN 'drzavna'::quota_type ELSE 'privatna'::quota_type END, + 1 + (g % 6), + sem, + now() - (interval '1 day' * (g % 900)), + now() - (interval '1 day' * (g % 400)), + CASE WHEN sem % 3 = 0 THEN now() - interval '30 days' ELSE NULL END +FROM generate_series(1, 4000) g +CROSS JOIN LATERAL ( + SELECT 1 + ((g + k) % 20) AS sem FROM generate_series(0, 3) k +) s +ON CONFLICT (user_id, semester_id) DO NOTHING; + +-- five subjects per enrolment +INSERT INTO semesters_subjects (enrolled_semesters_id, subjects_id, professor_id, signature) +SELECT es.id, + ps.subject_id, + ps.prof_id, + (es.id + ps.subject_id) % 4 <> 0 +FROM enrolled_semesters es +CROSS JOIN LATERAL ( + SELECT p.subject_id, p.prof_id + FROM professour_subjects p + WHERE p.active_semester_id = es.semester_id + AND p.subject_id IN ( + SELECT ms.subject_id FROM major_subjects ms WHERE ms.major_id = es.major_id + ) + ORDER BY (p.subject_id * 13 + es.id) % 997 + LIMIT 5 +) ps +ON CONFLICT (enrolled_semesters_id, subjects_id) DO NOTHING; + +-- roughly 70% of enrolled subjects are passed +INSERT INTO passed_subjects (enrolled_id, grade, date_passed) +SELECT ss.id, + (ARRAY['6','7','8','9','10']::grade_type[])[1 + (ss.id % 5)], + now() - (interval '1 day' * (ss.id % 800)) +FROM semesters_subjects ss +WHERE ss.id % 10 < 7 +ON CONFLICT (enrolled_id) DO NOTHING; + +-- ---------- payments and documents ---------- +INSERT INTO payment (enrollment_id, amount) +SELECT es.id, 200 + (es.id % 5) * 100 +FROM enrolled_semesters es; + +INSERT INTO documents (id, type, body, cost) +SELECT g, 'Document type ' || g, 'Body of document ' || g, 50 * g +FROM generate_series(1, 8) g; + +INSERT INTO user_documents (user_id, document_id) +SELECT 1000 + g, 1 + (g % 8) FROM generate_series(1, 4000) g +ON CONFLICT DO NOTHING; + +-- sequences must catch up with the explicit ids +SELECT setval(pg_get_serial_sequence('perf.users', 'id'), (SELECT max(id) FROM users)); +SELECT setval(pg_get_serial_sequence('perf.major', 'id'), (SELECT max(id) FROM major)); +SELECT setval(pg_get_serial_sequence('perf.active_semesters', 'id'), (SELECT max(id) FROM active_semesters)); +SELECT setval(pg_get_serial_sequence('perf.subjects', 'id'), (SELECT max(id) FROM subjects)); +SELECT setval(pg_get_serial_sequence('perf.documents', 'id'), (SELECT max(id) FROM documents)); + +ANALYZE; Index: sql/perf/perf_schema.sql =================================================================== --- sql/perf/perf_schema.sql (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) +++ sql/perf/perf_schema.sql (revision 2853f2ebbc643ed265db27aa0af19878c14db48c) @@ -0,0 +1,140 @@ +DROP SCHEMA IF EXISTS perf CASCADE; +CREATE SCHEMA perf; + +SET search_path TO perf; + +CREATE TYPE user_role AS ENUM ('student', 'prof', 'admin'); +CREATE TYPE hs_type AS ENUM ('strucen', 'gimnazija'); +CREATE TYPE quota_type AS ENUM ('privatna', 'drzavna', 'stipendija'); +CREATE TYPE semester_type AS ENUM ('summer', 'winter'); +CREATE TYPE grade_type AS ENUM ('6', '7', '8', '9', '10'); + +CREATE TABLE users ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, + name VARCHAR(100) NOT NULL, + embg VARCHAR(13), + surname VARCHAR(100) NOT NULL, + "index" VARCHAR(20), + bday TIMESTAMP, + email VARCHAR(150) NOT NULL UNIQUE, + password VARCHAR(255) NOT NULL, + role user_role NOT NULL, + quota quota_type, + enrollment_year INTEGER, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE TABLE high_school ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL UNIQUE REFERENCES users (id), + gpa REAL NOT NULL, + type hs_type NOT NULL +); + +CREATE TABLE contact ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL UNIQUE REFERENCES users (id), + city VARCHAR(100) NOT NULL, + municipality VARCHAR(100) NOT NULL, + address VARCHAR(150) NOT NULL, + number VARCHAR(20) NOT NULL, + microsoft_email VARCHAR(150) +); + +CREATE TABLE major ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name VARCHAR(150) NOT NULL +); + +CREATE TABLE active_semesters ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + year INTEGER NOT NULL, + type semester_type NOT NULL, + UNIQUE (year, type) +); + +CREATE TABLE subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name VARCHAR(150) NOT NULL UNIQUE, + code VARCHAR(30) NOT NULL UNIQUE, + awarded_credits INTEGER NOT NULL, + dependency_credit INTEGER +); + +CREATE TABLE enrolled_semesters ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + quota quota_type NOT NULL, + major_id INTEGER NOT NULL REFERENCES major (id), + note VARCHAR(255), + student_comment VARCHAR(255), + created_at TIMESTAMP NOT NULL DEFAULT now(), + last_change TIMESTAMP, + completed TIMESTAMP, + semester_id INTEGER NOT NULL REFERENCES active_semesters (id), + UNIQUE (user_id, semester_id) +); + +CREATE TABLE semesters_subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrolled_semesters_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), + subjects_id INTEGER NOT NULL REFERENCES subjects (id), + professor_id INTEGER NOT NULL REFERENCES users (id), + signature BOOLEAN NOT NULL DEFAULT FALSE, + UNIQUE (enrolled_semesters_id, subjects_id) +); + +CREATE TABLE passed_subjects ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrolled_id INTEGER NOT NULL UNIQUE REFERENCES semesters_subjects (id), + grade grade_type NOT NULL, + date_passed TIMESTAMP NOT NULL +); + +CREATE TABLE documents ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + type VARCHAR(100) NOT NULL, + body TEXT NOT NULL, + cost INTEGER NOT NULL +); + +CREATE TABLE user_documents ( + user_id INTEGER NOT NULL REFERENCES users (id), + document_id INTEGER NOT NULL REFERENCES documents (id), + PRIMARY KEY (user_id, document_id) +); + +CREATE TABLE token ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + user_id INTEGER NOT NULL REFERENCES users (id), + token VARCHAR(255) NOT NULL, + expires_at TIMESTAMP NOT NULL, + is_valid BOOLEAN NOT NULL DEFAULT TRUE +); + +CREATE TABLE payment ( + id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + enrollment_id INTEGER NOT NULL REFERENCES enrolled_semesters (id), + amount INTEGER NOT NULL +); + +CREATE TABLE major_subjects ( + major_id INTEGER NOT NULL REFERENCES major (id), + subject_id INTEGER NOT NULL REFERENCES subjects (id), + mandatory_semester INTEGER NOT NULL, + PRIMARY KEY (major_id, subject_id) +); + +CREATE TABLE dependency_subject ( + subject_id INTEGER NOT NULL REFERENCES subjects (id), + dependency_id INTEGER NOT NULL REFERENCES subjects (id), + PRIMARY KEY (subject_id, dependency_id), + CHECK (subject_id <> dependency_id) +); + +CREATE TABLE professour_subjects ( + prof_id INTEGER NOT NULL REFERENCES users (id), + active_semester_id INTEGER NOT NULL REFERENCES active_semesters (id), + subject_id INTEGER NOT NULL REFERENCES subjects (id), + PRIMARY KEY (prof_id, active_semester_id, subject_id) +);