SQL-Engineering-Handbook

EXPLAIN and Execution Plans

Module 16 · Query Optimization Home · Engineering Guide · Cheatsheet · Troubleshooting

Lesson 02 of 12 · ← Lifecycle · Next: SARGability → · SQL Lab

Introduction

EXPLAIN is how you ask the query optimizer to show its work. It is the single most important tool in this entire module — every other lesson assumes you can run EXPLAIN, read its output, and identify the expensive step.

Learning Objectives

Why This Exists

Without EXPLAIN, query tuning is guesswork — changing things and hoping they helped. With it, tuning becomes a measurement discipline: form a hypothesis about what’s slow, confirm it in the plan, make one change, re-measure.

Business Motivation

A retail analytics team runs this nightly to feed a dashboard:

SELECT d.dept_name, l.city, COUNT(*) AS staff_count
FROM employes e
JOIN departments d ON e.dept_id = d.dept_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.country = 'India'
GROUP BY d.dept_name, l.city;

When this report’s runtime jumps from 4 seconds to 90 seconds after a data migration, EXPLAIN ANALYZE — not intuition — is how you find out whether the cause is a missing index, stale statistics, or a changed join strategy.

Estimated vs. Actual: EXPLAIN vs. EXPLAIN ANALYZE

EXPLAIN
SELECT * FROM employes WHERE dept_id = 4;

EXPLAIN ANALYZE
SELECT * FROM employes WHERE dept_id = 4;

The gap between estimated and actual rows is one of the most useful signals in query tuning: a large gap means the optimizer’s statistics are stale or its cardinality estimate for a filter/join is wrong — and it may be picking a plan that made sense for the estimated data, not the actual data.

Reading a Plan Tree

Execution Plan Tree

Diagram note: the linked diagram uses Seq Scan/Index Scan labels, which are PostgreSQL’s EXPLAIN terminology. The text below reads the same plan shape using MySQL’s EXPLAIN output first, since MySQL is this module’s primary baseline, with the PostgreSQL equivalent labeled alongside it.

Plans are trees, read bottom-up, inside-out: the innermost/lowest nodes run first and feed rows upward into their parent node.

MySQL: Classic Tabular EXPLAIN

MySQL’s default EXPLAIN output is a table, one row per operation, not a nested tree of cost numbers:

+----+-------------+-------+--------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+--------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | e     | ALL    | idx_dept_id   | NULL | NULL    | NULL | 500000  | Using temp  |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4    | e.dept_id | 1   | Using where |
+----+-------------+-------+--------+---------------+------+---------+------+---------+-------------+

The type column is the access method, ordered roughly worst-to-best: ALL (full table scan) is the one to hunt for on a large table — here, employes (aliased e) shows type: ALL with possible_keys: idx_dept_id but key: NULL, meaning the index exists but wasn’t chosen. That row is your starting hypothesis: employes is doing a full scan instead of using its index for this query’s filter or join column.

MySQL 8.0.18+ also supports a tree-shaped view via EXPLAIN FORMAT=TREE (or EXPLAIN ANALYZE, which adds actual timing):

-> Sort  (cost=1400 rows=1000)
    -> Table scan on <temporary>  (cost=1200 rows=1000)
        -> Aggregate using temporary table
            -> Nested loop inner join  (cost=800 rows=500000)
                -> Table scan on e  (cost=500 rows=500000)   <- expensive
                -> Index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.25 rows=1)

Here, Table scan on e (the employes table) is the most expensive leaf — in MySQL’s classic tabular format this is the row where type: ALL.

PostgreSQL-specific terminology (cross-database comparison)

The same query, planned by PostgreSQL, reports the equivalent nodes with different names:

flowchart BT
    A[Sort — cost=1400] --> B[HashAggregate — cost=1200]
    B --> C[Hash Join — cost=800]
    C --> D["Seq Scan on employes — cost=500 (PostgreSQL)"]
    C --> E["Index Scan on departments — cost=20 (PostgreSQL)"]
    style D fill:#fee2e2,stroke:#b91c1c

