Spotting Row-Estimate Explosions in Nested Loops #

The most punishing single failure mode in PostgreSQL query plans is a nested loop chosen on a one-row estimate that turns out to match tens of thousands of rows. A nested loop is cheap when its outer side yields a handful of rows — the inner side runs a few times. But when the planner estimates one row and the reality is 50,000, that same nested loop executes its inner scan 50,000 times, and a query that should take milliseconds runs for minutes. The plan node looks innocuous; the loops= counter is where the damage hides.

This page teaches you to read that counter and trace the explosion to its statistical root cause. It belongs under Identifying Plan Bottlenecks; the general skill of comparing estimates to reality is covered in reading cost vs actual time in EXPLAIN ANALYZE.

Anatomy of the Explosion #

A nested loop’s true cost is not the inner node’s printed actual time — it is that time multiplied by loops. EXPLAIN ANALYZE reports the inner node’s timing per loop, so a modest-looking inner scan hides its total under a large loops count.

Nested loop row-estimate explosion A diagram contrasting the planner's belief with runtime reality. The planner estimates the outer side yields one row, so it expects the inner index scan to run once. At runtime the outer side yields fifty thousand rows, so the inner scan runs fifty thousand loops. The true cost equals inner per-loop time multiplied by loops. Planner believes Runtime reality Outer scan: est. rows = 1 (correlated predicate) Inner Index Scan expected loops = 1 "nested loop is cheapest" Outer scan: actual rows = 50000 estimate was wrong Inner Index Scan loops = 50000 true cost = per-loop time × 50000 The plan node looks cheap; loops is where the cost hides

Reading the Explosion in EXPLAIN ANALYZE #

Take a query joining orders to line items, filtered by two correlated order attributes:

EXPLAIN (ANALYZE)
SELECT o.order_id, li.sku, li.quantity
FROM orders o
JOIN line_items li ON li.order_id = o.order_id
WHERE o.channel = 'wholesale' AND o.priority = 'expedite';
Nested Loop  (cost=0.85..14.20 rows=1 width=32)
             (actual time=0.09..2140.6 rows=182000 loops=1)   -- ← est 1, actual 182000
  ->  Index Scan using idx_orders_channel_priority on orders o
        (cost=0.42..6.10 rows=1 width=8)
        (actual time=0.03..46.2 rows=50000 loops=1)           -- ← outer: est 1, actual 50000
        Index Cond: ((channel = 'wholesale') AND (priority = 'expedite'))
  ->  Index Scan using idx_line_items_order on line_items li
        (cost=0.43..8.05 rows=4 width=28)
        (actual time=0.01..0.03 rows=4 loops=50000)           -- ← inner ran 50000 loops
        Index Cond: (order_id = o.order_id)

The diagnosis is entirely in the numbers:

Had the planner known the outer side yields 50,000 rows, it would have built the line_items side into a hash table once and probed it — a hash join costing a fraction of 182,000 nested probes.

Root Causes of the One-Row Estimate #

Three related conditions produce the collapsed estimate:

Step-by-Step: Diagnose and Fix #

Step 1 — Find the estimate/actual gap #

Run EXPLAIN (ANALYZE) and scan for a node whose estimated rows is 1 (or single digits) while actual rows is large — especially when it feeds the outer side of a Nested Loop.

Step 2 — Quantify the true inner cost #

On the inner node, multiply per-loop actual time by loops. That product, not the printed per-loop figure, is the real cost. A loops=50000 with even 0.03 ms each is the bottleneck.

Step 3 — Identify the correlated columns #

Determine which predicates or join columns collapsed the estimate. Check whether two filtered columns are semantically linked, or whether a join key correlates with a filter.

Step 4 — Create extended statistics and re-analyze #

Teach the planner the correlation with a multivariate statistics object covering distinct-value combinations:

CREATE STATISTICS orders_channel_priority (ndistinct, dependencies)
  ON channel, priority FROM orders;
