SQL Performance and Index Optimization

The goal of this phase is to analyze and improve the performance of the most important database operations in the Online Learning Platform Management System.

The application contains several frequent operations:

browsing and filtering courses checking existing enrollments validating active user subscriptions loading course modules and lessons managing support tickets generating reports for enrollments, certificates and quiz attempts

The following indexes are not automatically created by primary key constraints, but they can improve the performance of WHERE filters, JOIN operations and report queries.

Testing Methodology

The performance analysis is done using PostgreSQL EXPLAIN ANALYZE.

The testing process is:

first, the query is executed without additional indexes then, the required index is created the same query is executed again the execution plan and execution time are compared

This allows us to check whether PostgreSQL uses Seq Scan, Index Scan or another optimized query plan.

Scenario 1: Course Browsing and Filtering

Users frequently browse available courses and filter them by course status, name or price.

SQL query:

EXPLAIN ANALYZE
SELECT c.course_id, c.name, c.price, c.status
FROM course c
WHERE c.status = 'ACTIVE'
ORDER BY c.price;

Indexes:

CREATE INDEX IF NOT EXISTS idx_course_status_price
ON course(status, price);

CREATE INDEX IF NOT EXISTS idx_course_name
ON course(name);

Explanation:

The composite index on (status, price) is useful because the query filters by status and sorts by price.

The index on name improves course searching by course name.

Scenario 2: Enrollment Checking

Before a user enrolls in a course, the system checks whether the user is already enrolled in that course.

SQL query:

EXPLAIN ANALYZE
SELECT e.enrollment_id, e.user_id, e.course_id, e.completion_status
FROM enrollment e
WHERE e.user_id = 1
AND e.course_id = 3;

Index:

CREATE INDEX IF NOT EXISTS idx_enrollment_user_course
ON enrollment(user_id, course_id);

Explanation:

This index is useful because the application frequently searches enrollments by both user_id and course_id.

Without this index, PostgreSQL may scan the whole enrollment table. With the index, the lookup is faster and more direct.

Scenario 3: Active Subscription Validation

When a user wants to access course content, the system checks whether the user has an active subscription.

SQL query:

EXPLAIN ANALYZE
SELECT us.subscription_id, us.user_id, us.plan_id, us.status, us.end_date
FROM user_subscription us
WHERE us.user_id = 1
AND us.status = 'ACTIVE'
AND us.end_date >= CURRENT_DATE;

Index:

CREATE INDEX IF NOT EXISTS idx_user_subscription_active
ON user_subscription(user_id, status, end_date);

Explanation:

The index improves subscription validation because the query filters by user_id, status and end_date.

This is important because subscription validation is performed before allowing access to paid course content.

Scenario 4: Instructor Course Management

Instructors frequently manage their own courses. The system displays all courses created by a selected instructor.

SQL query:

EXPLAIN ANALYZE
SELECT c.course_id, c.name, c.price, c.status
FROM course c
WHERE c.instructor_id = 5;

Index:

CREATE INDEX IF NOT EXISTS idx_course_instructor_id
ON course(instructor_id);

Explanation:

The instructor_id column is used for filtering courses by instructor. This index improves the performance of the instructor dashboard.

Scenario 5: Course Content Loading

When a user opens a course, the application loads all modules and lessons for that course.

SQL query:

EXPLAIN ANALYZE
SELECT
m.module_id,
m.title AS module_title,
l.lesson_id,
l.title AS lesson_title
FROM module m
JOIN lesson l ON l.module_id = m.module_id
WHERE m.course_id = 3
ORDER BY m.module_id, l.lesson_id;

Indexes:

CREATE INDEX IF NOT EXISTS idx_module_course_id
ON module(course_id);

CREATE INDEX IF NOT EXISTS idx_lesson_module_id
ON lesson(module_id);

Explanation:

The index on module(course_id) improves loading modules for a selected course.

The index on lesson(module_id) improves loading lessons for each module.

Scenario 6: Admin Support Ticket Filtering

Administrators frequently filter support tickets by status and check the newest tickets first.

SQL query:

EXPLAIN ANALYZE
SELECT st.ticket_id, st.subject, st.status, st.created_at
FROM support_ticket st
WHERE st.status = 'OPEN'
ORDER BY st.created_at DESC;

Index:

CREATE INDEX IF NOT EXISTS idx_support_ticket_status_created
ON support_ticket(status, created_at DESC);

Explanation:

This index is useful because the admin panel filters tickets by status and sorts them by created_at.

Scenario 7: Annual Enrollment and Certificate Report

This report analyzes enrollments, completed courses and issued certificates per year.

SQL query:

EXPLAIN ANALYZE
SELECT
EXTRACT(YEAR FROM e.enroll_date) AS year,
COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
COUNT(DISTINCT CASE
WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
END) AS completed_enrollments,
COUNT(DISTINCT c.certificate_id) AS issued_certificates,
AVG(e.progress_percentage) AS average_progress
FROM enrollment e
LEFT JOIN certificate c ON c.enrollment_id = e.enrollment_id
GROUP BY EXTRACT(YEAR FROM e.enroll_date)
ORDER BY year;

Indexes:

CREATE INDEX IF NOT EXISTS idx_enrollment_enroll_date
ON enrollment(enroll_date);

CREATE INDEX IF NOT EXISTS idx_enrollment_completion_status
ON enrollment(completion_status);

CREATE INDEX IF NOT EXISTS idx_certificate_enrollment_id
ON certificate(enrollment_id);

Explanation:

The index on enroll_date improves time-based reports.

The index on completion_status helps when filtering completed enrollments.

The index on certificate(enrollment_id) improves the join between certificate and enrollment.

Scenario 8: Quiz Attempts Report

The system can analyze quiz attempts by user, quiz and attempt date.

SQL query:

EXPLAIN ANALYZE
SELECT
qa.user_id,
qa.quiz_id,
COUNT(*) AS total_attempts,
AVG(qa.score) AS average_score
FROM quiz_attempt qa
GROUP BY qa.user_id, qa.quiz_id
ORDER BY qa.user_id;

Indexes:

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_user_id
ON quiz_attempt(user_id);

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_quiz_id
ON quiz_attempt(quiz_id);

CREATE INDEX IF NOT EXISTS idx_quiz_attempt_date
ON quiz_attempt(attempt_date);

Explanation:

These indexes improve reports that analyze quiz attempts by user, quiz or date.