Reading a Postgres query plan from the bottom up

The plan is a tree, and rows flow up it

For a long time I read plans top to bottom, because that is the order the text comes out. That was the wrong model. The first line is the root, but the work happens in the most indented nodes. Each node pulls rows from its children and passes its output to its parent. A parent cannot finish before its children do, so the time shown on a node already includes everything beneath it.

Children are marked with ->. In a Hash Join, the first child is the probe side. The second child, a Hash node, is the side loaded into an in-memory table. Once that clicks, most join plans become easy to follow.

A sample plan

 Hash Join  (cost=1843.22..9120.45 rows=212 width=64) (actual time=18.904..142.370 rows=4871 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=3120 read=874
   ->  Index Scan using orders_created_at_idx on orders o  (cost=0.43..7018.11 rows=212 width=40) (actual time=0.031..118.602 rows=4871 loops=1)
         Index Cond: ((created_at >= '2026-10-01') AND (created_at < '2026-11-01'))
         Filter: (status = 'paid'::text)
         Rows Removed by Filter: 41220
         Buffers: shared hit=2904 read=860
   ->  Hash  (cost=1204.00..1204.00 rows=51200 width=36) (actual time=18.511..18.512 rows=51200 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 3432kB
         ->  Seq Scan on customers c  (cost=0.00..1204.00 rows=51200 width=36) (actual time=0.009..9.870 rows=51200 loops=1)
 Planning Time: 0.412 ms
 Execution Time: 143.906 ms

Cost versus actual time

Each node carries two sets of numbers. cost=0.43..7018.11 is the planner's estimate in arbitrary units derived from settings such as seq_page_cost and cpu_tuple_cost. The first number is startup cost, the time before the first row, and the second is total cost. I only compare costs between alternative plans for the same query. They are not milliseconds.

actual time=0.031..118.602 is real time in milliseconds, again a pair: first row and last row. Both values are averages per loop, so the real total for a node is the per-loop time multiplied by loops. EXPLAIN ANALYZE also reads the clock for every row, which can distort very fast plans on machines with slow timers. EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) keeps the row counts and buffer numbers without the per-row timing.

EXPLAIN ANALYZE executes the statement. For an UPDATE or DELETE, wrap it in a transaction and roll back.

Estimated rows versus actual rows

This comparison matters more than anything else in the output. The planner chooses join order, join method and scan type from its row estimates. When an estimate is off by a factor of ten or more, the choices above that node are often wrong. In the sample, the orders scan expected 212 rows and got 4871. A nested loop looks sensible for 212 rows and painful for 4871, so that miss is where I start digging.

Estimates drift for familiar reasons. Statistics are stale because autoanalyze has not run since a big load. Two columns are correlated, and the planner multiplies their selectivities as if they were independent. Or the predicate wraps a column in a function, which hides it from the statistics completely. The usual fixes are a manual ANALYZE, a multivariate statistic, or a higher per-column target:

CREATE STATISTICS addr_city_country (dependencies, ndistinct)
  ON city, country FROM addresses;
ANALYZE addresses;

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

Scan types

Seq Scan is not a red flag by itself. If a query needs 40% of a table, reading it sequentially beats chasing random index entries. An Index Scan reads index entries and then fetches each heap row, which is random I/O. A Bitmap Heap Scan builds a bitmap from the index and visits heap pages in physical order, which suits middle-sized results.

Pay attention to Rows Removed by Filter. In the sample, the index narrowed the search by date but not by status, so Postgres fetched 46,091 heap rows to keep 4,871. A composite index that includes status might fix that.

Index Only Scan skips the heap for pages the visibility map marks all-visible. The Heap Fetches: line shows how often it still went to the table. A large number usually means vacuum is behind on that table.

Nested Loop versus Hash Join

A Nested Loop runs its inner side once per outer row. Check loops= on the inner node. With a small outer side and an index probe inside, this is excellent. With an underestimated outer side, the loop count can explode. A Hash Join loads the smaller input into a hash table and probes it with the larger one. It scales linearly and needs no index. Batches: 1 is what you want. A higher number means the hash spilled to disk because work_mem was too small.

What I look at first

I start with the execution time and the node that owns most of it, then walk down to its children. Next I compare estimated and actual rows at each node. Then I check buffers. High read counts mean pages came from outside shared_buffers, and temp read or temp written means a sort or hash spilled to disk. Last, I multiply inner-node loops by per-loop time, because that is where the surprises usually hide.