source: sql/perf/after_dossier_one.txt

Last change on this file was 2853f2e, checked in by Stefan-Saveski <stefansaveski19@…>, 11 hours ago

Add performance analysis scripts and schema for educational database

  • Created performance analysis scripts for student dossier, subject pass rate, and top students per major.
  • Added indexes to improve query performance on key tables.
  • Generated bulk test data for performance analysis in the 'perf' schema.
  • Established the 'perf' schema with necessary tables and types for educational data management.
  • Property mode set to 100644
File size: 10.3 KB
Line 
1######## dossier_one ########
2Sort (cost=260.44..260.44 rows=1 width=167) (actual time=0.869..0.872 rows=1.00 loops=1)
3 Sort Key: (COALESCE((sum(s.awarded_credits)), '0'::bigint)) DESC, (COALESCE((round(avg(((ps.grade)::text)::integer), 2)), '0'::numeric)) DESC, u.index
4 Sort Method: quicksort Memory: 25kB
5 Buffers: shared hit=188 read=4
6 -> Nested Loop Left Join (cost=23.45..260.43 rows=1 width=167) (actual time=0.841..0.846 rows=1.00 loops=1)
7 Buffers: shared hit=179 read=4
8 -> Nested Loop Left Join (cost=19.14..254.96 rows=1 width=113) (actual time=0.729..0.733 rows=1.00 loops=1)
9 Buffers: shared hit=175 read=4
10 -> Nested Loop Left Join (cost=14.54..203.24 rows=1 width=105) (actual time=0.300..0.303 rows=1.00 loops=1)
11 Buffers: shared hit=161
12 -> Nested Loop Left Join (cost=9.64..116.19 rows=1 width=97) (actual time=0.246..0.249 rows=1.00 loops=1)
13 Buffers: shared hit=95
14 -> Nested Loop Left Join (cost=5.33..97.71 rows=1 width=81) (actual time=0.231..0.233 rows=1.00 loops=1)
15 Buffers: shared hit=89
16 -> 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)
17 Index Cond: (id = 2500)
18 Filter: (role = 'student'::user_role)
19 Index Searches: 1
20 Buffers: shared hit=3
21 -> GroupAggregate (cost=5.05..89.39 rows=1 width=52) (actual time=0.216..0.217 rows=1.00 loops=1)
22 Buffers: shared hit=86
23 -> Nested Loop (cost=5.05..89.13 rows=14 width=16) (actual time=0.078..0.175 rows=8.00 loops=1)
24 Buffers: shared hit=82
25 -> Nested Loop (cost=4.90..86.81 rows=14 width=16) (actual time=0.074..0.160 rows=8.00 loops=1)
26 Buffers: shared hit=66
27 -> Nested Loop (cost=4.61..80.15 rows=20 width=12) (actual time=0.055..0.102 rows=20.00 loops=1)
28 Buffers: shared hit=18
29 -> 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)
30 Recheck Cond: (user_id = 2500)
31 Heap Blocks: exact=4
32 Buffers: shared hit=6
33 -> 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)
34 Index Cond: (user_id = 2500)
35 Index Searches: 1
36 Buffers: shared hit=2
37 -> 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)
38 Index Cond: (enrolled_semesters_id = es.id)
39 Index Searches: 4
40 Buffers: shared hit=12
41 -> 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)
42 Index Cond: (enrolled_id = ss.id)
43 Index Searches: 20
44 Buffers: shared hit=48
45 -> 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)
46 Index Cond: (id = ss.subjects_id)
47 Index Searches: 8
48 Buffers: shared hit=16
49 -> GroupAggregate (cost=4.32..18.46 rows=1 width=20) (actual time=0.015..0.015 rows=1.00 loops=1)
50 Buffers: shared hit=6
51 -> 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)
52 Recheck Cond: (user_id = 2500)
53 Heap Blocks: exact=4
54 Buffers: shared hit=6
55 -> 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)
56 Index Cond: (user_id = 2500)
57 Index Searches: 1
58 Buffers: shared hit=2
59 -> GroupAggregate (cost=4.90..87.03 rows=1 width=12) (actual time=0.053..0.054 rows=1.00 loops=1)
60 Buffers: shared hit=66
61 -> Nested Loop Left Join (cost=4.90..87.01 rows=1 width=8) (actual time=0.027..0.052 rows=12.00 loops=1)
62 Filter: (ps_1.id IS NULL)
63 Rows Removed by Filter: 8
64 Buffers: shared hit=66
65 -> Nested Loop (cost=4.61..80.15 rows=20 width=8) (actual time=0.014..0.023 rows=20.00 loops=1)
66 Buffers: shared hit=18
67 -> 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)
68 Recheck Cond: (user_id = 2500)
69 Heap Blocks: exact=4
70 Buffers: shared hit=6
71 -> 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)
72 Index Cond: (user_id = 2500)
73 Index Searches: 1
74 Buffers: shared hit=2
75 -> 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)
76 Index Cond: (enrolled_semesters_id = es_2.id)
77 Index Searches: 4
78 Buffers: shared hit=12
79 -> 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)
80 Index Cond: (enrolled_id = ss_1.id)
81 Index Searches: 20
82 Buffers: shared hit=48
83 -> GroupAggregate (cost=4.60..51.70 rows=1 width=12) (actual time=0.428..0.428 rows=1.00 loops=1)
84 Buffers: shared hit=14 read=4
85 -> Nested Loop (cost=4.60..51.68 rows=4 width=8) (actual time=0.299..0.426 rows=4.00 loops=1)
86 Buffers: shared hit=14 read=4
87 -> 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)
88 Recheck Cond: (user_id = 2500)
89 Heap Blocks: exact=4
90 Buffers: shared hit=6
91 -> 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)
92 Index Cond: (user_id = 2500)
93 Index Searches: 1
94 Buffers: shared hit=2
95 -> 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)
96 Index Cond: (enrollment_id = es_3.id)
97 Index Searches: 4
98 Buffers: shared hit=8 read=4
99 -> GroupAggregate (cost=4.31..5.44 rows=1 width=20) (actual time=0.105..0.106 rows=1.00 loops=1)
100 Buffers: shared hit=4
101 -> Hash Join (cost=4.31..5.42 rows=1 width=12) (actual time=0.103..0.105 rows=1.00 loops=1)
102 Hash Cond: (d.id = ud.document_id)
103 Buffers: shared hit=4
104 -> 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)
105 Buffers: shared hit=1
106 -> Hash (cost=4.30..4.30 rows=1 width=8) (actual time=0.088..0.088 rows=1.00 loops=1)
107 Buckets: 1024 Batches: 1 Memory Usage: 9kB
108 Buffers: shared hit=3
109 -> 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)
110 Index Cond: (user_id = 2500)
111 Heap Fetches: 0
112 Index Searches: 1
113 Buffers: shared hit=3
114Planning:
115 Buffers: shared hit=563
116Planning Time: 17.012 ms
117Execution Time: 1.067 ms
118-- OK explain_dossier_one.sql
Note: See TracBrowser for help on using the repository browser.