SQL Mastery
Advanced 16 min read SQL Mastery

EXPLAIN and Query Plans

Read what the planner will actually do

EXPLAIN reveals the physical execution plan — scans, joins, sorts, and their costs. Reading plans is how you find the real bottleneck in a slow query.

EXPLAINquery planSeq ScanIndex Scancostssort
Prerequisites: Indexes and Query Performance
Before we start

What you will learn

  • Read EXPLAIN ANALYZE output node by node
  • Spot the expensive patterns: scans, sorts, nested loop overheating, materialization
  • Tie plan shape back to missing indexes or bad joins
Introduction

What is this?

EXPLAIN prints the planner's chosen physical plan — a tree of nodes each with rows-estimates and costs (startup/total). EXPLAIN ANALYZE actually runs the query and shows real times and row counts, letting you compare estimates to reality. It is the definitive tool for a slow query.

Motivation

Why do you need it?

  • Guessing at SQL performance ('add an index?') wastes time; the plan names the cost center.
  • Estimate-vs-actual divergence reveals stale statistics or mis-modeled joins.
  • Reading plans is a core interview and on-call skill.
Think about it

Mental model

Imagine

A GPS route preview before driving

Think of

EXPLAIN

The planner thinks fastest path is via A, but heavy traffic (stale stats) at junction B is in the live view. EXPLAIN ANALYZE is the live-update: what the driver actually sees.

Mechanics

How it works

  • Nodes nest; costs accumulate bottom-up; each shows estimated rows and startup/total cost.
  • Seq Scan reads the whole table; Index Scan reads via an index; Index Only Scan avoids the table.
  • Hash Join builds a hash of one side; Nested Loop executes per-row lookups; Merge Join needs sorted inputs.
  • Sort nodes (ORDER BY / DISTINCT) spill to disk when the sort set doesn't fit work_mem.
  • EXPLAIN ANALYZE adds actual rows and ms, exposing where estimates were wrong.

Anatomy of a plan tree.

Top node
Join node
Index or Seq Scan
ANALYZE verdict
Reference

Key commands

EXPLAIN ANALYZE SELECT ...;

Run and measure the plan.

EXPLAIN (ANALYZE, BUFFERS) ...;

Include cache-hit buffers.

ANALYZE orders;

Refresh table statistics before re-planning.

Hands-on

Examples

Reading a plan verdict

Reading a plan verdict
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';
--  Seq Scan on orders (cost=0.00..4500.12 rows=100000..) (actual rows=87621 time=420ms)
--  Filter: (status = 'pending'::text)

A Seq Scan over the whole table with a high actual — the plan names the missing index.

Real world

In production

  • An ops runbook tells on-call 'run EXPLAIN ANALYZE, screenshot the top cost node, and open a ticket with it'.
  • A query reviewer blocks merges whose plan shows Seq Scan on a table > 1M rows.
  • After ANALYZE the planner switches from nested-loop to hash join, and latency drops.
Guidelines

Best practices

  • Run EXPLAIN (ANALYZE, BUFFERS) with a representative WHERE — it executes the query.
  • Compare estimated rows to actual rows; big divergence = stale stats or missing stats.
  • Look for the widest-cost node and ask what removes it: an index, a join shape, a sort.
  • Confirm planner changes are measured on production-like cardinality.
Watch out

Common mistakes

  • EXPLAIN without ANALYZE on a cold cache, then mistaking estimated for measured.
  • Tuning for the sample's tiny data instead of the real cardinality.
  • Reading plans row by row instead of spotting the cost spine.
  • Forgetting the plan you see is for 'this query, this stats state' — parameters can shift it.
Root cause analysis

Common issues

Error
Planner picks a bad join after a schema change
Root cause

Stale stats from the DDL

Fix

ANALYZE the affected tables and re-evaluate the plan.

Error
Same query fast in dev, slow in prod
Root cause

Cardinality and work_mem differ

Fix

Benchmark prod-shaped volumes; tune work_mem or the plan with pg settings.

When things break

Debugging tips

  • Anchor on the node with the highest total cost; fix that first.
  • A Sort node with enormous rows suggests pushing ORDER BY into an index or reducing row set.
  • Buffers: lots of shared_read vs heap hits will show index effectiveness visually.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

What does EXPLAIN ANALYZE add beyond EXPLAIN?

2

EXPLAIN output is identical for every run of the same query.

Go deeper

Official documentation

PostgreSQL EXPLAIN docs
docs
SQLite query planner
docs