Reading and Interpreting Query Execution Plans
What you'll learn: How to decode what your database is actually doing when it runs a query—so you can spot slow operations and optimize them.
What Is a Query Execution Plan?
When you run a query, the database doesn't just blindly execute it. It creates a plan—a step-by-step blueprint showing which indexes to use, what order to join tables, and how much work each operation will cost. Think of it like a recipe that breaks down "make dinner" into precise steps with estimated cooking times.
Using EXPLAIN and EXPLAIN ANALYZE
Most databases let you peek at this plan with EXPLAIN:
EXPLAIN
SELECT customer_name, COUNT(*)
FROM orders
WHERE order_date > '2024-01-01'
GROUP BY customer_name;
This shows the estimated plan without running the query. For actual performance data, use EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT ...
This executes the query and reports real timing and row counts.
What to Look For
- Seq Scan vs Index Scan: Sequential scans read entire tables (slow for large data). Index scans use your indexes (faster).
- Cost numbers: Estimates of work required. Higher = more expensive.
- Rows: Estimated vs actual row counts. Big mismatches mean stale statistics.
- Nested loops, Hash joins, Merge joins: Different join strategies with varying performance.
- Filter conditions: WHERE clauses applied at different stages.
Reading the Plan
Plans are often tree-structured, read from innermost operations outward. Look for operations consuming the most time or processing unexpected row volumes—those are your bottlenecks.
Key Takeaway: Query execution plans reveal how your database executes queries, letting you identify full table scans, confirm index usage, and measure actual costs—essential for performance tuning.