ANALYZE orders;

Now the planner estimates the joint selectivity from real data instead of multiplying, and the outer estimate rises toward 50,000 — which makes the nested loop look expensive and a hash join look cheap.

Step 5 — Apply a stopgap if the query is failing now #

If the query is timing out in production before you can ship statistics, force the alternative for the session:

SET LOCAL enable_nestloop = off;   -- stopgap only; forces a hash join for this session

EXPLAIN (ANALYZE)
SELECT o.order_id, li.sku, li.quantity
FROM orders o
JOIN line_items li ON li.order_id = o.order_id
WHERE o.channel = 'wholesale' AND o.priority = 'expedite';

This rescues the immediate query but disables nested loops for every join in the session, so it is a bridge to the statistics fix, not the fix itself.

Step 6 — Validate the natural plan #

Re-run EXPLAIN (ANALYZE) with enable_nestloop back on. With correct statistics the planner should estimate the true outer count and choose a Hash Join unprompted.

Before/After Plan Comparison #

-- BEFORE: est 1 row → nested loop, inner scan runs 50000 loops
Nested Loop  (actual time=0.09..2140.6 rows=182000 loops=1)  est rows=1
  ->  Index Scan on orders  (actual rows=50000)  est rows=1
  ->  Index Scan on line_items  (actual rows=4 loops=50000)   -- ← 50000 probes

-- AFTER: extended stats fix the estimate → hash join, one build
Hash Join  (actual time=52.1..238.7 rows=182000 loops=1)  est rows=50000
  Hash Cond: (li.order_id = o.order_id)
  ->  Index Scan on orders  (actual rows=50000)  est rows=50000  -- ← estimate now correct
  ->  Hash  ->  Seq Scan on line_items  (actual rows=750000 loops=1)  -- ← built once

The 50,000 inner probes collapse to a single hash build, and the join’s actual time falls from 2.1 seconds to a quarter-second.

Common Pitfalls #

Treating the symptom, not the cause. Leaving enable_nestloop = off in place “fixes” one query while silently forbidding nested loops everywhere, including the many queries where a nested loop is correct. Ship the extended statistics and revert the flag.

Missing the multi-column nature of the correlation. Creating statistics on the wrong pair of columns leaves the estimate collapsed. Confirm which columns actually co-vary — sometimes it is a filter column correlated with the join key, not two filter columns, that drives the miss. The underlying selectivity math is detailed in cost estimation models.

A LIMIT tricking the planner. With LIMIT, the planner may favor a nested loop expecting to stop early after finding a few rows. If the matching rows are sparse, it scans far more than it estimated. Watch for LIMIT queries whose nested-loop inner loops far exceed the limit.

Assuming a hash join is always the answer. Once statistics are correct the planner may still, legitimately, choose a nested loop for a genuinely small outer side — that is the right call. Force a hash join only when the estimate is wrong, not whenever you see a nested loop.

Frequently Asked Questions #

How do I spot a row-estimate explosion in a nested loop? Look for a nested loop whose outer side shows estimated rows near 1 but actual rows in the thousands, with an inner node showing a matching loops count. The planner chose the nested loop believing the inner side would run once or twice; the loops value reveals it ran tens of thousands of times. Multiply the inner node’s per-loop actual time by loops for the true cost.

What causes PostgreSQL to estimate one row when there are thousands? Usually correlated predicates or a join on non-independent columns. PostgreSQL multiplies the selectivities of separate conditions as if independent, and when they actually correlate the product collapses toward one row. Missing extended statistics leave the planner unable to know the columns move together.

Should I set enable_nestloop = off to fix the explosion? Only as a stopgap. Turning it off for the session forces a hash join and can rescue a query that is timing out now, but it treats the symptom. The durable fix is CREATE STATISTICS on the correlated columns followed by ANALYZE, so the planner estimates the real row count and chooses the right join on its own.

Related