How to Read a PostgreSQL Query Plan Step by Step Guide

Anyone can paste a plan into a chat. Reading one is a separate skill, and it is a mechanical one: a handful of rules turn a screen of nested text into a short list of suspects. This article is a worked walkthrough, line by line, of one plan with a problem in it, followed by the rules in a form you can apply to your own. It is the practical companion to reading EXPLAIN ANALYZE output in PostgreSQL step by step, which explains the node types and numbers in general.

The plan to read

The query: the ten customers who spent the most in March, with their order counts.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, count(*) AS orders, sum(o.total) AS spent
FROM customer c
JOIN app_order o ON o.customer_id = c.id
WHERE o.placed_at >= '2026-03-01' AND o.placed_at < '2026-04-01'
GROUP BY c.name
ORDER BY spent DESC
LIMIT 10;

Limit  (cost=48210.5..48210.5 rows=10 width=48) (actual time=3120.4..3120.5 rows=10 loops=1)
  ->  Sort  (cost=48210.5..48212.0 rows=600 width=48) (actual time=3120.4..3120.4 rows=10 loops=1)
        Sort Key: (sum(o.total)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=48190.0..48196.0 rows=600 width=48) (actual time=3110.2..3118.9 rows=42000 loops=1)
              Group Key: c.name
              Batches: 1  Memory Usage: 6200kB
              ->  Nested Loop  (cost=0.85..47890.0 rows=40000 width=20) (actual time=0.09..3040.1 rows=410000 loops=1)
                    ->  Seq Scan on customer c  (cost=0.00..1800.0 rows=100000 width=24) (actual time=0.01..22.0 rows=100000 loops=1)
                    ->  Index Scan using app_order_customer_idx on app_order o  (cost=0.43..0.46 rows=0 width=12) (actual time=0.02..0.03 rows=4 loops=100000)
                          Index Cond: (customer_id = c.id)
                          Filter: ((placed_at >= '2026-03-01') AND (placed_at < '2026-04-01'))
                          Rows Removed by Filter: 18
Planning Time: 0.5 ms
Execution Time: 3121.0 ms

Execution takes 3.1 seconds. Which step is responsible?

Step 1: find the shape

Indentation gives the tree. The root is Limit. Its child is Sort, whose child is HashAggregate, whose child is Nested Loop. The nested loop has two children: a Seq Scan on customer (the outer side, listed first) and an Index Scan on app_order (the inner side). So the data flow is: scan all customers; for each, probe the order index; aggregate; sort; take ten.

Step 2: bottom-up, one node at a time

Seq Scan on customer. Estimated 100,000 rows, actual 100,000, time 22 ms. A good estimate and a cheap node. Nothing to fix here.

Index Scan on app_order. Estimated rows=0 (the planner rounds a fractional estimate down, meaning less than one row per probe), actual rows=4 per loop, loops=100000. Per-loop time is 0.03 ms. Multiply: 0.03 ms times 100,000 loops is about 3,000 ms. This is the node that holds the time. The filter on placed_at then discarded 18 rows per probe (Rows Removed by Filter: 18), so each probe read 22 rows to keep 4. The index on customer_id finds all of a customer's orders over all time, and the date range is applied afterward.

Nested Loop. Total time 3,040 ms, of which the inner scans account for about 3,000 and the outer scan 22. Its own work is small. The loop is expensive because it runs 100,000 times.

HashAggregate. Total 3,118 ms, children 3,040 ms, so its own time is about 78 ms. Its estimate was 600 groups and the actual was 42,000, so the planner expected a much smaller result. That wrong estimate matters less here because only one memory batch was needed, but it hints that the planner underestimated the cardinality of name.

Sort and Limit. Own time under 2 ms. The top-N heapsort keeps only ten rows, which is why memory use is 26 kB.

Step 3: locate the cost

Reading the numbers, the cost sits in the index probe repeated 100,000 times. The planner chose a nested loop starting from customers because it expected the join to produce 40,000 rows and thought each probe would return almost nothing (rows=0). In reality 410,000 order rows matched, over four per customer.

