OtherTopics: queries.sql

-- Phase 9 - the queries measured for the index analysis.
--
-- Six of the seven come from Phase 6 (sql/reports.sql); the seventh is the
-- application query that the professor's gradebook page issues, kept as a
-- contrast between a report and an everyday selective query.
--
-- Run against the `perf` schema, which holds data of realistic size.
-- Each query was run with EXPLAIN (ANALYZE, BUFFERS) twice: once with no
-- extra indexes, then again after indexes.sql. The outputs are the
-- before_*.txt / after_*.txt files next to this script.
SET search_path TO perf;

-- 1. Phase 6, report 1 - pass rate per subject and semester.
--    before_rep_subject_pass_rate.txt / after_rep_subject_pass_rate.txt
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_subject_pass_rate();

-- 2. Phase 6, report 2 - dossier of every student (p_user_id IS NULL).
--    before_rep_student_dossier.txt / after_rep_student_dossier.txt
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_student_dossier();

-- 3. Phase 6, report 3 - best student of every study programme.
--    before_rep_top_student_per_major.txt / after_rep_top_student_per_major.txt
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_top_student_per_major();

-- 4. Phase 6, report 4 - busiest professor of every active semester.
--    before_rep_busiest_professor.txt / after_rep_busiest_professor.txt
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_busiest_professor();

-- 5. Phase 6, report 5 - change of activity between consecutive semesters.
--    before_rep_semester_growth.txt / after_rep_semester_growth.txt
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_semester_growth();

-- 6. Phase 6, report 2 again, but for ONE student. Same report, selective
--    argument - this is where an index can change the plan.
--    before_dossier_one.txt / after_dossier_one.txt
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_student_dossier(2500);

-- 7. Application query (not Phase 6): the professor's gradebook, i.e. the
--    students enrolled in the subjects one professor teaches.
--    before_gradebook.txt / after_gradebook.txt
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.surname, u.name, s.name AS subject, ss.signature, ps.grade
FROM semesters_subjects ss
JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
JOIN users u              ON u.id  = es.user_id
JOIN subjects s           ON s.id  = ss.subjects_id
LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
WHERE ss.professor_id = 47
ORDER BY s.name, u.surname;