QueryOptimization: testing.sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_student_current_semester
WHERE student_id = 12345; -- OK


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_student_upcoming_deadlines
WHERE student_id = 12345; -- OK


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_student_transcript
WHERE student_id = 12345; -- OK


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_course_announcements
WHERE edition_id = 12345; -- OK


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_exercise_grading_queue
WHERE exercise_id = 12345; -- OK


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_course_prerequisite_chain
WHERE course_code = 'F23L3S138'; -- OK




EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_student_registration_state
WHERE student_id = 12345;

CREATE INDEX CONCURRENTLY idx_pe_student_active ON Program_Enrollment(student_id)
    WHERE date_disenrollment IS NULL AND finished IS NOT TRUE;

CREATE INDEX CONCURRENTLY idx_se_pe_year_sem ON Semester_Enrollment(program_enrollment_id, academic_year, semester);

CREATE INDEX CONCURRENTLY idx_ce_semester ON Course_Enrollment(semester_enrollment_id);
CREATE INDEX CONCURRENTLY idx_ce_edition ON Course_Enrollment(course_edition_id);

CREATE INDEX CONCURRENTLY idx_sg_ce ON Student_Grade(course_enrollment_id);

CREATE INDEX CONCURRENTLY idx_course_equiv_course1 ON Course_Equivalence(course1_code);
CREATE INDEX CONCURRENTLY idx_course_equiv_course2 ON Course_Equivalence(course2_code);

-- INDICES STILL DON'T HELP --> USE FUNCTION fn_student_registration_state
SELECT * FROM fn_student_registration_state(12345);

--------------------------------------------------

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)
SELECT * FROM v_teaching_assistant_eligibility
WHERE edition_id = 13139;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ce_edition ON Course_Enrollment(course_edition_id);
CREATE INDEX CONCURRENTLY idx_teaches_edition ON Teaches(course_edition_id);
CREATE INDEX CONCURRENTLY idx_exercise_edition ON Exercise(course_edition_id);
CREATE INDEX CONCURRENTLY idx_exam_edition ON Exam(course_edition_id);
CREATE INDEX CONCURRENTLY idx_survey_edition ON Survey(course_edition_id);

CREATE INDEX CONCURRENTLY idx_se_year_sem ON Semester_Enrollment(academic_year, semester);

CREATE INDEX CONCURRENTLY idx_teaching_assistant_student ON Teaching_Assistant(student_id);
CREATE INDEX CONCURRENTLY idx_teaches_teacher_edition ON Teaches(teacher_id, course_edition_id);

-- INDICES STILL DON'T HELP --> USE FUNCTION fn_teaching_assistant_eligibility
SELECT DISTINCT id FROM Teacher WHERE id >= 10000 LIMIT 1;
SELECT DISTINCT id FROM Course_Edition ORDER BY id DESC;
SELECT * FROM fn_teaching_assistant_eligibility(13139);

-- DROP INDEX idx_teaches_edition;
-- DROP INDEX idx_exercise_edition;
-- DROP INDEX idx_exam_edition;
-- DROP INDEX idx_survey_edition;
--
-- DROP INDEX idx_se_year_sem;
--
-- DROP INDEX idx_teaching_assistant_student;
-- DROP INDEX idx_teaches_teacher_edition;