Reading EXPLAIN

Last lesson you added an index and believed it helped. EXPLAIN is how you check: it shows the exact plan the planner chose, node by node, so you stop guessing and start reading. This lesson is pure exploration — every query here is read-only, so run them freely and watch the plans change.

The seed is a classic pair of tables: ~4,000 customers and ~40,000 orders, with an index on orders.customer_id. Enough rows that the planner has real choices to make.

SELECT
(SELECT count(*) FROM customers) AS customers,
(SELECT count(*) FROM orders)    AS orders;

EXPLAIN shows the plan without running the query

Put EXPLAIN in front of any query and Postgres returns its plan — what it intends to do — without executing it:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

You'll see something like this. Your numbers will differ from run to run and machine to machine — read the shape, not the digits:

Index Scan using orders_customer_id_idx on orders  (cost=0.29..40.62 rows=10 width=29)
  Index Cond: (customer_id = 42)

The planner picked an Index Scan: it walks the index on customer_id straight to the matching rows instead of reading all 40,000. That's the index from the previous lesson earning its keep.

The estimate line: cost, rows, width

Every plan node ends with a parenthesized estimate. Decode it:

(cost=0.29..40.62 rows=10 width=29)

• cost=startup..total — two numbers in arbitrary units (roughly "how many sequential page reads would cost the same"). startup is the cost before the first row can be returned; total is the cost to return all rows. They are estimates, not milliseconds — only useful for comparing plans against each other.
• rows=10 — how many rows the planner estimates this node will emit.
• width=29 — estimated average row size in bytes. Wider rows mean more data to move.

The startup vs total split matters for LIMIT: a plan with high startup cost is a bad fit when you only want the first few rows. Compare an index scan (tiny startup, it can stream) against a sort (must consume everything before emitting the first row):

EXPLAIN SELECT * FROM orders ORDER BY amount DESC LIMIT 5;

A plan is a tree — read it inside-out

Bigger queries produce a tree of nodes. Indentation shows the shape: each child feeds its parent, so you read from the most-indented (innermost) node outward and upward. The innermost nodes run first; the top node produces the final result.

Here's a join between our two tables, filtered to one customer:

EXPLAIN SELECT c.name, o.status, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42;

A plan like this:

Nested Loop  (cost=0.58..49.10 rows=10 width=40)
  ->  Index Scan using customers_pkey on customers c  (cost=0.28..8.30 rows=1 width=15)
        Index Cond: (id = 42)
  ->  Index Scan using orders_customer_id_idx on orders o  (cost=0.29..40.62 rows=10 width=29)
        Index Cond: (customer_id = 42)

Read it inside-out: find the one customer via its primary-key index (innermost), then for that customer look up their orders via the customer_id index, and the Nested Loop on top stitches each customer row to its matching order rows. One customer, so the loop runs once — cheap.

EXPLAIN ANALYZE actually runs the query

EXPLAIN only estimates. Add ANALYZE and Postgres executes the query for real, then reports actual timings and row counts next to the estimates:

EXPLAIN (ANALYZE) SELECT c.name, o.status, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42;

Nested Loop  (cost=0.58..49.10 rows=10 width=40) (actual time=0.031..0.052 rows=10 loops=1)
  ->  Index Scan using customers_pkey on customers c  (cost=0.28..8.30 rows=1 width=15) (actual time=0.012..0.013 rows=1 loops=1)
        Index Cond: (id = 42)
  ->  Index Scan using orders_customer_id_idx on orders o  (cost=0.29..40.62 rows=10 width=29) (actual time=0.010..0.020 rows=10 loops=1)
        Index Cond: (customer_id = 42)
Planning Time: 0.180 ms
Execution Time: 0.090 ms

Now each node carries (actual time=startup..total rows=N loops=L) in real milliseconds, plus two summary lines at the bottom: Planning Time (choosing the plan) and Execution Time (running it).

ANALYZE executes the query. For a SELECT that's harmless. For an UPDATE, DELETE, or INSERT it will actually change your data. To inspect a write safely, wrap it in a transaction and roll back — or just don't run ANALYZE on destructive statements.

BEGIN;
EXPLAIN (ANALYZE) DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;

loops multiplies

loops=L is how many times a node ran. Inside a Nested Loop, the inner node runs once per outer row, and the actual time shown is per loop — so the real total is roughly time × loops. When a join touches many customers, that inner scan repeats a lot:

EXPLAIN (ANALYZE) SELECT c.country, count(*)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.country;

Watch for a big loops on an expensive inner node — that's often where time goes.

Estimated vs actual = stale statistics

The most valuable habit: compare the estimated rows= to the actual rows=. Close together means the planner understands your data. Wildly off (estimate 10, actual 10,000) means its statistics are stale, and it may have picked a bad plan on bad information. The fix is to refresh them:

ANALYZE orders;

