source: sql/perf/before_rep_student_dossier.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: 13.5 KB
Line 
1######## rep_student_dossier ########
2Sort (cost=10637.88..10647.88 rows=4000 width=167) (actual time=186.541..186.663 rows=4000.00 loops=1)
3 Sort Key: (COALESCE(ps.credits, '0'::bigint)) DESC, (COALESCE(ps.average_grade, '0'::numeric)) DESC, u.index
4 Sort Method: quicksort Memory: 597kB
5 Buffers: shared hit=74402
6 -> Hash Left Join (cost=10176.50..10398.56 rows=4000 width=167) (actual time=161.352..166.897 rows=4000.00 loops=1)
7 Hash Cond: (u.id = doc.user_id)
8 Buffers: shared hit=74393
9 -> Hash Left Join (cost=9941.26..10112.81 rows=4000 width=113) (actual time=158.207..162.271 rows=4000.00 loops=1)
10 Hash Cond: (u.id = pay.user_id)
11 Buffers: shared hit=74374
12 -> Hash Left Join (cost=8928.25..9089.28 rows=4000 width=105) (actual time=147.952..151.215 rows=4000.00 loops=1)
13 Hash Cond: (u.id = pen.user_id)
14 Buffers: shared hit=74133
15 -> Hash Left Join (cost=5790.87..5941.40 rows=4000 width=97) (actual time=85.625..88.142 rows=4000.00 loops=1)
16 Hash Cond: (u.id = sem.user_id)
17 Buffers: shared hit=1266
18 -> Hash Left Join (cost=5226.87..5366.89 rows=4000 width=81) (actual time=80.270..81.958 rows=4000.00 loops=1)
19 Hash Cond: (u.id = ps.user_id)
20 Buffers: shared hit=1112
21 -> 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)
22 Filter: (role = 'student'::user_role)
23 Rows Removed by Filter: 120
24 Buffers: shared hit=78
25 -> Hash (cost=5176.87..5176.87 rows=4000 width=52) (actual time=80.229..80.233 rows=4000.00 loops=1)
26 Buckets: 4096 Batches: 1 Memory Usage: 267kB
27 Buffers: shared hit=1034
28 -> Subquery Scan on ps (cost=5076.87..5176.87 rows=4000 width=52) (actual time=77.647..79.556 rows=4000.00 loops=1)
29 Buffers: shared hit=1034
30 -> HashAggregate (cost=5076.87..5136.87 rows=4000 width=52) (actual time=77.646..79.272 rows=4000.00 loops=1)
31 Group Key: es.user_id
32 Batches: 1 Memory Usage: 1169kB
33 Buffers: shared hit=1034
34 -> Hash Join (cost=2140.75..3956.87 rows=56000 width=16) (actual time=18.728..62.203 rows=56000.00 loops=1)
35 Hash Cond: (ss.subjects_id = s.id)
36 Buffers: shared hit=1024
37 -> Hash Join (cost=2131.00..3798.04 rows=56000 width=16) (actual time=18.661..53.692 rows=56000.00 loops=1)
38 Hash Cond: (ss.enrolled_semesters_id = es.id)
39 Buffers: shared hit=1021
40 -> Hash Join (cost=1617.00..3137.01 rows=56000 width=16) (actual time=13.980..38.382 rows=56000.00 loops=1)
41 Hash Cond: (ss.id = ps_1.enrolled_id)
42 Buffers: shared hit=867
43 -> 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)
44 Buffers: shared hit=510
45 -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=13.597..13.598 rows=56000.00 loops=1)
46 Buckets: 65536 Batches: 1 Memory Usage: 2919kB
47 Buffers: shared hit=357
48 -> 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)
49 Buffers: shared hit=357
50 -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=4.624..4.625 rows=16000.00 loops=1)
51 Buckets: 16384 Batches: 1 Memory Usage: 753kB
52 Buffers: shared hit=154
53 -> 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)
54 Buffers: shared hit=154
55 -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.061..0.061 rows=300.00 loops=1)
56 Buckets: 1024 Batches: 1 Memory Usage: 20kB
57 Buffers: shared hit=3
58 -> 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)
59 Buffers: shared hit=3
60 -> Hash (cost=514.00..514.00 rows=4000 width=20) (actual time=5.335..5.336 rows=4000.00 loops=1)
61 Buckets: 4096 Batches: 1 Memory Usage: 236kB
62 Buffers: shared hit=154
63 -> Subquery Scan on sem (cost=434.00..514.00 rows=4000 width=20) (actual time=3.543..4.578 rows=4000.00 loops=1)
64 Buffers: shared hit=154
65 -> HashAggregate (cost=434.00..474.00 rows=4000 width=20) (actual time=3.543..4.248 rows=4000.00 loops=1)
66 Group Key: es_1.user_id
67 Batches: 1 Memory Usage: 409kB
68 Buffers: shared hit=154
69 -> 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)
70 Buffers: shared hit=154
71 -> Hash (cost=3137.36..3137.36 rows=1 width=12) (actual time=62.317..62.320 rows=2000.00 loops=1)
72 Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB
73 Buffers: shared hit=72867
74 -> Subquery Scan on pen (cost=3137.33..3137.36 rows=1 width=12) (actual time=59.122..62.107 rows=2000.00 loops=1)
75 Buffers: shared hit=72867
76 -> GroupAggregate (cost=3137.33..3137.35 rows=1 width=12) (actual time=59.121..62.000 rows=2000.00 loops=1)
77 Group Key: es_2.user_id
78 Buffers: shared hit=72867
79 -> Sort (cost=3137.33..3137.34 rows=1 width=8) (actual time=59.115..59.766 rows=24000.00 loops=1)
80 Sort Key: es_2.user_id
81 Sort Method: quicksort Memory: 1331kB
82 Buffers: shared hit=72867
83 -> Nested Loop (cost=1617.29..3137.32 rows=1 width=8) (actual time=9.689..55.136 rows=24000.00 loops=1)
84 Buffers: shared hit=72867
85 -> Hash Left Join (cost=1617.00..3137.01 rows=1 width=8) (actual time=9.673..27.475 rows=24000.00 loops=1)
86 Hash Cond: (ss_1.id = ps_2.enrolled_id)
87 Filter: (ps_2.id IS NULL)
88 Rows Removed by Filter: 56000
89 Buffers: shared hit=867
90 -> 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)
91 Buffers: shared hit=510
92 -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.557..9.558 rows=56000.00 loops=1)
93 Buckets: 65536 Batches: 1 Memory Usage: 2700kB
94 Buffers: shared hit=357
95 -> 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)
96 Buffers: shared hit=357
97 -> 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)
98 Index Cond: (id = ss_1.enrolled_semesters_id)
99 Index Searches: 24000
100 Buffers: shared hit=72000
101 -> Hash (cost=963.01..963.01 rows=4000 width=12) (actual time=10.248..10.250 rows=4000.00 loops=1)
102 Buckets: 4096 Batches: 1 Memory Usage: 204kB
103 Buffers: shared hit=241
104 -> Subquery Scan on pay (cost=883.01..963.01 rows=4000 width=12) (actual time=9.040..9.722 rows=4000.00 loops=1)
105 Buffers: shared hit=241
106 -> HashAggregate (cost=883.01..923.01 rows=4000 width=12) (actual time=9.039..9.466 rows=4000.00 loops=1)
107 Group Key: es_3.user_id
108 Batches: 1 Memory Usage: 409kB
109 Buffers: shared hit=241
110 -> Hash Join (cost=514.00..803.01 rows=16000 width=8) (actual time=2.494..6.802 rows=16000.00 loops=1)
111 Hash Cond: (p.enrollment_id = es_3.id)
112 Buffers: shared hit=241
113 -> 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)
114 Buffers: shared hit=87
115 -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=2.457..2.457 rows=16000.00 loops=1)
116 Buckets: 16384 Batches: 1 Memory Usage: 753kB
117 Buffers: shared hit=154
118 -> 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)
119 Buffers: shared hit=154
120 -> Hash (cost=185.24..185.24 rows=4000 width=20) (actual time=3.117..3.118 rows=4000.00 loops=1)
121 Buckets: 4096 Batches: 1 Memory Usage: 236kB
122 Buffers: shared hit=19
123 -> Subquery Scan on doc (cost=105.24..185.24 rows=4000 width=20) (actual time=1.968..2.671 rows=4000.00 loops=1)
124 Buffers: shared hit=19
125 -> HashAggregate (cost=105.24..145.24 rows=4000 width=20) (actual time=1.967..2.401 rows=4000.00 loops=1)
126 Group Key: ud.user_id
127 Batches: 1 Memory Usage: 409kB
128 Buffers: shared hit=19
129 -> Hash Join (cost=1.18..75.24 rows=4000 width=12) (actual time=0.033..1.051 rows=4000.00 loops=1)
130 Hash Cond: (ud.document_id = d.id)
131 Buffers: shared hit=19
132 -> 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)
133 Buffers: shared hit=18
134 -> Hash (cost=1.08..1.08 rows=8 width=8) (actual time=0.012..0.012 rows=8.00 loops=1)
135 Buckets: 1024 Batches: 1 Memory Usage: 9kB
136 Buffers: shared hit=1
137 -> 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)
138 Buffers: shared hit=1
139Planning:
140 Buffers: shared hit=453
141Planning Time: 16.655 ms
142Execution Time: 188.547 ms
143-- OK explain_rep_student_dossier.sql
Note: See TracBrowser for help on using the repository browser.