Reading Postgres EXPLAIN ANALYZE Correctly: the 'loops' Multiplier Everyone Misses

You run EXPLAIN ANALYZE on the slow query, scan down the plan, and one node jumps out at 0.070 milliseconds. Fast. You move on, spend an hour tuning a different node, and the query is still slow. The node you dismissed was actually the problem. It ran 0.070 milliseconds, one hundred and fifty times, and you never multiplied.

This is the most expensive misreading in database performance work, and it is entirely avoidable. Postgres is not hiding the cost from you. It prints it in plain sight, on the same line, in a field called loops. The trouble is that under time pressure, staring at a dense, deeply nested plan at 1am, the eye slides right past it.

This article is about reading the plan the way it is actually written, so the expensive node stops hiding in a small number.

The line everyone misreads

Here is a fragment of a real-shaped plan. Look at the inner node of the nested loop:

Nested Loop  (cost=0.42..1250.30 rows=150 width=64)
             (actual time=0.030..11.240 rows=150 loops=1)
  ->  Seq Scan on orders o  (actual time=0.010..0.180 rows=150 loops=1)
  ->  Index Scan using idx_items_order on items i
        (actual time=0.005..0.070 rows=8 loops=150)

The Index Scan reads actual time=0.005..0.070. If you stop there you conclude it takes 0.070 ms and is irrelevant. But look at the end of the same line: loops=150. That node executed once per row produced by the outer Seq Scan, one hundred and fifty times. Its real contribution is roughly 0.070 ms x 150 = 10.5 ms, which is essentially the entire 11.24 ms the whole query took. The "fast" node is the query's cost.

The same multiplier applies to rows. The Index Scan shows rows=8, but that is 8 rows per loop. It actually produced about 8 x 150 = 1,200 rows in total. Getting this wrong throws off every downstream estimate you make by eye.

Why this happens: the inner node runs once per outer row

A nested loop join works exactly as its name says. For every row the outer side produces, it executes the inner side once to find matching rows. So if the outer scan yields 150 rows, the inner node runs 150 times, and Postgres reports the inner node's timing as an average of a single run. This is genuinely the more useful number for understanding the node in isolation, but it means you cannot compare it to a loops=1 node without doing the multiplication first.

This is also why nested loops are wonderful for tiny outer sets and catastrophic for large ones. The cost scales with the number of loops. A nested loop over 15 outer rows is nothing; the same plan over 1.5 million outer rows, each triggering an index probe, is a query that never finishes. Which plan the planner picks depends heavily on its estimate of how many outer rows there will be, and that is the next thing to check.

The second trap: estimated rows versus actual rows

Every node prints two row counts. The rows= inside the cost=... parenthesis is the planner's estimate; the rows= inside the actual time=... parenthesis is what really happened. When these disagree by a large factor, the planner made its decision on bad information.

->  Bitmap Heap Scan on events e
      (cost=... rows=10 ...)            <- planner expected 10
      (actual time=... rows=10841 ...)  <- reality: 10,841

A planner that expects 10 rows will happily choose a nested loop, because 10 loops is cheap. When 10,841 rows actually arrive, that same plan runs the inner side 10,841 times and the query falls off a cliff. The fix is usually not to force a different plan; it is to give the planner better statistics so it stops guessing wrong:

ANALYZE events;
-- then re-run EXPLAIN ANALYZE and watch the estimate move toward reality

If the estimate is still far off after ANALYZE, the column may need more granular statistics (raising the statistics target) or the predicate may be correlated in a way the planner cannot model. But start with fresh statistics, because stale ones are the common case, especially on tables that just received a big bulk load. Passive autovacuum makes this worse, which I dig into in Postgres table bloat: why the default autovacuum settings are too passive.

The third signal: Rows Removed by Filter on a Seq Scan

When a plan node reads rows and then discards those that fail a condition, Postgres reports Rows Removed by Filter. On a sequential scan of a large table, this number is a direct measure of wasted work:

Seq Scan on invoices  (actual time=0.020..48.900 rows=312 loops=1)
  Filter: (status = 'overdue')
  Rows Removed by Filter: 498210

The scan read nearly half a million rows to return 312. That ratio, more than about half the scanned rows removed, is one of the clearest "you are missing an index" signals in the entire plan. An index on status (or a partial index WHERE status = 'overdue') lets Postgres jump straight to the 312 matching rows instead of reading and rejecting half a million. This is the reading skill that pairs with knowing which indexes to build, which I cover for the MariaDB side in a MariaDB slow query log playbook, and it is the same instinct that catches the ORM N+1 query problem before it ships.

A method for reading any plan under pressure

1. Find the node with the largest real total time. Multiply actual time end value by loops for every candidate, not just the ones with a big raw number. The villain often has a small per-loop time and a large loop count.
2. Check estimated vs actual rows on that node and its parents. A big gap explains a bad plan choice and points you at ANALYZE or better statistics.
3. Scan for Rows Removed by Filter and large Seq Scans. High removal ratios mean a missing index.
4. Only then decide what to change. Add the index, refresh statistics, or rewrite the query, based on which of the three the plan actually shows.

What people get wrong

Comparing per-loop times across nodes directly. A loops=1 node at 5 ms and a loops=200 node at 0.1 ms are not "5 ms vs 0.1 ms." They are 5 ms vs 20 ms. Normalise to totals before you compare anything, or you will optimise the wrong node every time.

Trusting EXPLAIN without ANALYZE. Plain EXPLAIN shows only the planner's estimates, never what actually happened. The entire estimated-versus-actual insight, the most valuable thing in the plan, requires ANALYZE, which really runs the query. Use it (on a read, or inside a transaction you roll back for writes).

Forcing a plan with enable_seqscan = off and calling it fixed. Disabling a scan type to bully the planner into your preferred plan hides the real problem, which is usually a bad row estimate. The moment data changes, your forced plan becomes the wrong one and you have no statistics telling you so. Fix the estimate; do not overrule the planner blind.

When it is still broken

• The plan is 30 nodes deep and unreadable as text. Paste it into a visual plan viewer such as the one linked from the official docs; the tree layout makes the expensive branch obvious in a way raw text cannot at that depth.
• Estimates are right but it is still slow. Then the plan is genuinely the best available and the work is real. Look at whether the query needs to touch that much data at all: a better index, a materialised summary, or a narrower query is the lever, not the planner.
• It is fast in EXPLAIN ANALYZE but slow in the app. The difference is usually not the plan; it is connection overhead, a cold cache on the first run, or the app fetching far more rows than it uses. Measure the app path separately before you keep tuning the plan.

For the canonical field reference, the PostgreSQL documentation's Using EXPLAIN chapter is worth one careful read. Everything above is just the habit of never trusting a plan node's cost until you have multiplied it by its loops.