Query Optimization and Execution Plans

Query optimizer role

The optimizer decides join order, access path (index vs scan), and algorithm (hash join, merge join, nested loop). Understanding plan output is key to fixing slow queries.

EXPLAIN ANALYZE
SELECT c.title, COUNT(e.enrollment_id) AS total_enrollments
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.title
ORDER BY total_enrollments DESC;

Optimization playbook

• Filter early to reduce join input size.
• Avoid SELECT * in analytical and API queries.
• Use covering indexes for frequent access paths.
• Rewrite correlated subqueries when planner struggles.
• Track slow-query logs continuously in production.