Two facts stand out. First, the estimate for the inner scan, 0 rows, versus 4 actual, is off, though a small absolute number. Second, and more important, the plan visits every customer, including those with no March orders, because the date filter is applied on the orders after the lookup, and not before the join.

Step 4: what to try

The plan suggests three directions, each testable by rerunning the plan:

1. Index the date range with the key: CREATE INDEX ON app_order (placed_at, customer_id) or a composite (customer_id, placed_at). With (customer_id, placed_at), each probe jumps straight to a customer's March orders and the filter disappears. With (placed_at) leading, the planner can start from orders in the range and join to customers, which reads 410,000 order rows once and probes customers 410,000 times by primary key, or hashes customers.

2. Reverse the join order. If the planner starts from app_order filtered by date, it reads the 410,000 qualifying rows and joins customers with a hash join. A selective date index makes that plan cheaper than the nested loop, and the planner will choose it once the index exists.

3. Check statistics. Run ANALYZE app_order and see whether the estimates change; if the estimate for the date range was far off, the planner might have chosen differently with correct numbers.

After adding CREATE INDEX app_order_placed_idx ON app_order (placed_at) INCLUDE (customer_id, total) the plan becomes an Index Only Scan on orders feeding a Hash Join to customers, and the time falls into the tens of milliseconds. That outcome is what you would verify by rerunning, and not by assuming.

The rules, distilled

1. Start at the deepest node and go up. The leaves read data, and every parent consumes what the children produce.

2. **Compare the two rows figures at every node.** Estimated is the first in the cost group, actual is in the time group. A difference of ten times or more is a lead.

3. Multiply by loops. Time and rows in a node are averages for one execution. 0.03 ms × 100,000 loops is three seconds.

4. Compute own time. Parent total minus child total, taking loops into account. A node with a high total and a child with nearly the same total is only waiting.

5. Read the filter lines. Filter plus a high Rows Removed by Filter means the node read data it then discarded.

6. Read the detail lines. Sort Method, Batches, Heap Fetches, Buffers: temp read explain memory and disk behavior.

7. Look at the first and last numbers of actual time. The first is the startup (time to the first row), and a large startup on a node under a Limit means the limit cannot stop early.

8. Stop at the first plausible cause. Change it and compare, rather than listing every oddity.

Common misreadings

Reading the cost as time. Cost units are not milliseconds. A cost of 48,210 does not mean 48 seconds.

Fixating on the node with the largest total. The top node always has the largest total, since it includes everything below it. The node to examine has the largest own time.

Ignoring loops. A node with actual time=0.02..0.03 looks free until loops=100000.

Trusting a plan from a different dataset. A plan on a dev database with 1,000 rows may show a sequential scan, which the planner prefers for small tables, and tell you nothing about production.

Treating one run as the truth. The first run may be cold; run twice and compare the second. For variable queries, use several parameter values.

**Chasing rows=0 estimates.** The planner rounds estimates and clamps at one; a displayed rows=0 is a small fraction, not a literal zero. The concern is the product with loops.

When a plan cannot be trusted as evidence

A plan is a poor guide when the data is not representative: a freshly restored copy has cold caches and no autovacuum history, a test database lacks the skew of production. It is poor when the statement run in the console differs from the one the application sends, which happens with prepared statements that use generic plans, different search_path settings and different parameter values. It is poor when the problem is waiting rather than working: lock waits, connection queueing, replication delay or a saturated disk will not appear as an expensive node, and a fast plan with a slow statement points to them. It is also poor when the plan changes between runs because of a threshold between two nearly equal costs, in which case the conclusion is that the planner is on the edge, and the fix is to make one choice clearly cheaper with an index or a statistic.

In these cases, widen the view. Capture the real plan with auto_explain, as in using auto_explain to catch slow queries, look at waits in pg_stat_activity and compare with comparing plans before and after a change.

What to do next

Pick your slowest query from pg_stat_statements, run it with EXPLAIN (ANALYZE, BUFFERS) on production-like data, and apply the eight rules. Write down the node you suspect before changing anything, then check whether the change moved the time where you predicted. The manual explains the output format in using EXPLAIN, and the planner's behavior in how the planner uses statistics.