Reading EXPLAIN

You've learned what the optimizer does (16.1) and what indexes give it to work with (16.2). Now you get the instrument that shows you its decisions: EXPLAIN. Every "why is this query slow?" investigation in your career will start with this word, and reading its output — a little tree of plan nodes — is the difference between guessing at fixes and seeing the problem. This lesson's playground has a 100,000-row orders table; the plans you'll read are real.

EXPLAIN vs EXPLAIN ANALYZE

Two forms, one crucial difference:

• EXPLAIN query — the optimizer plans the query and shows you the plan, without running it. Instant, and safe on anything.
• EXPLAIN ANALYZE query — plans it, actually runs it, and reports what really happened alongside the estimates. The ground truth — but it executes the query, so an EXPLAIN ANALYZE DELETE … really deletes (wrap risky ones in BEGIN … ROLLBACK, 12.1's trick).

Plain EXPLAIN on a search of our big table, keeping the output free of cost noise for now:

EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE customer_id = 42;

                     QUERY PLAN
----------------------------------------------------
 Bitmap Heap Scan on big_orders
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on big_orders_customer_idx
         Index Cond: (customer_id = 42)
(4 rows)

That's a plan tree. Each line starting with -> is a node feeding rows to the node above it.

Reading the tree: bottom-up, inside-out

The rule that unlocks every plan you'll ever read: execution starts at the deepest, most-indented node; rows flow upward. Reading that plan from the bottom:

1. Bitmap Index Scan on big_orders_customer_idx — the index from 16.2 is consulted with the condition customer_id = 42, producing the locations of matching rows.
2. Bitmap Heap Scan on big_orders — those locations are visited in the table itself to fetch the actual rows (rechecking the condition on the way).

Two nodes, one story: use the index to find, then the table to fetch — the phone-book model from 16.2, drawn by the machine itself. Now delete the index's usefulness — filter on a column with no index — and watch the plan change shape:

EXPLAIN (COSTS OFF)
SELECT * FROM big_orders WHERE amount > 50;

             QUERY PLAN
------------------------------------
 Seq Scan on big_orders
   Filter: (amount > '50'::numeric)
(2 rows)

Seq Scan: read the whole table, top to bottom, keeping rows that pass the filter. On 100,000 rows that's the honest choice here — and remember 16.1's lesson: a seq scan is not automatically a problem. For a query keeping a large share of the table (this filter keeps about 45% of the rows), reading it straight through beats bouncing along an index. The smell isn't "Seq Scan"; it's "Seq Scan feeding a filter that keeps 12 rows of a million."

The nodes that matter

A dozen node types cover practically every plan you'll meet. The scan family you've seen (Seq Scan, Index Scan, Bitmap … Scan — and Index Only Scan, 16.2's covering-index payoff). The rest of the cast:

EXPLAIN (COSTS OFF)
SELECT v.customer_id, sum(b.amount)
FROM vip_customers v
JOIN big_orders b ON b.customer_id = v.customer_id
GROUP BY v.customer_id;

                     QUERY PLAN
----------------------------------------------------
 HashAggregate
   Group Key: v.customer_id
   ->  Hash Join
         Hash Cond: (b.customer_id = v.customer_id)
         ->  Seq Scan on big_orders b
         ->  Hash
               ->  Seq Scan on vip_customers v
(7 rows)

Bottom-up again: the small vip_customers table is scanned and loaded into a Hash (an in-memory lookup table); big_orders is scanned once, each row probing that hash — that's a Hash Join, the workhorse for joining a big table to a smaller one. The survivors flow up into HashAggregate, which implements your GROUP BY. The other two join strategies from 22.2's deeper story: Nested Loop (for each outer row, probe the inner side — unbeatable when the outer side is tiny and the inner side is indexed) and Merge Join (both sides sorted, zipped together — shines on pre-sorted, large inputs). You don't choose between them; you read which one the planner chose and ask whether its assumptions look right.

One more everyday pair — ORDER BY … LIMIT:

EXPLAIN (COSTS OFF)
SELECT * FROM big_orders ORDER BY amount DESC LIMIT 5;

             QUERY PLAN
------------------------------------
 Limit
   ->  Sort
         Sort Key: amount DESC
         ->  Seq Scan on big_orders
(4 rows)

Sort is where unindexed ORDER BYs pay their bill, and Limit truncates the flow above it. (Postgres is smart enough to keep only the top 5 during that sort — EXPLAIN ANALYZE would show it as Sort Method: top-N heapsort.)

Estimates vs reality: what ANALYZE adds

Run the first query as EXPLAIN ANALYZE in the playground below and each node grows a second parenthesis — real numbers next to the guesses. The four that matter, and the smells they expose:

• rows= (estimate) vs rows= (actual) — the single most important comparison in performance work. Off by 10× or more? The planner chose its strategy using bad information (stale statistics, correlated columns), and every decision above that node is suspect. That's the row-estimate cliff.
• loops= — how many times the node ran. A Nested Loop's inner side with loops=48000 is your quadratic blowup, found.
• Buffers: — pages touched: shared hit came from cache, read came from disk. I/O made visible.
• Sort Method: — quicksort Memory: is fine; external merge Disk: is a sort that spilled — the classic sign it needs an index or a smaller working set.

Timing numbers (actual time=…, Execution Time:) vary run to run and machine to machine — in this course we read plans for their structure and row counts, which are the part that transfers to your production database anyway.

A repeatable tuning workflow

When a slow query lands on your desk, resist the urge to sprinkle indexes. The loop that actually works:

1. Measure: EXPLAIN ANALYZE the real query with real parameters. Save the output.
2. Hypothesize: find the node where actual rows or loops explode, or the estimate lies — that node, not the query as a whole, is your suspect.
3. Change one thing: an index for that condition, a rewrite of that subquery, an ANALYZE to refresh statistics.
4. Re-measure and compare. Better? Keep it and stop. Not better? Revert, back to step 2.

One change per iteration — plans shift globally when you touch anything, and two changes at once means you no longer know which one mattered. This discipline, plus the reading skill above, is most of what "the database person" (23.3) actually does. Next lesson: the recurring villains those investigations turn up — the tuning patterns that matter.