= Query Optimization =

== Overview ==

This phase focuses on the analysis and optimization of database queries for the Courses Management System (CMS). The goal is to ensure that critical reporting and administrative tasks execute efficiently, even under high load. We distinguish between **Analytical Views** (using aggregate functions) and **Non-Analytical Views** (no aggregate functions allowed per course requirements).

The optimization process followed a structured approach:
1.  **Analysis**: Identified the primary filters and usage scenarios for each view.
2.  **Performance Testing**: Measured initial execution time using `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)`.
3.  **Optimization**: Added necessary indexes or restructured queries.
4.  **Re-testing**: Measured final execution time.
5.  **Decision**: Determined if optimization was necessary based on whether the time was acceptable (< 3s for non-analytical, < 3s for analytical).

== View 1: v_student_current_semester ==

1.  **Primary Filter**: The view is filtered by `student_id` (e.g., `WHERE student_id = 12345`).
2.  **Usage Scenario**: Used to display current semester courses for a specific student. This is critical for the application as it's used for course selection and grade viewing.
3.  **Initial Execution Time**: 0.94 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 2: v_student_upcoming_deadlines ==

1.  **Primary Filter**: The view is filtered by `student_id` (e.g., `WHERE student_id = 12345`).
2.  **Usage Scenario**: Used to display upcoming exercise and exam deadlines for a specific student. Critical for student dashboard.
3.  **Initial Execution Time**: 8.87 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 3: v_student_transcript ==

1.  **Primary Filter**: The view is filtered by `student_id` (e.g., `WHERE student_id = 12345`).
2.  **Usage Scenario**: Used to display the complete academic transcript for a student. Critical for student and teacher reporting.
3.  **Initial Execution Time**: 0.09 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 4: v_course_announcements ==

1.  **Primary Filter**: The view is filtered by `edition_id` (e.g., `WHERE edition_id = 12345`).
2.  **Usage Scenario**: Used to display course announcements. Critical for teacher and student communication.
3.  **Initial Execution Time**: 1.07 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 5: v_exercise_grading_queue ==

1.  **Primary Filter**: The view is filtered by `exercise_id` (e.g., `WHERE exercise_id = 12345`).
2.  **Usage Scenario**: Used to display the grading queue for a specific exercise. Critical for teacher grading workflow.
3.  **Initial Execution Time**: 92.67 ms.
4.  **Slow Operations**: Full scan on `exercise_submission` (2.4M rows) with parallel execution workers.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 6: v_course_prerequisite_chain ==

1.  **Primary Filter**: The view is filtered by `course_code` (e.g., `WHERE course_code = 'F23L3S138'`).
2.  **Usage Scenario**: Used to display the prerequisite chain for a course. Critical for student enrollment eligibility.
3.  **Initial Execution Time**: 0.94 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 7: v_student_registration_state ==

1.  **Primary Filter**: The view is filtered by `student_id` (e.g., `WHERE student_id = 12345`).
2.  **Usage Scenario**: Used to check if a student can enroll in a course. Critical for enrollment system.
3.  **Initial Execution Time**: 2.18 s.
4.  **Slow Operations**: Full scan on `Program_Enrollment`, `Course_Enrollment`, `Student_Grade`, and `Course_Equivalence`. The CTEs inside the View materialized all data before applying the WHERE filter, wasting I/O and CPU.
5.  **Time with Indexes**: 2.20 s (Indexes did not help due to CTE materialization issue).
6.  **Optimization Decision**: The View was replaced by `fn_student_registration_state` function. The function pushes the filter down immediately, avoiding full table scans. Execution time reduced to ~400ms with proper filtering.

== View 8: v_teaching_assistant_eligibility ==

1.  **Primary Filter**: The view is filtered by `edition_id` (e.g., `WHERE edition_id = 13139`).
2.  **Usage Scenario**: Used to check if a student is eligible to be a Teaching Assistant. Critical for TA assignment process.
3.  **Initial Execution Time**: 848 ms.
4.  **Slow Operations**: Full scan on `Course_Enrollment`, `Teaches`, `Student_Grade`, and `Teaching_Assistant`. The CTEs inside the View materialized all data before applying the WHERE filter.
5.  **Time with Indexes**: 841 ms (Indexes did not help due to CTE materialization issue).
6.  **Optimization Decision**: The View was replaced by `fn_teaching_assistant_eligibility` function. The function pushes the filter down immediately, avoiding full table scans. Execution time reduced to ~470ms with proper filtering.

== Analytical Views (Extras) ==

The following views use aggregate functions and are included as extras. They execute in reasonable time (under 3s).

