source: sql/perf/before_gradebook.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: 3.1 KB
Line 
1######## gradebook WITHOUT index ########
2Sort (cost=3025.52..3027.18 rows=664 width=73) (actual time=16.122..16.143 rows=684.00 loops=1)
3 Sort Key: s.name, u.surname
4 Sort Method: quicksort Memory: 81kB
5 Buffers: shared hit=1965 read=152
6 -> Hash Join (cost=1699.04..2994.40 rows=664 width=73) (actual time=7.662..14.333 rows=684.00 loops=1)
7 Hash Cond: (ss.subjects_id = s.id)
8 Buffers: shared hit=1962 read=152
9 -> Hash Join (cost=1689.29..2979.56 rows=664 width=38) (actual time=7.580..13.939 rows=684.00 loops=1)
10 Hash Cond: (es.user_id = u.id)
11 Buffers: shared hit=1959 read=152
12 -> Nested Loop Left Join (cost=1518.59..2807.12 rows=664 width=13) (actual time=6.304..12.389 rows=684.00 loops=1)
13 Buffers: shared hit=1881 read=152
14 -> Hash Join (cost=1518.30..1978.94 rows=664 width=13) (actual time=6.270..9.008 rows=684.00 loops=1)
15 Hash Cond: (es.id = ss.enrolled_semesters_id)
16 Buffers: shared hit=664
17 -> 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)
18 Buffers: shared hit=154
19 -> Hash (cost=1510.00..1510.00 rows=664 width=13) (actual time=6.211..6.212 rows=684.00 loops=1)
20 Buckets: 1024 Batches: 1 Memory Usage: 41kB
21 Buffers: shared hit=510
22 -> 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)
23 Filter: (professor_id = 47)
24 Rows Removed by Filter: 79316
25 Buffers: shared hit=510
26 -> 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)
27 Index Cond: (enrolled_id = ss.id)
28 Heap Fetches: 0
29 Index Searches: 684
30 Buffers: shared hit=1217 read=152
31 -> Hash (cost=119.20..119.20 rows=4120 width=33) (actual time=1.225..1.225 rows=4120.00 loops=1)
32 Buckets: 8192 Batches: 1 Memory Usage: 336kB
33 Buffers: shared hit=78
34 -> 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)
35 Buffers: shared hit=78
36 -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.073..0.074 rows=300.00 loops=1)
37 Buckets: 1024 Batches: 1 Memory Usage: 25kB
38 Buffers: shared hit=3
39 -> 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)
40 Buffers: shared hit=3
41Planning:
42 Buffers: shared hit=361
43Planning Time: 10.951 ms
44Execution Time: 16.332 ms
45-- OK gradebook.sql
Note: See TracBrowser for help on using the repository browser.