Reading EXPLAIN: How the Planner Describes a Query

The one thing to understand first

The PostgreSQL executor runs a tree of plan nodes, each pulling rows from its children on demand (the Volcano/iterator model, src/backend/executor/). EXPLAIN prints that tree. The node at the top produces the final result; leaves are scans of tables or indexes. Reading a plan means reading this tree from the inside out.

A bad plan is almost never a dumb planner — it is a planner working from a wrong row estimate. So the one skill that matters when reading EXPLAIN is comparing the planner's estimated rows to the actual rows; the biggest mismatch is where your problem lives.

The cost numbers

Each node shows cost=startup..total rows=N width=B:

• startup cost — work before the first row can be emitted (e.g. a sort must read everything first).
• total cost — work to return all rows.
• rows — the planner's estimate of rows emitted.
• width — average row size in bytes.

Costs are in arbitrary units anchored to seq_page_cost = 1.0. They are only meaningful relative to each other.

EXPLAIN ANALYZE: estimates vs reality

Plain EXPLAIN shows estimates; EXPLAIN ANALYZE actually runs the query and adds actual time=...rows=...loops=.... The single most valuable habit is comparing estimated rows to actual rows. A large gap means the planner is working from bad statistics — and a bad row estimate is the root cause of most bad plans (wrong join order, wrong join type, wrong scan).

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

The loops trap

In a nested loop, the inner node's actual rows and actual time are per loop. To get the true total, multiply by loops. A node that looks cheap (rows=1) executed 2 million times is your bottleneck. Always factor in loops before deciding what is slow.

BUFFERS tells the I/O story

With BUFFERS, each node reports shared hit (found in cache) and read (fetched from disk). High read on a node points to a working set that does not fit in shared_buffers or an index that is not being used. shared hit with high counts can still be slow if the node is visited in a huge loop.

Common node types

• Seq Scan — read the whole table. Correct when returning a large fraction of rows.
• Index Scan — walk an index, fetch matching heap tuples. Good for selective predicates.
• Index Only Scan — answer entirely from the index (needs the visibility map all-visible) — no heap fetch.
• Bitmap Heap Scan — build a bitmap of matching TIDs from one or more indexes, then read the heap in physical order. Good for medium selectivity.
• Nested Loop / Hash Join / Merge Join — the three join strategies.

Layer 3 — Watch it happen on your own database

-- Always analyze with buffers; read estimated vs actual rows on every node
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

-- Force the contrast: a stale-stats table will mis-estimate
ANALYZE orders;            -- refresh stats, then re-run the EXPLAIN above
SET enable_seqscan = off;  -- session-only: see the alternative plan's cost

Run the EXPLAIN (ANALYZE, BUFFERS) and walk the tree inside-out: on each node compare rows= (estimate) to actual rows=, multiply inner-loop figures by loops, and read shared hit vs read to see what came from cache. Toggling enable_seqscan off (for one session only) is a safe way to make the planner show you the plan it rejected and why it cost more.

Layer 4 — The levers this hands you

Reading a plan is only useful if it changes what you do next. The decision tree:

• Find the node with the largest actual time after accounting for loops.
• Check whether its estimated rows match actual rows.
• If estimates are wrong → fix statistics (ANALYZE, extended statistics, higher statistics_target).
• If estimates are right but the plan is still poor → reconsider indexes or query shape.

• **BUFFERS is non-negotiable** — without it you cannot tell a cache hit from a disk read.
• **auto_explain** captures plans for slow queries in production without manual reproduction.
• **enable_* toggles** (seqscan, nestloop, hashjoin) are diagnostic levers, not permanent settings.

Layer 5 — What an Oracle DBA should expect vs what they get

EXPLAIN is your DBMS_XPLAN, but the workflow and philosophy differ:

• No hints by design. Oracle lets you pin a plan with optimizer hints; PostgreSQL deliberately has none in core. You fix the inputs (statistics, indexes, query shape) rather than overriding the optimizer.
• Estimated vs actual in one command. EXPLAIN ANALYZE is like DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST — both estimate and actual rows side by side. The "E-Rows vs A-Rows" habit transfers directly.
• Costs are unitless and relative. Like Oracle's cost they are not milliseconds; only the ratios between nodes matter.
• No SQL Plan Baselines / SQL Profiles. Plan stability comes from stable statistics and (optionally) extensions like pg_hint_plan, not from a built-in baseline store.

Key takeaway

EXPLAIN prints the executor's node tree with cost=startup..total rows width; EXPLAIN (ANALYZE, BUFFERS) adds the truth — actual time, rows, loops, and cache vs disk reads. Read inside-out, multiply by loops, and chase the biggest estimate-vs-actual gap. Because PostgreSQL has no hints, the fix is almost always better statistics or a better index — not arguing with the planner.