source: sql/perf/after_rep_subject_pass_rate.txt

Last change on this file was 2853f2e, checked in by Stefan-Saveski <stefansaveski19@…>, 6 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: 8.0 KB
Line 
1######## rep_subject_pass_rate ########
2Sort (cost=12503.55..12505.05 rows=600 width=146) (actual time=154.481..154.698 rows=6000.00 loops=1)
3 Sort Key: a.year, a.type, (round(((100.0 * ((count(ps.id)))::numeric) / ((count(ss.id)))::numeric), 1)) DESC, s.code
4 Sort Method: quicksort Memory: 747kB
5 Buffers: shared hit=2577
6 -> Hash Left Join (cost=12288.43..12475.86 rows=600 width=146) (actual time=131.644..140.919 rows=6000.00 loops=1)
7 Hash Cond: (ss.subjects_id = sa.subject_id)
8 Buffers: shared hit=2566
9 -> Hash Left Join (cost=8300.17..8471.00 rows=600 width=86) (actual time=96.448..102.055 rows=6000.00 loops=1)
10 Hash Cond: ((ss.subjects_id = sps.subject_id) AND (es.semester_id = sps.semester_id))
11 Buffers: shared hit=1699
12 -> Hash Join (cost=5672.27..5839.95 rows=600 width=82) (actual time=69.495..73.840 rows=6000.00 loops=1)
13 Hash Cond: (ss.subjects_id = s.id)
14 Buffers: shared hit=1035
15 -> Hash Join (cost=5662.52..5828.60 rows=600 width=64) (actual time=69.356..72.693 rows=6000.00 loops=1)
16 Hash Cond: (es.semester_id = a.id)
17 Buffers: shared hit=1032
18 -> HashAggregate (cost=5661.07..5751.07 rows=6000 width=56) (actual time=69.338..71.731 rows=6000.00 loops=1)
19 Group Key: ss.subjects_id, es.semester_id
20 Batches: 1 Memory Usage: 1169kB
21 Buffers: shared hit=1031
22 -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=15.669..48.617 rows=80000.00 loops=1)
23 Hash Cond: (ss.id = ps.enrolled_id)
24 Buffers: shared hit=1021
25 -> Hash Join (cost=514.00..2034.06 rows=80000 width=12) (actual time=4.269..23.426 rows=80000.00 loops=1)
26 Hash Cond: (ss.enrolled_semesters_id = es.id)
27 Buffers: shared hit=664
28 -> 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)
29 Buffers: shared hit=510
30 -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=4.199..4.200 rows=16000.00 loops=1)
31 Buckets: 16384 Batches: 1 Memory Usage: 753kB
32 Buffers: shared hit=154
33 -> 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)
34 Buffers: shared hit=154
35 -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=11.266..11.266 rows=56000.00 loops=1)
36 Buckets: 65536 Batches: 1 Memory Usage: 2919kB
37 Buffers: shared hit=357
38 -> 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)
39 Buffers: shared hit=357
40 -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.014..0.015 rows=20.00 loops=1)
41 Buckets: 1024 Batches: 1 Memory Usage: 9kB
42 Buffers: shared hit=1
43 -> 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)
44 Buffers: shared hit=1
45 -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.080..0.080 rows=300.00 loops=1)
46 Buckets: 1024 Batches: 1 Memory Usage: 25kB
47 Buffers: shared hit=3
48 -> 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)
49 Buffers: shared hit=3
50 -> Hash (cost=2537.91..2537.91 rows=6000 width=16) (actual time=26.921..26.922 rows=6000.00 loops=1)
51 Buckets: 8192 Batches: 1 Memory Usage: 346kB
52 Buffers: shared hit=664
53 -> Subquery Scan on sps (cost=2417.91..2537.91 rows=6000 width=16) (actual time=25.227..26.223 rows=6000.00 loops=1)
54 Buffers: shared hit=664
55 -> HashAggregate (cost=2417.91..2477.91 rows=6000 width=16) (actual time=25.226..25.820 rows=6000.00 loops=1)
56 Group Key: ss_1.subjects_id, es_1.semester_id
57 Batches: 1 Memory Usage: 409kB
58 Buffers: shared hit=664
59 -> Hash Join (cost=514.00..1978.01 rows=58653 width=12) (actual time=2.774..16.440 rows=58801.00 loops=1)
60 Hash Cond: (ss_1.enrolled_semesters_id = es_1.id)
61 Buffers: shared hit=664
62 -> 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)
63 Filter: signature
64 Rows Removed by Filter: 21199
65 Buffers: shared hit=510
66 -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=2.702..2.703 rows=16000.00 loops=1)
67 Buckets: 16384 Batches: 1 Memory Usage: 753kB
68 Buffers: shared hit=154
69 -> 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)
70 Buffers: shared hit=154
71 -> Hash (cost=3984.51..3984.51 rows=300 width=36) (actual time=35.177..35.178 rows=300.00 loops=1)
72 Buckets: 1024 Batches: 1 Memory Usage: 21kB
73 Buffers: shared hit=867
74 -> Subquery Scan on sa (cost=3977.01..3984.51 rows=300 width=36) (actual time=34.994..35.139 rows=300.00 loops=1)
75 Buffers: shared hit=867
76 -> HashAggregate (cost=3977.01..3981.51 rows=300 width=36) (actual time=34.993..35.095 rows=300.00 loops=1)
77 Group Key: ss_2.subjects_id
78 Batches: 1 Memory Usage: 80kB
79 Buffers: shared hit=867
80 -> Hash Join (cost=1617.00..3137.01 rows=56000 width=8) (actual time=9.870..24.436 rows=56000.00 loops=1)
81 Hash Cond: (ss_2.id = ps_1.enrolled_id)
82 Buffers: shared hit=867
83 -> 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)
84 Buffers: shared hit=510
85 -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.613..9.613 rows=56000.00 loops=1)
86 Buckets: 65536 Batches: 1 Memory Usage: 2700kB
87 Buffers: shared hit=357
88 -> 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)
89 Buffers: shared hit=357
90Planning:
91 Buffers: shared hit=416 read=8
92Planning Time: 11.797 ms
93Execution Time: 156.015 ms
94-- OK explain_rep_subject_pass_rate.sql
Note: See TracBrowser for help on using the repository browser.