Lesson 28: Query Optimization & EXPLAIN

EXPLAIN shows the database's execution plan for a query — the starting point for understanding and fixing a slow query.

Real-time example

A dashboard query that takes 8 seconds gets diagnosed with EXPLAIN ANALYZE, revealing a missing index causing a full table scan.

EXPLAIN ANALYZE
SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-07-01'
GROUP BY c.name;
-- look for: Seq Scan on large tables, high 'cost' or 'actual time'

What's happening

EXPLAIN shows the planned steps and their estimated cost; EXPLAIN ANALYZE actually runs the query and shows real timing per step.

Optimize in this order: add the right index, rewrite the query, and only then consider denormalizing or caching.