Sort (cost=1400)                                    -- PostgreSQL EXPLAIN
  └── HashAggregate (cost=1200)
        └── Hash Join (cost=800)
              ├── Seq Scan on employes (cost=500)   <- expensive
              └── Hash
                    └── Index Scan on departments (cost=20)

Seq Scan on employes here is PostgreSQL’s name for the same “full table scan” MySQL reports as type: ALL — don’t expect MySQL’s EXPLAIN to literally print Seq Scan; it won’t.

Access Methods You’ll See

MySQL EXPLAIN’s type column, worst-to-best (the ones you’ll use most):

MySQL type Meaning
ALL Full table scan — reads every row. Expensive on large tables
index Scans the whole index (not the table) — better than ALL, still not selective
range Uses an index to scan a range of rows (e.g. BETWEEN, >, IN)
ref Uses a non-unique index to look up rows matching a value
eq_ref Uses a unique/primary-key index — at most one matching row per outer row
const At most one matching row, resolved during query planning
Extra: Using index Covering index — answers the query entirely from the index, no row lookup needed (see “covering index,” Lesson 03)

PostgreSQL-specific terminology (cross-database comparison)

PostgreSQL node Roughly equivalent MySQL type / Extra
Seq Scan type: ALL
Index Scan type: ref / range
Index Only Scan Extra: Using index
Bitmap Heap Scan No exact MySQL equivalent; closest is type: range with Extra: Using index condition

Do not expect MySQL’s EXPLAIN to output Seq Scan, Index Scan, Index Only Scan, or Bitmap Heap Scan literally — those strings are PostgreSQL’s plan-node names, shown here only for cross-database comparison.

Engineering Notes

MySQL / PostgreSQL / SQL Server Notes

Common Mistakes

Interview Questions

Edge Cases

Troubleshooting Guidance

Scalability Considerations

Running EXPLAIN ANALYZE on write queries or very expensive reads in production has real cost — it executes the query. At scale, prefer running it against a production-like replica, or during low-traffic windows, rather than directly against a live primary for anything beyond a quick, cheap SELECT.

Additional Dialect Notes

Summary

EXPLAIN and EXPLAIN ANALYZE turn “this query feels slow” into “this specific node in this specific plan is expensive” — every optimization technique in the rest of this module exists to fix a specific pattern you learn to recognize here.

Practice Challenges

  1. Run EXPLAIN ANALYZE on any JOIN query from Module 03 against a sufficiently large copy of the schema and identify the most expensive node.
  2. Find a query in your own work where estimated and actual row counts diverge by more than 10x, and explain a plausible cause.

Real Company Usage

Production Applications

These scenarios are illustrative case studies inspired by common production failure modes; specific figures are illustrative unless an external source is cited in PRODUCTION_INCIDENTS.md. See its disclaimer for the full explanation.

Performance Notes

Optimization Notes

Interview Insight

“Walk me through this EXPLAIN output” is a staple senior-level question. Interviewers want you to: (1) identify the most expensive node, (2) explain what access method it uses and why, (3) propose one targeted fix. Saying “a full table scan (type: ALL in MySQL, Seq Scan in PostgreSQL) on 8M rows with a filter that could use an index” beats vague answers like “it needs optimization.” Bonus points for noting estimated-vs-actual row mismatches and attributing them to stale statistics.

Further Experiments

  1. Run EXPLAIN ANALYZE on a three-table JOIN from Module 03 and annotate each node with its actual time — practice reading bottom-up.
  2. Deliberately write a non-SARGable predicate, confirm a full table scan (type: ALL in MySQL’s EXPLAIN, Seq Scan in PostgreSQL’s) in the plan, rewrite it, and re-run — compare the plan output side by side.
  3. Use EXPLAIN (BUFFERS, ANALYZE) on PostgreSQL to see whether slowness is I/O-bound (high shared read blocks) or CPU-bound (low I/O, high execution time).

Continue Learning

01_Query Execution Lifecycle · 03_SARGability · 07_Query Tuning Workflow

Further Reading

This site is open source. Improve this page.