source: sql/perf/after_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=93.283..93.287 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=81.507..82.943 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=15.695..62.979 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=15.614..51.151 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=3.462..24.820 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.011..4.913 rows=80000.00 loops=1)
23 Buffers: shared hit=510
24 -> Hash (cost=314.00..314.00 rows=16000 width=12) (actual time=3.390..3.391 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.018..1.826 rows=16000.00 loops=1)
28 Buffers: shared hit=154
29 -> Hash (cost=917.00..917.00 rows=56000 width=12) (actual time=12.055..12.055 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.011..5.684 rows=56000.00 loops=1)
33 Buffers: shared hit=357
34 -> Hash (cost=6.00..6.00 rows=300 width=8) (actual time=0.075..0.076 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.019..0.044 rows=300.00 loops=1)
38 Buffers: shared hit=3
39 -> Nested Loop (cost=131.42..261.59 rows=91 width=95) (actual time=86.807..93.255 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=86.753..93.190 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=81.558..82.640 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=81.512..81.779 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.037..0.038 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.033..0.033 rows=6.00 loops=1)
56 Buffers: shared hit=1
57 -> Hash (cost=80.00..80.00 rows=4000 width=48) (actual time=2.977..2.977 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.425 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.009..0.009 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=412
67Planning Time: 13.507 ms
68Execution Time: 93.983 ms
69-- OK explain_rep_top_student_per_major.sql
Note: See TracBrowser for help on using the repository browser.