source: sql/perf/before_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=14550.91..14551.08 rows=67 width=156) (actual time=117.230..117.233 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=13136.41..14335.26 rows=20 width=40) (actual time=101.227..116.955 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=13136.41..13336.16 rows=79900 width=28) (actual time=100.465..106.806 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..4715.69 rows=79900 width=28) (actual time=13.233..57.501 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..2289.69 rows=79900 width=20) (actual time=3.240..33.159 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=79900 width=12) (actual time=3.221..21.944 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.009..4.461 rows=80000.00 loops=1)
24 Buffers: shared hit=510
25 -> Hash (cost=314.00..314.00 rows=16000 width=8) (actual time=3.159..3.159 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..1.817 rows=16000.00 loops=1)
29 Buffers: shared hit=154
30 -> Hash (cost=1.20..1.20 rows=20 width=12) (actual time=0.015..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.010..0.011 rows=20.00 loops=1)
34 Buffers: shared hit=1
35 -> Hash (cost=917.00..917.00 rows=56000 width=8) (actual time=9.895..9.895 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.009..4.658 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=117.030..117.221 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=101.230..101.231 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.039 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.079..0.079 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=245
57Planning Time: 7.402 ms
58Execution Time: 124.040 ms
59-- OK explain_rep_semester_growth.sql
Note: See TracBrowser for help on using the repository browser.