That's the manual version of what autovacuum does in the background. Our seed already ran ANALYZE, so your estimates should track reality — a stale table in production is where they drift apart.

Node types you'll keep seeing

The plan vocabulary is small once you recognize the players:

Scans — how a single table is read:

• Seq Scan — read every row. Fine for small tables or when a filter matches most rows.
• Index Scan — walk an index to the matching rows, then fetch each from the table. Great when few rows match.
• Index Only Scan — answered entirely from the index, no table fetch, because every column needed is in the index.
• Bitmap Index Scan + Bitmap Heap Scan — a middle ground: build a bitmap of matching row locations from the index, then read the table in physical order. Postgres picks this when a query matches too many rows for a plain Index Scan (random fetches would hurt) but too few to bother reading the whole table.

Nothing indexes status yet, so both of these run as Seq Scans — but watch the rows= estimate, which is how the planner tells a rare value from a common one:

EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';

EXPLAIN SELECT * FROM orders WHERE status = 'shipped';

Same plan, very different estimates: cancelled is ~4% of the table, shipped ~85%. That selectivity gap is exactly what decides whether an index is worth building — a Bitmap Index Scan (from the list above) is what Postgres would reach for on the rare value once an index exists. You'll make that happen in the capstone.

Joins — how two inputs are combined:

• Nested Loop — for each outer row, probe the inner side. Best when one side is tiny (like our one-customer join).
• Hash Join — build a hash table from one side, stream the other through it. The planner's usual pick for joining two large sets.
• Merge Join — both inputs sorted on the join key, then zipped together.

Join all orders to their customers and the planner switches strategy — no single-row filter, so a Hash Join over the whole tables beats looping:

EXPLAIN SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

The rest — Sort, Aggregate / HashAggregate (what GROUP BY produces), and Limit (stops early once it has enough rows) round out the set.

BUFFERS shows the I/O

Timings tell you how long; BUFFERS tells you how much data moved. Add it to see shared-buffer hits (found in cache) versus reads (fetched from disk) — the real cost of a query is often I/O, not CPU:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'shipped';

Seq Scan on orders  (cost=0.00.....) (actual time=... rows=34000 loops=1)
  Filter: (status = 'shipped'::text)
  Rows Removed by Filter: 6000
  Buffers: shared hit=294
Planning Time: ...
Execution Time: ...

A high read= count on a supposedly fast query is a red flag — that's disk I/O the query planner had to pay for. Rows Removed by Filter is a bonus: rows the scan touched and then threw away, a hint that an index might have skipped them entirely.

A few more options worth knowing:

• VERBOSE — output column lists and fully-qualified names for each node.
• SETTINGS — show any planner settings changed from their defaults (handy when a plan looks surprising).
• FORMAT JSON — machine-readable output, for tools and plan visualizers.

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

Your turn: let a plan drive a fix

You've spent the lesson reading plans — now use one to make a decision. Earlier, status = 'cancelled' fell back to a Seq Scan because nothing indexes status. Confirm it, this time with real timings:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'cancelled';

A Seq Scan over all 40,000 rows just to keep ~4% of them. The plan named the problem; add the index it's missing:

CREATE INDEX orders_status_idx ON orders (status);

Now read the plan one more time:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'cancelled';

The top node flips to a Bitmap Index Scan on orders_status_idx feeding a Bitmap Heap Scan, and the Execution Time drops — Postgres now seeks the ~4% that match instead of reading everything. That's the whole loop this lesson is about: read the plan, spot the Seq Scan, add the fix, and confirm from the new plan that it worked.

What you learned

• EXPLAIN shows the planner's chosen plan without running the query; EXPLAIN (ANALYZE) runs it for real and reports actual timings and row counts — so never ANALYZE a write unless it's wrapped in a transaction you ROLLBACK.
• A plan is a tree: read from the most-indented (innermost) nodes outward and upward — each node feeds its parent.
• The estimate line is cost=startup..total rows=N width=B: costs are arbitrary units for comparing plans, startup is cost-to-first-row (matters with LIMIT), rows and width are estimates.
• With ANALYZE, watch two things: loops=L (an inner node's time is per loop, so multiply), and estimated-vs-actual rows — a big gap means stale statistics, so run ANALYZE (or let autovacuum).
• Recognize the node types: Seq / Index / Index Only / Bitmap scans; Nested Loop / Hash / Merge joins; Sort, Aggregate/HashAggregate, Limit — and why selectivity decides which scan Postgres picks.
• EXPLAIN (ANALYZE, BUFFERS) exposes cache hits vs disk reads; VERBOSE, SETTINGS, and FORMAT JSON add detail when you need it.
• The payoff loop: read a plan, spot a Seq Scan on an unindexed filter, add the index, and confirm from the new plan (Seq Scan → Bitmap Index Scan) that it worked — EXPLAIN-driven tuning.

Up next: index types beyond B-tree — GIN, GiST, BRIN, and more.