SQL-Engineering-Handbook

08 — EXPLAIN & Execution Plans

Introduction

Every prior file has referenced EXPLAIN output without fully unpacking it. This file is the complete reference: what every column means, how to read a plan tree, and how the same concepts map across MySQL, PostgreSQL, SQL Server, and Oracle.

Learning Objectives

Business Motivation

A query that ran fine in staging times out in production. The schema is identical; the row counts aren’t. EXPLAIN (and especially EXPLAIN ANALYZE) is how you find out why — without it, you’re debugging performance by guesswork.

Why This Exists

The optimizer’s chosen plan is invisible unless you ask for it. EXPLAIN surfaces the plan without running the query; EXPLAIN ANALYZE runs it and reports actual measured behavior against the plan’s estimates — the gap between the two is often the most valuable diagnostic signal available.

Production Use Cases

Architecture Discussion

EXPLAIN shows the estimated plan: what the optimizer intends to do, based on statistics, without executing anything. EXPLAIN ANALYZE executes the query and reports actual row counts, actual timing, and actual loop counts alongside the original estimates — letting you spot exactly where the optimizer’s assumptions diverged from reality.

Syntax

-- MySQL: plan only, no execution
EXPLAIN
SELECT * FROM orders WHERE customer_id = 88291;

-- MySQL 8.0.18+: adds actual execution stats
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 88291;

-- PostgreSQL: plan only
EXPLAIN
SELECT * FROM orders WHERE customer_id = 88291;

-- PostgreSQL: executes and reports actual timing/rows
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 88291;

Syntax Breakdown

Visual Explanation — MySQL EXPLAIN Columns

id  select_type  table   type  key                   rows   Extra
1   SIMPLE       orders  ref   idx_orders_customer_id 5      Using index

ASCII Diagram — Plan Tree (Join Example)

Reading an execution plan: estimated vs. actual rows

Mermaid version (renders inline on GitHub without loading the SVG) ```mermaid flowchart TD A["Nested Loop Join
est. rows: 40 · actual rows: 8,200 ⚠"] --> B["Index Scan: orders.status
est. rows: 40 · actual rows: 8,200 ⚠"] A --> C["PK Lookup: customers.id
est. rows: 1/loop · loops: 8,200"] ``` 
EXPLAIN
SELECT o.order_id, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';

Plan tree (conceptual):
        Nested Loop Join
        /              \
  Index scan          Index/PK lookup
  orders.status         customers.id
  (outer, driving)      (inner, probed per outer row)

Execution Flow — Reading a Plan Bottom-Up

  1. Innermost/rightmost operations execute first conceptually (though engines render this differently — PostgreSQL nests visually, MySQL’s tabular EXPLAIN requires reading id/nesting carefully).
  2. Each operator’s rows (estimated) vs. actual rows (with ANALYZE) tells you where the optimizer’s model diverged from reality.
  3. Large gaps between estimated and actual rows are the single strongest signal of a statistics problem (File 07) rather than an indexing problem.

Engineering Notes

Loops (visible in EXPLAIN ANALYZE / PostgreSQL’s EXPLAIN ANALYZE) tells you how many times an inner plan node executed — critical for nested loop joins, where a small per-loop cost multiplied by millions of outer rows becomes the dominant cost of the entire query, even though each individual loop looks cheap in isolation.

Performance Notes

Storage Considerations

Plans themselves aren’t stored persistently in MySQL/PostgreSQL by default (unlike SQL Server’s plan cache) — every EXPLAIN reflects current statistics at call time, which is exactly why stale statistics (File 07) matter operationally.

Optimizer Notes

cost (PostgreSQL’s EXPLAIN shows startup cost and total cost in arbitrary units, not milliseconds) is a relative number for comparing candidate plans — it is not directly a time prediction, which is why EXPLAIN ANALYZE’s actual time figures matter for real diagnosis.

ANSI SQL Notes

EXPLAIN syntax and output format are entirely vendor-specific; the ANSI standard has no EXPLAIN concept at all.

MySQL Notes

PostgreSQL Notes

SQL Server Notes

Oracle Notes

Edge Cases

Best Practices

Anti-patterns

Common Mistakes

Interview Questions

  1. What is the difference between EXPLAIN and EXPLAIN ANALYZE, and when would you avoid running the latter in production?
  2. In MySQL’s EXPLAIN output, what does Using filesort in the Extra column tell you, and why is it a signal to investigate?
  3. Compare Nested Loop, Hash, and Merge joins — under what conditions is each typically preferred by the optimizer?
  4. You see a large gap between estimated and actual row counts in an EXPLAIN ANALYZE plan. What does that suggest, and what’s your next step?

Summary

EXPLAIN reveals the optimizer’s intended plan without running the query; EXPLAIN ANALYZE runs it and reports actual measured behavior alongside the original estimates. Reading a plan means understanding access type (index vs. scan), join strategy (nested loop, hash, merge), and the gap between estimated and actual rows — that gap is usually the fastest path to diagnosing a real production performance problem.

Practice

See 12_PRACTICE_PROBLEMS.md, Production Debugging section, Problems 1–4.

Further Reading

See resources/documentation.md.

This site is open source. Improve this page.