Parameterized Inner Index Scans and Loops #

The inner index scan of a nested loop reports actual time=0.021..0.024 rows=3 loops=184000. It looks like the fastest node in the plan. It is actually responsible for 4.4 seconds of a 5-second query, because every number on that line is an average per loop, and the loop ran 184,000 times.

Nested loops are the most common join in OLTP plans and the easiest to misread. When the planner chooses them at all is covered in merge join vs nested loop; this page covers reading and tuning the parameterized inner side.

The Algorithmic Condition #

A nested loop reads one row from its outer input, then executes its inner input for that row, then reads the next outer row. When the inner input is an index scan whose condition refers to a column of the current outer row, it is a parameterized path: the outer value is passed in as a parameter, and the plan shows it in the inner node’s Index Cond, such as (customer_id = o.customer_id).

The planner prefers this shape when:

The cost is roughly outer rows × (index descent + heap fetches per match). Nothing is built in memory and nothing spills, which is why nested loops are ideal for small outer sets and catastrophic for large ones. The same parameterization appears under LATERAL subqueries, correlated EXISTS, and index-backed anti-joins, and can be wrapped in a Memoize cache when outer values repeat.

Parameterized paths can also appear below other nodes: the planner may push the outer value through a join or an append into several index scans, producing Index Cond lines that reference relations outside their own subtree. Those are still per-loop executions and still need multiplying.

One loop, repeated for every outer row Four steps repeated per outer row. Read the next outer row. Pass its customer_id as a parameter. Descend the index with that value and fetch matching heap rows. Emit the joined rows and return for the next outer row. next outer row orders o bind parameter o.customer_id inner index scan descent + heap fetches emit matches then loop again per-loop figures on the inner node must be multiplied by loops

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= now() - interval '2 days';
Nested Loop  (actual time=0.06..5021.8 rows=184002 loops=1)
  ->  Index Scan using orders_placed_at_idx on orders o
        (cost=0.44..8120.10 rows=21040 width=16) (actual time=0.03..402.6 rows=184002 loops=1)
        Index Cond: (placed_at >= (now() - '2 days'::interval))
  ->  Index Scan using customers_pkey on customers c
        (cost=0.43..0.46 rows=1 width=40) (actual time=0.021..0.024 rows=1 loops=184002)
        Index Cond: (id = o.customer_id)                   -- parameterized by the outer row
        Buffers: shared hit=412008 read=140120
Execution Time: 5104.6 ms
-- inner total time ≈ 0.024 ms × 184002 loops ≈ 4.4 s
-- outer estimate 21040 vs actual 184002: the planner expected 21k loops, not 184k

The arithmetic:

Per-loop numbers and their totals Four fields on the inner index scan are annotated. Actual time of 0.024 milliseconds is per loop and totals 4.4 seconds. Rows equals 1 per loop, 184 thousand total. Loops of 184 thousand is the multiplier. Buffers are already cumulative across loops. actual time=0.021..0.024 per loop → ≈ 4.4 s total rows=1 per loop → 184,002 total loops=184002 the multiplier (estimate was 21,040) Buffers: shared hit=412008 read=140120 already totals across loops

Step-by-Step Resolution #

  1. Multiply before judging. For every inner node with loops > 1, compute actual total time × loops. Rank nodes by that total.

  2. Compare actual loops with the outer node’s estimate. A large gap means the choice of nested loop rested on an underestimate — the fix is upstream, in the outer node’s statistics; see spotting row estimate explosions.

  3. Check per-loop buffers. Divide inner Buffers by loops. Two or three per loop is an efficient probe; tens per loop means many heap fetches per key or a poorly correlated index.

  4. Make each probe cheaper when the loop count is legitimately high: an index-only scan avoids heap fetches entirely.

    CREATE INDEX CONCURRENTLY customers_id_email_idx ON customers (id) INCLUDE (email);
VACUUM (ANALYZE) customers;   -- keeps the visibility map current for index-only scans

  5. Or let the planner choose a set-based join once estimates are right, and confirm the alternative:

    BEGIN;
SET LOCAL enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) <query>;
ROLLBACK;

  6. Check for repeated outer keys. If many outer rows share the same customer_id, a Memoize node or pre-aggregation of the outer side cuts the loop count.

Cheap per loop, expensive in total The same 0.024 millisecond probe costs 0.5 seconds at 21 thousand loops, the planner's estimate; 4.4 seconds at 184 thousand loops, the actual count; and 24 seconds at one million loops. 21,040 loops (estimate) 0.5 s 184,002 loops (actual) 4.4 s 1,000,000 loops 24 s nested loop cost is linear in the outer row count

Before and After #

-- BEFORE: heap fetch per probe
Index Scan using customers_pkey  (actual time=0.021..0.024 rows=1 loops=184002)   Buffers: read=140120   Execution Time: 5104.6 ms

-- AFTER: covering index, visibility map current
Index Only Scan using customers_id_email_idx  (actual time=0.006..0.007 rows=1 loops=184002)   Heap Fetches: 0   Execution Time: 1702.3 ms

Parameterized paths that are not joins #

The same per-loop reading applies wherever a node executes more than once. A correlated SubPlan in a SELECT list shows loops equal to the number of rows it was evaluated for. The inner side of a LATERAL join shows loops per outer row. A Memoize node’s child shows loops equal to cache misses rather than outer rows, which is exactly how you tell whether the cache is working. And under Gather, loops counts parallel participants, not repetitions — a scan with loops=5 under a four-worker Gather ran once in each process. Reading loops in its context is the single most important habit for interpreting any EXPLAIN ANALYZE output, and the underlying arithmetic is covered in reading cost vs actual time in EXPLAIN ANALYZE.

Common Pitfalls #

Ranking nodes by per-loop time. The inner probe looks negligible. Diagnostic signal: the real bottleneck hidden under a tiny actual time. Fix: multiply by loops first.

Multiplying buffers by loops. Buffer counts are already totals. Diagnostic signal: I/O figures that exceed the table size several times over. Fix: divide by loops for per-probe cost; never multiply.

Forcing hash joins globally. enable_nestloop = off harms the many plans where nested loops are right. Diagnostic signal: widespread regressions. Fix: correct the outer estimate or scope the setting to one transaction.

Missing the index on the join key. Without it, the inner side becomes a sequential scan per loop. Diagnostic signal: Seq Scan with loops in the thousands. Fix: index the inner join column.

Frequently Asked Questions #

What does loops mean on an inner index scan? #

It is the number of times the node was executed — once per outer row in a nested loop. The node’s actual time and rows are averages per execution, so multiply them by loops to get totals.

Are Buffers in EXPLAIN per loop or total? #

Total. Buffer counts are accumulated across all executions of the node, so divide by loops to see the I/O cost of a single probe.

When is a nested loop with many loops acceptable? #

When each probe is a short index descent with few buffers — ideally an index-only scan — and the total inner time is small relative to the query. It becomes the wrong plan when the outer row count was badly underestimated and a set-based join would process the rows in one pass.

Up: Merge Join vs Nested Loop