source: sql/perf/before_rep_top_student_per_major.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: 5.0 KB
Line 
1######## rep_top_student_per_major ########
2Sort (cost=6208.33..6208.56 rows=91 width=95) (actual time=106.513..106.518 rows=6.00 loops=1)
3 Sort Key: m.name
4 Sort Method: quicksort Memory: 25kB
5 Buffers: shared hit=1056
6 CTE standing
7 -> HashAggregate (cost=5883.79..5943.79 rows=4000 width=56) (actual time=92.287..93.895 rows=4000.00 loops=1)
8 Group Key: es.user_id, es.major_id
9 Batches: 1 Memory Usage: 1169kB
10 Buffers: shared hit=1034
11 -> Hash Left Join (cost=2140.75..4083.79 rows=80000 width=20) (actual time=16.730..71.128 rows=80000.00 loops=1)
12 Hash Cond: (ss.subjects_id = s.id)
13 Join Filter: (ps.id IS NOT NULL)
14 Rows Removed by Join Filter: 24000
15 Buffers: shared hit=1024
16 -> Hash Left Join (cost=2131.00..3861.07 rows=80000 width=20) (actual time=16.601..57.953 rows=80000.00 loops=1)
17 Hash Cond: (ss.id = ps.enrolled_id)
18 Buffers: shared hit=1021
19 -> Hash Right Join (cost=514.00..2034.06 rows=80000 width=16) (actual time=4.275..27.868 rows=80000.00 loops=1)
20 Hash Cond: (ss.enrolled_semesters_id = es.id)
21 Buffers: shared hit=664
22 -> Seq Scan on semesters_subjects ss (cost=0.00..1310.00 rows=80000 width=12) (actual time=0.013..5.272 rows=80000.00 loops=1)
23 Buffers: shared hit=510
24 -> Hash (cost=314.00..314.00 rows=16000 width=12) (actual time=4.208..4.209 rows=16000.00 loops=1)
25 Buckets: 16384 Batches: 1 Memory Usage: 816kB
26 Buffers: shared hit=154
27 -> Seq Scan on enrolled_semesters es (cost=0.00..314.00 rows=16000 width=12) (actual time=0.020..2.209 rows=16000.00 loops=1)
28 Buffers: shared hit=154
29 -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=12.206..12.207 rows=56000.00 loops=1)
30 Buckets: 65536 Batches: 1 Memory Usage: 2919kB
31 Buffers: shared hit=357
32 -> Seq Scan on passed_subjects ps (cost=0.00..917.00 rows=56000 width=12) (actual time=0.012..5.719 rows=56000.00 loops=1)
33 Buffers: shared hit=357
34 -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.126..0.126 rows=300.00 loops=1)
35 Buckets: 1024 Batches: 1 Memory Usage: 20kB
36 Buffers: shared hit=3
37 -> Seq Scan on subjects s (cost=0.00..6.00 rows=300 width=8) (actual time=0.037..0.065 rows=300.00 loops=1)
38 Buffers: shared hit=3
39 -> Nested Loop (cost=131.42..261.59 rows=91 width=95) (actual time=98.002..106.481 rows=6.00 loops=1)
40 Buffers: shared hit=1053
41 -> Hash Anti Join (cost=131.13..225.46 rows=91 width=60) (actual time=97.975..106.434 rows=6.00 loops=1)
42 Hash Cond: (st.major_id = st1.major_id)
43 Join Filter: ((st.credits < st1.credits) OR ((st.credits = st1.credits) AND (st.average_grade < st1.average_grade)) OR ((st.credits = st1.credits) AND (st.average_grade = st1.average_grade) AND (st.user_id > st1.user_id)))
44 Rows Removed by Join Filter: 28423
45 Buffers: shared hit=1035
46 -> Hash Join (cost=1.14..91.86 rows=120 width=64) (actual time=92.353..93.570 rows=4000.00 loops=1)
47 Hash Cond: (st.major_id = m.id)
48 Buffers: shared hit=1035
49 -> CTE Scan on standing st (cost=0.00..80.00 rows=4000 width=56) (actual time=92.293..92.600 rows=4000.00 loops=1)
50 Storage: Memory Maximum Storage: 251kB
51 Buffers: shared hit=1034
52 -> Hash (cost=1.06..1.06 rows=6 width=12) (actual time=0.051..0.051 rows=6.00 loops=1)
53 Buckets: 1024 Batches: 1 Memory Usage: 9kB
54 Buffers: shared hit=1
55 -> Seq Scan on major m (cost=0.00..1.06 rows=6 width=12) (actual time=0.045..0.046 rows=6.00 loops=1)
56 Buffers: shared hit=1
57 -> Hash (cost=80.00..80.00 rows=4000 width=48) (actual time=3.222..3.222 rows=4000.00 loops=1)
58 Buckets: 4096 Batches: 1 Memory Usage: 267kB
59 -> CTE Scan on standing st1 (cost=0.00..80.00 rows=4000 width=48) (actual time=0.001..2.644 rows=4000.00 loops=1)
60 Storage: Memory Maximum Storage: 251kB
61 -> Index Scan using users_pkey on users u (cost=0.28..0.39 rows=1 width=33) (actual time=0.006..0.006 rows=1.00 loops=6)
62 Index Cond: (id = st.user_id)
63 Index Searches: 6
64 Buffers: shared hit=18
65Planning:
66 Buffers: shared hit=335
67Planning Time: 10.504 ms
68Execution Time: 107.303 ms
69-- OK explain_rep_top_student_per_major.sql
Note: See TracBrowser for help on using the repository browser.