EXPLAIN predicts, EXPLAIN ANALYZE measures
Plain EXPLAIN asks the planner what it intends to do and returns that plan without running it. Every number you see is a guess derived from table statistics. EXPLAIN ANALYZE executes the statement to completion and annotates each node with what really happened, which is why it is the only form worth reading when you are chasing a regression.
The consequence people forget is that EXPLAIN ANALYZE on an UPDATE, DELETE or INSERT performs the write, so a measured plan for a data-modifying statement needs an explicit transaction you then roll back. The timing instrumentation also carries overhead on plans with millions of node invocations, which makes a node's reported time an attribution of where work went rather than a benchmark.
The tree executes from the inside out
The output is a tree printed with the root at the top. Indentation and -> mark parent-child relationships, and rows flow upward: each child produces tuples that its parent consumes. You therefore read it innermost-first to follow execution order, and top-down to understand intent. On a two-table join the scans are the leaves, the join node is their parent, and any sort, aggregate or limit sits above that.
Nested Loop (cost=0.72..312.44 rows=12 width=48) (actual time=0.061..1841.203 rows=9214 loops=1)
-> Index Scan using orders_created_at_idx on orders o (cost=0.43..96.10 rows=12 width=24) (actual time=0.034..12.885 rows=9214 loops=1)
Index Cond: (created_at > '2026-07-01'::timestamptz)
-> Index Scan using order_items_order_id_idx on order_items i (cost=0.29..17.98 rows=1 width=24) (actual time=0.180..0.196 rows=1 loops=9214)
Index Cond: (order_id = o.id)
Planning Time: 0.214 ms
Execution Time: 1843.9 ms
What each number on a node means
cost=0.43..96.10 is a startup cost and a total cost. Startup is the work needed before the first row can be emitted, which is why a sort has a large startup cost and a sequential scan has almost none; total is the cost to return every row. The unit is arbitrary. It is not milliseconds and not I/O operations: by convention seq_page_cost is 1.0 and everything else, including random_page_cost and cpu_tuple_cost, is expressed relative to it. Costs are therefore only ever useful for comparing two plans for the same query on the same server.
rows and width are the planner's estimated output cardinality and average row size in bytes. In the parenthesised actual section, rows is the measured output but per loop, and loops is how many times the node was executed. The inner scan above returned 9,214 rows in total, not one: one row per loop across 9,214 loops. The same applies to actual time, which is per-loop first-row and last-row timing, so the true contribution of that node is roughly 0.196 ms times 9,214, or about 1.8 seconds. Read those two fields independently and you will conclude the cheap-looking node is fine.
The divergence that explains almost everything
The single most valuable signal in the whole output is the ratio between estimated rows and total actual rows on each node. Work up the tree and find the lowest node where the two disconnect badly, because that is where the planner's model of your data broke, and every choice above it was made on bad input.
The plan above is the canonical shape. The planner estimated 12 orders and priced a nested loop accordingly, which is a perfectly rational choice for 12 iterations. Reality was 9,214 orders, so it performed 9,214 index lookups where a hash join over one pass of order_items would have finished in a fraction of the time. The nested loop is not the bug; the cardinality estimate is. That distinction points the fix at statistics, at extended statistics for correlated columns, or at rewriting a predicate the planner cannot estimate — not at adding an index, and certainly not at disabling nested loops globally.
Choosing between the three scan shapes
A Seq Scan reads every page of the table in physical order. An Index Scan walks the index and, for each match, fetches the corresponding heap tuple by pointer, which is a random access; an Index Only Scan is the same without the heap fetch, possible when the index supplies every referenced column and the visibility map says the pages are all-visible.
A Bitmap Heap Scan, always fed by one or more Bitmap Index Scans, sits between them: it collects matching tuple IDs into a bitmap first, then reads the heap once in physical block order. That is what wins for medium selectivity, where there are too many matches for random fetches to be cheap but far too few to justify reading the whole table. It is also the mechanism that lets two separate indexes be combined with BitmapAnd or BitmapOr.
A sequential scan is the right answer more often than candidates admit. When a predicate matches a large fraction of the rows, sequential access beats one random read per row, and the crossover is low: often single-digit percentages, depending on row width and cache state. On a small table spanning a handful of pages the index is pure overhead, and an unfiltered aggregate has nothing to filter with anyway. Seeing Seq Scan and immediately proposing an index, without checking actual rows against table size, reads as pattern-matching rather than analysis.
BUFFERS turns guesses about I/O into evidence
Add BUFFERS and each node reports shared hit (found in the buffer cache), shared read (fetched from the operating system, so possibly from disk), plus dirtied and written. From PostgreSQL 18 it is included with ANALYZE by default; on earlier versions you ask for it explicitly with EXPLAIN (ANALYZE, BUFFERS).
This is what separates a slow query from a cold one. Two runs with identical row counts where the first is mostly read and the second mostly hit means you measured cache warming, not a plan problem. And temp read/temp written on a sort or hash node says the operation spilled out of work_mem onto disk, which is a memory setting to change rather than a plan to fix.
Before proposing any fix, say out loud which node has the worst estimated-to-actual row ratio and what the planner would have chosen had it known the true count. That sentence is the entire skill; everything else in the plan is supporting detail.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).