source: sql/perf/after_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.6 KB
RevLine 
[2853f2e]1######## gradebook WITH index ########
2Sort (cost=2048.55..2050.22 rows=666 width=73) (actual time=7.428..7.447 rows=684.00 loops=1)
3 Sort Key: s.name, u.surname
4 Sort Method: quicksort Memory: 81kB
5 Buffers: shared hit=2031 read=2
6 -> Nested Loop Left Join (cost=1042.40..2017.32 rows=666 width=73) (actual time=4.026..6.081 rows=684.00 loops=1)
7 Buffers: shared hit=2028 read=2
8 -> Hash Join (cost=1042.11..1185.19 rows=666 width=52) (actual time=4.009..4.875 rows=684.00 loops=1)
9 Hash Cond: (ss.subjects_id = s.id)
10 Buffers: shared hit=659 read=2
11 -> Hash Join (cost=1032.36..1173.67 rows=666 width=38) (actual time=3.929..4.688 rows=684.00 loops=1)
12 Hash Cond: (u.id = es.user_id)
13 Buffers: shared hit=656 read=2
14 -> 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)
15 Buffers: shared hit=78
16 -> Hash (cost=1024.04..1024.04 rows=666 width=13) (actual time=3.903..3.903 rows=684.00 loops=1)
17 Buckets: 1024 Batches: 1 Memory Usage: 41kB
18 Buffers: shared hit=578 read=2
19 -> Hash Join (cost=563.39..1024.04 rows=666 width=13) (actual time=1.575..3.817 rows=684.00 loops=1)
20 Hash Cond: (es.id = ss.enrolled_semesters_id)
21 Buffers: shared hit=578 read=2
22 -> 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)
23 Buffers: shared hit=154
24 -> Hash (cost=555.06..555.06 rows=666 width=13) (actual time=1.558..1.559 rows=684.00 loops=1)
25 Buckets: 1024 Batches: 1 Memory Usage: 41kB
26 Buffers: shared hit=424 read=2
27 -> 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)
28 Recheck Cond: (professor_id = 47)
29 Heap Blocks: exact=424
30 Buffers: shared hit=424 read=2
31 -> 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)
32 Index Cond: (professor_id = 47)
33 Index Searches: 1
34 Buffers: shared read=2
35 -> Hash (cost=6.00..6.00 rows=300 width=22) (actual time=0.074..0.074 rows=300.00 loops=1)
36 Buckets: 1024 Batches: 1 Memory Usage: 25kB
37 Buffers: shared hit=3
38 -> 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)
39 Buffers: shared hit=3
40 -> 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)
41 Index Cond: (enrolled_id = ss.id)
42 Heap Fetches: 0
43 Index Searches: 684
44 Buffers: shared hit=1369
45Planning:
46 Buffers: shared hit=373 read=1
47Planning Time: 11.015 ms
48Execution Time: 7.615 ms
49-- OK gradebook.sql
Note: See TracBrowser for help on using the repository browser.