source: sql/perf/after_rep_semester_growth.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: 4.5 KB
Line 
1######## rep_semester_growth ########
2Sort (cost=14562.60..14562.77 rows=67 width=156) (actual time=130.220..130.225 rows=20.00 loops=1)
3 Sort Key: cur.chrono
4 Sort Method: quicksort Memory: 26kB
5 Buffers: shared hit=1026, temp read=361 written=362
6 CTE semester_stats
7 -> GroupAggregate (cost=13146.60..14346.95 rows=20 width=40) (actual time=112.102..129.943 rows=20.00 loops=1)
8 Group Key: a.id, (((a.year * 10) + CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END))
9 Buffers: shared hit=1022, temp read=361 written=362
10 -> Sort (cost=13146.60..13346.60 rows=80000 width=28) (actual time=111.166..118.496 rows=80000.00 loops=1)
11 Sort Key: a.id, (((a.year * 10) + CASE a.type WHEN 'summer'::semester_type THEN 1 ELSE 2 END)), es.id
12 Sort Method: external merge Disk: 2888kB
13 Buffers: shared hit=1022, temp read=361 written=362
14 -> Hash Left Join (cost=2132.45..4717.02 rows=80000 width=28) (actual time=16.506..66.606 rows=80000.00 loops=1)
15 Hash Cond: (ss.id = ps.enrolled_id)
16 Buffers: shared hit=1022
17 -> Hash Right Join (cost=515.45..2290.01 rows=80000 width=20) (actual time=3.651..37.227 rows=80000.00 loops=1)
18 Hash Cond: (es.semester_id = a.id)
19 Buffers: shared hit=665
20 -> Hash Right Join (cost=514.00..2034.06 rows=80000 width=12) (actual time=3.628..24.767 rows=80000.00 loops=1)
21 Hash Cond: (ss.enrolled_semesters_id = es.id)
22 Buffers: shared hit=664
23 -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=8) (actual time=0.010..4.829 rows=80000.00 loops=1)
24 Buffers: shared hit=510
25 -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.567..3.567 rows=16000.00 loops=1)
26 Buckets: 16384 Batches: 1 Memory Usage: 753kB
27 Buffers: shared hit=154
28 -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=8) (actual time=0.010..2.060 rows=16000.00 loops=1)
29 Buffers: shared hit=154
30 -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.016..0.016 rows=20.00 loops=1)
31 Buckets: 1024 Batches: 1 Memory Usage: 9kB
32 Buffers: shared hit=1
33 -> Seq Scan on active_semesters a (cost=0.00..1.20 rows=20 width=12) (actual time=0.011..0.012 rows=20.00 loops=1)
34 Buffers: shared hit=1
35 -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=12.745..12.745 rows=56000.00 loops=1)
36 Buckets: 65536 Batches: 1 Memory Usage: 2700kB
37 Buffers: shared hit=357
38 -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=8) (actual time=0.010..5.858 rows=56000.00 loops=1)
39 Buffers: shared hit=357
40 -> Nested Loop Left Join (cost=0.00..213.62 rows=67 width=156) (actual time=130.017..130.212 rows=20.00 loops=1)
41 Join Filter: ((prev.chrono < cur.chrono) AND (NOT EXISTS(SubPlan 2)))
42 Rows Removed by Join Filter: 381
43 Buffers: shared hit=1026, temp read=361 written=362
44 -> CTE Scan on semester_stats cur (cost=0.00..0.40 rows=20 width=36) (actual time=112.105..112.106 rows=20.00 loops=1)
45 Storage: Memory Maximum Storage: 18kB
46 Buffers: shared hit=1022, temp read=336 written=362
47 -> CTE Scan on semester_stats prev (cost=0.00..0.40 rows=20 width=20) (actual time=0.000..0.045 rows=20.00 loops=20)
48 Storage: Memory Maximum Storage: 18kB
49 SubPlan 2
50 -> CTE Scan on semester_stats mid (cost=0.00..0.50 rows=1 width=0) (actual time=0.090..0.090 rows=0.90 loops=190)
51 Filter: ((chrono < cur.chrono) AND (chrono > prev.chrono))
52 Rows Removed by Filter: 8
53 Storage: Memory Maximum Storage: 18kB
54 Buffers: temp read=25
55Planning:
56 Buffers: shared hit=314
57Planning Time: 11.066 ms
58Execution Time: 138.077 ms
59-- OK explain_rep_semester_growth.sql
Note: See TracBrowser for help on using the repository browser.