Databases
Advanced 14 min read Databases

Query Planning and EXPLAIN

Reading the engine's mind, then arguing back

The optimizer reads your SQL, estimates row counts from statistics, and shapes a physical plan. EXPLAIN shows you the plan; EXPLAIN (ANALYZE, BUFFERS) shows what really ran and where the estimates lie.

optimizerEXPLAINcostcardinalitystatisticsjoin order
Prerequisites: Indexing Explain and Query Plans
Before we start

What you will learn

  • Read a cost/plan tree top to bottom
  • Spot the difference between estimated and actual row counts
  • Issue the most convincing EXPLAIN variants for a diagnosis
Introduction

What is this?

EXPLAIN reveals the physical plan the optimizer picked and the cost model that motivated it. EXPLAIN (ANALYZE, BUFFERS) actually runs the query and shows measured rows, time, and I/O; the gap between estimate and actual is literally where your tuning should start.

Motivation

Why do you need it?

  • A slow query is a symptom; the plan is the biopsy.
  • Estimates vs actuals tell you whether statistics, correlations, or join orders are the lie.
  • It is the universal language between you, the DBA at 3am, and the review ticket.
Think about it

Mental model

Imagine

A map app showing only the chosen route

Think of

EXPLAIN (no ANALYZE)

You see the road it picked and the 'estimated drive time' the model guessed. EXPLAIN ANALYZE is your dashcam replay: actual time, actual stops, and the mistake in the guess.

Mechanics

How it works

  • The planner reads ANALYZE-collected histograms, null fractions, and column correlations for cardinality estimates.
  • Costs are abstract units: startup + per-row I/O, CPU, and sort penalties, tuned by cost parameters.
  • Node order matters: a nested loop, hash join, filter, or sort sits at a plan level with its own rows/cycles estimates.
  • ANALYZE measures real execution time and row counts to highlight divergences like bad selectivity guesses.
  • The planner can be gently overridden (work_mem, enable_* flags, reading actual vs estimated) for one-off tuning.

Anatomy of a plan tree with cost flow.

Top node
Join node
Scan nodes
Buffers & actuals
Reference

Key commands

EXPLAIN SELECT ...;

Plan only, no execution; cheap for exploration.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Execute and measure time, rows, and buffer I/O.

ANALYZE orders;

Refresh statistics that drive the above estimates.

SET work_mem = '256MB';

Give sorts/hashes more RAM and watch the plan change.

Hands-on

Examples

The estimate-versus-actual tell

The estimate-versus-actual tell
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders o JOIN users u ON u.id = o.user_id
WHERE u.plan = 'pro';
--   Nested Loop  (cost=0.86..180.23 rows=89) (actual rows=8934 time=210ms)
--     ->  Seq Scan on users  (cost=0.00..40.2 rows=89) (actual rows=8934)
--     ->  Index Scan using users_pkey on users u  (cost=0.83..1.60 rows=1)
--   Buffers: shared hit=1872

rows=89 estimated vs 8934 actual is a 100x miss: the planner misjudged plan='pro' selectivity — stale stats or a correlation it did not know.

Real world

In production

  • A regression review diffs EXPLAIN before and after a schema migration to catch a plan flip.
  • Auto-tuners track the actual-vs-estimate gap as the trigger to refresh stats or add a statistic.
  • Cost-based hints get used by a small squad that needs an exact plan for one giant warehouse query.
Guidelines

Best practices

  • Always run (ANALYZE, BUFFERS) in a transaction-sized test; ANALYZE executes the query, so wrap it or use a replica.
  • Compare estimates vs actuals before touching indexes; the fixable lie is usually a stats issue.
  • Keep plans reproducible: note the isolation level, session settings, and ANALYZE state.
Watch out

Common mistakes

  • Running EXPLAIN without ANALYZE to 'be safe' and then diagnosing blindly with no real numbers.
  • Deriving that a seq scan is bad and an index is good from a plan alone; small tables are correctly seq-scanned.
  • Tuning a query while another session runs transactions that are the actual source of latency.
When things break

Debugging tips

  • Look for a huge rows estimate vs actual mismatch, then ANALYZE or reconsider the correlation.
  • When the planner picks a nested loop over a 100k-row side, test enable_hashjoin=off/on to see the other plan.
  • Inspect which node consumes the most time, not the one with the highest cost label — time is the ground truth.
Interviews

Interview checkpoint

Practice

Hands-on lab

Diagnose and Fix a Slow Join Cost
35 min · advanced
In progress
Objectives
  • Generate tables whose join the optimizer will mis-estimate
  • Reproduce a nested-loop blow-up and measure it with EXPLAIN (ANALYZE, BUFFERS)
  • Fix it with statistics or a cost tuning parameter and prove the new plan

Diagnose and Fix a Slow Join Cost

35 min · advanced

0/6 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which variant shows the planner's cost estimates AND real execution numbers?

2

A seq scan in a plan always means the index was not used.

Go deeper

Official documentation

PostgreSQL EXPLAIN
docs
PostgreSQL planner/optimizer
docs
SQLite query planner
docs