== View 9: v_exam_results_summary ==

1.  **Primary Filter**: The view is filtered by `exam_id` (e.g., `WHERE exam_id = 12345`).
2.  **Usage Scenario**: Used to display exam results summary. Critical for teacher reporting.
3.  **Initial Execution Time**: 15 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== View 10: v_teacher_dashboard ==

1.  **Primary Filter**: The view is filtered by `teacher_id` (e.g., `WHERE teacher_id = 12345`).
2.  **Usage Scenario**: Used to display teacher dashboard. Critical for teacher workflow.
3.  **Initial Execution Time**: 8 ms.
4.  **Slow Operations**: None identified.
5.  **Time with Indexes**: N/A (No slow operations).
6.  **Optimization Decision**: No optimization needed. Execution time is acceptable.

== Indexes Created During Optimization ==

||= Index Name =||= Table =||= Columns =||= Purpose =||
||= `idx_pe_student_active` =|| `Program_Enrollment` || `student_id` || Filter for active enrollments ||
||= `idx_se_pe_year_sem` =|| `Semester_Enrollment` || `program_enrollment_id, academic_year, semester` || Filter for semester enrollments ||
||= `idx_course_equiv_course1` =|| `Course_Equivalence` || `course1_code` || Filter for course equivalences ||
||= `idx_sg_ce` =|| `Student_Grade` =|| `course_enrollment_id` || Filter for grades ||
||= `idx_teaches_teacher_edition` =|| `Teaches` || `teacher_id, course_edition_id` || Filter for teaching assignments ||
||= `idx_exercise_edition` =|| `Exercise` || `course_edition_id` || Filter for exercises ||
||= `idx_exam_edition` =|| `Exam` || `course_edition_id` || Filter for exams ||

== Index Limitations and Function Replacement ==

Despite creating indexes, the `v_student_registration_state` and `v_teaching_assistant_eligibility` views still did not reduce query execution time significantly because:
1.  The CTEs inside the views did not have direct conditions on the indexed columns.
2.  PostgreSQL materialized every CTE completely before executing the outer WHERE filter.

**Solution**: Replaced the views with functions that push the filters down immediately.

== Execution Plans Analysis ==

Execution plans were acquired using `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING, FORMAT TEXT)`.

**Before Optimization (Views):**
*   **Seq Scan** on `Program_Enrollment` followed by full join operations.
*   **Full Table Scan** on `Student_Grade` and `Course_Enrollment`.
*   High buffer usage due to materialized CTEs.

**After Optimization (Functions):**
*   **Index Scan** on `Program_Enrollment` immediately filtered by `student_id`.
*   Reduced buffer usage and faster I/O.
*   Lower CPU usage due to fewer unnecessary joins.

== Database Size Impact ==

Optimization efforts focus on the 13GB total database size. By reducing unnecessary data movement (via function filtering) and utilizing specific indexes, we ensure that critical queries like `v_student_current_semester` and `v_upcoming_deadlines` run efficiently without triggering full table scans.

== Summary ==

||= View =||= Initial Time =||= Optimized Time =||= Optimization Needed =||
||= v_student_current_semester =|| 0.94 ms || 0.94 ms || No ||
||= v_student_upcoming_deadlines =|| 8.87 ms || 8.87 ms || No ||
||= v_student_transcript =|| 0.09 ms || 0.09 ms || No ||
||= v_course_announcements =|| 1.07 ms || 1.07 ms || No ||
||= v_exercise_grading_queue =|| 92.67 ms || 92.67 ms || No ||
||= v_course_prerequisite_chain =|| 0.94 ms || 0.94 ms || No ||
||= v_student_registration_state =|| 2.18 s || 0.40 s || Yes (Function) ||
||= v_teaching_assistant_eligibility =|| 848 ms || 470 ms || Yes (Function) ||
||= v_exam_results_summary =|| 15 ms || 15 ms || No ||
||= v_teacher_dashboard =|| 8 ms || 8 ms || No ||

All critical views now execute within acceptable time limits, ensuring the application functions smoothly.

== Function Performance ==

The following functions were created to optimize critical queries:

||= Function =||= Primary Filter =||= Usage Scenario =||= Execution Time =||
||= `fn_student_registration_state` =||`student_id` || Check if a student can enroll in a course || ~400ms ||
||= `fn_teaching_assistant_eligibility` =|| `edition_id` || Check if a student is eligible to be a TA || ~470ms ||

== No Additional Indices Retained ==

No additional indices were retained because they did not serve any purpose in the optimization process. The function-based approach proved more effective for the specific CTE materialization issues encountered.