EXPLAIN and execution plans: what the planner decides and why

A dashboard query takes 6 seconds in production. A colleague asks: “did you EXPLAIN it?” You run EXPLAIN ANALYZE and in two seconds you see a Seq Scan on orders ... actual rows=80000000. One index. 200ms. That is what EXPLAIN is for.

What an execution plan is

SQL is declarative — you write which rows you want, not how to get them. The Postgres planner bridges that gap. It reads your SQL, consults pg_statistic to estimate how many rows each condition will produce, enumerates possible combinations of scan types and join algorithms, costs each option, and picks the cheapest one. The result is a plan — a tree of operators with row estimates attached.

A query goes through four stages before any row is returned:

1. Parse — text is converted to a parse tree
2. Rewrite — views and rules are applied
3. Plan — the planner builds the cheapest execution tree
4. Execute — the engine runs the tree

The planner is the only stage you tune. Everything else is automatic.

Reading the output

A simple plan looks like this:

Index Scan using idx_orders_workspace on orders
  (cost=0.43..14.2 rows=42 width=120)
  (actual time=0.08..1.1 rows=42 loops=1)
  Index Cond: (workspace_id = 42)

• cost=0.43..14.2 — startup cost (first row) and total cost in arbitrary units. Not milliseconds. Ratios matter; absolute values do not.
• rows=42 — planner’s estimate of rows this node will emit
• actual rows=42 — rows actually emitted when the query ran
• loops=1 — how many times this node executed (important inside joins)

The diagnostic rule: compare rows (estimate) to actual rows (reality). A 10× gap is suspicious. A 1000× gap is why your query takes 8 minutes instead of 50ms.

The GPS metaphor

EXPLAIN is the GPS route preview before you drive. EXPLAIN ANALYZE is the dashcam from the actual trip. The preview says “this route is 12 minutes” — that is an estimate from map data. The dashcam says “the trip took 47 minutes because of traffic the map did not know about.” The gap between estimate and reality is exactly where you intervene: bad map data (stale statistics) gives bad route choices; bad sensors (no index for this condition) make the trip slower than the route promised.

A concrete scenario: finding the bottleneck

A team’s search query takes 200ms on staging, 8 seconds in production. EXPLAIN ANALYZE reveals:

Hash Join  (cost=2400..55000 rows=50000)
            (actual time=7800..8050 rows=42)
  Hash Batches: 64  Memory Usage: 2.1GB

Batches: 64 means the hash table spilled to disk 64 times — the result of work_mem being too small for the join’s actual size. Fix: SET work_mem = '64MB'. Same query, 220ms. One EXPLAIN, one config change.