Changes between Version 2 and Version 3 of QueryOptimization

Timestamp:

07/01/26 18:12:45 (4 days ago)

Author:

231082

Comment:

--

QueryOptimization

@@ -112 +112 @@
 ==== Before Optimization ====
 
-- '''Execution Time''': ~923 ms
-
-- '''Buffers''': shared hit=8369 read=40525, temp read=39620 written=39808
+* '''Execution Time''': ~923 ms
+* '''Buffers''': shared hit=8369 read=40525, temp read=39620 written=39808
 
 ==== Problem Analysis ====
@@ -128 +127 @@
 Replaced the view with a function that first identifies the course edition and then filters to only students who have passed that course.
 
-- '''After Optimization''': ~940 ms – a slight improvement due to reduced data volume.
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION fn_teaching_assistant_eligibility(p_edition_id INTEGER)
+    RETURNS TABLE (...) LANGUAGE plpgsql STABLE AS $$
+DECLARE
+    var_course_code TEXT;
+    var_academic_year SMALLINT;
+    var_semester SMALLINT;
+BEGIN
+    SELECT ed.course_code, ed.academic_year, ed.semester
+    INTO var_course_code, var_academic_year, var_semester
+    FROM Course_Edition ed
+    WHERE ed.id = p_edition_id;
+    
+    -- Only process students who passed this specific course
+    ...
+}}}
+* '''After Optimization''': ~383 ms – a '''significant improvement''' (approximately 2.4× faster than the view).
 
 == Optimization Summary ==
 
 || '''View''' || '''Initial Time''' || '''Final Time''' || '''Method''' ||
-|| v_student_current_semester || 23.459 ms || 23.459 ms || Existing indexes sufficient ||
-|| v_student_upcoming_deadlines || 35.945 ms || 35.945 ms || Existing indexes sufficient ||
-|| v_student_transcript || 0.426 ms || 0.426 ms || Existing indexes sufficient ||
-|| v_course_announcements || 8.085 ms || 8.085 ms || Existing indexes sufficient ||
-|| v_exercise_grading_queue || 122.986 ms || 122.986 ms || Acceptable performance ||
-|| v_course_prerequisite_chain || 0.563 ms || 0.563 ms || Existing indexes sufficient ||
-|| v_student_registration_state || 2268 ms || ~370 ms || Function replacement (6×) ||
-|| v_teaching_assistant_eligibility || 923 ms || ~940 ms || Function replacement ||
+|| `v_student_current_semester`        || 23.459 ms  || 23.459 ms  || Existing indexes sufficient ||
+|| `v_student_upcoming_deadlines`      || 35.945 ms  || 35.945 ms  || Existing indexes sufficient ||
+|| `v_student_transcript`              || 0.426 ms   || 0.426 ms   || Existing indexes sufficient ||
+|| `v_course_announcements`            || 8.085 ms   || 8.085 ms   || Existing indexes sufficient ||
+|| `v_exercise_grading_queue`          || 122.986 ms || 122.986 ms || Acceptable performance ||
+|| `v_course_prerequisite_chain`       || 0.563 ms   || 0.563 ms   || Existing indexes sufficient ||
+|| `v_student_registration_state`      || 2268 ms    || ~370 ms    || Function replacement (6×) ||
+|| `v_teaching_assistant_eligibility`  || 923 ms     || ~383 ms    || Function replacement (2.4×) ||
 
 == Lessons Learned ==