BrevFeed
Sign in

40. EXPLAIN ANALYZE: reading query plans

📖 Reading · 9 min
💡 Every code box below is live — edit it and hit Run.

From "recognize the algorithm name" to actually reading a plan

If you've ever run EXPLAIN on a join, you may have spotted names like Nested Loop/Hash Join/Merge Join in the output without a full explanation of everything else on the page. This lesson is that full explanation — the foundational skill for every remaining lesson in this module and Module 12.

EXPLAIN vs. EXPLAIN ANALYZE — a critical distinction

EXPLAIN SELECT * FROM film WHERE rental_rate > 4.00;

Plain EXPLAIN shows the planner's estimated plan — it does not run the query. Costs and row counts are predictions, based on table statistics (gathered by ANALYZE — the performance-killers lesson at the end of this module shows what happens when they go stale).

EXPLAIN ANALYZE SELECT * FROM film WHERE rental_rate > 4.00;

EXPLAIN ANALYZE actually executes the query and shows real, measured timing and row counts alongside the original estimates. This distinction matters practically: EXPLAIN ANALYZE on an UPDATE, DELETE, or a SELECT with heavy side effects (a function with side effects, rare but possible) really performs that write — wrap it in BEGIN ... ROLLBACK if you want to inspect a modifying query's plan without committing the change:

BEGIN;
EXPLAIN ANALYZE UPDATE film SET rental_rate = rental_rate * 1.1 WHERE film_id = 1;
ROLLBACK;

Reading one plan line by line

Seq Scan on film  (cost=0.00..66.50 rows=423 width=384)
  Filter: (rental_rate > 4.00)
  • Seq Scan on film — the operation: a sequential scan (read every row in the table, in physical storage order) on film. Other common operations: Index Scan, Index Only Scan, Bitmap Heap Scan (next lesson covers what triggers each).
  • cost=0.00..66.50 — the planner's estimated cost, in arbitrary units (not milliseconds — a relative measure calibrated against seq_page_cost and other planner constants). The first number is the estimated cost to return the first row; the second is the estimated cost to return all rows.
  • rows=423 — the planner's estimated number of rows this step will produce.
  • width=384 — the planner's estimated average row width, in bytes.
  • Filter: (rental_rate > 4.00) — the condition applied at this step. A Filter (as opposed to an Index Cond, next lesson) means the row was fetched first, then checked against the condition afterward — every row in the table gets read, whether or not it matches.

The same plan, with EXPLAIN ANALYZE

Seq Scan on film  (cost=0.00..66.50 rows=423 width=384) (actual time=0.015..0.412 rows=417 loops=1)
  Filter: (rental_rate > 4.00)
  Rows Removed by Filter: 583

New fields, all measured, not estimated:

  • actual time=0.015..0.412 — actual milliseconds to first row, and to complete the step (cumulative across loops, see below).
  • actual rows=417 — the real row count, comparable directly against the estimate (rows=423) — a big divergence between estimated and actual rows is one of the most important things to notice in any plan, since it means the planner's statistics are stale or the query has a shape the planner can't estimate well, and is a leading cause of the planner choosing a bad plan elsewhere in a larger query (the performance-killers lesson returns to this).
  • loops=1 — how many times this step executed. A step nested inside a Nested Loop executes once per outer row, so loops can be far greater than 1, and actual time there is the per-loop average, not the total — a detail that trips people up reading nested-loop plans until it's pointed out once.
  • Rows Removed by Filter: 583 — rows read and then discarded by the filter — a strong, direct signal that an index on rental_rate could let the engine skip reading those 583 rows entirely (next lesson).

Top-to-bottom, but read cost bottom-up

A multi-line plan is a tree, indentation shows nesting, and Postgres prints it with the outermost operation first, its children indented below. But when reasoning about where time actually goes, read bottom-up: the innermost (most indented) operations run first and feed their output to the operations above them.

EXPLAIN ANALYZE
SELECT c.first_name, r.rental_date
FROM customer c
JOIN rental r ON r.customer_id = c.customer_id
WHERE c.customer_id = 1;

Nested Loop  (cost=0.28..359.16 rows=32 width=14) (actual time=0.02..0.15 rows=32 loops=1)
  ->  Index Scan using customer_pkey on customer c  (... actual time=0.01..0.01 rows=1 loops=1)
        Index Cond: (customer_id = 1)
  ->  Seq Scan on rental r  (... actual time=0.01..0.13 rows=32 loops=1)
        Filter: (customer_id = 1)

Read this as: first, the indented Index Scan finds customer 1 (fast, one row). Then, for each row that step produced (here just 1), the Seq Scan on rental runs — reading the whole rental table, filtering to customer_id = 1. The outer Nested Loop line's total cost/time is the combination of both children. This bottom-up reading habit is the single most useful skill for spotting which specific step is responsible for a slow query's total time — the performance-tuning capstone walks this workflow end to end.

Check yourself

  1. What's the practical difference between EXPLAIN and EXPLAIN ANALYZE — specifically, does either one actually execute the query?
  2. In an EXPLAIN ANALYZE plan, what does a large gap between the estimated rows= and the actual rows= usually indicate?
  3. Why should you read a multi-line plan's cost attribution bottom-up rather than top-down?
← Previous Next →