Forcing a Nested Loop for Small Inner Tables #

The choice between a hash join and a nested loop hinges almost entirely on one number: how many rows the outer side of the join produces. When that number is small and the inner join key is indexed, a nested loop is often dramatically cheaper — each outer row triggers a single indexed lookup. But if the planner overestimates the outer row count, it reaches for a hash join and pays to build a hash table that a tiny outer input never justified. This page shows how to recognize that misjudgment and correct it at the source rather than bludgeoning the planner into submission.

It sits under Merge Join vs Nested Loop; for the opposite case — when a hash join is genuinely the right call — see when the optimizer chooses a hash join.

Why Small Outer Cardinality Favors a Nested Loop #

A nested loop scans the outer input once and, for each outer row, probes the inner relation. If the inner side is an Index Scan on the join key, each probe is a cheap B-tree descent. Total cost is roughly outer_rows × cost_per_index_probe. When outer_rows is 5, that is five index lookups — trivial.

A hash join, by contrast, must first read one entire input and build a hash table over it before it can probe. That build has a fixed floor cost regardless of how selective the other side turns out to be. If the outer input yields only five rows, the hash build was almost entirely wasted.

Nested loop versus hash join cost as outer row count grows A schematic cost chart with outer row count on the horizontal axis and cost on the vertical axis. The nested loop line starts near zero and rises linearly with outer rows. The hash join line starts at a high fixed build cost and rises slowly. They cross at a threshold: below it the nested loop is cheaper, above it the hash join wins. An overestimated outer count pushes the planner past the crossover incorrectly. cost outer rows → nested loop hash join build crossover small outer: nested loop wins Overestimated outer count pushes the planner right of the crossover by mistake

The crossover point is where the nested loop’s linearly growing cost overtakes the hash join’s near-flat cost. The planner estimates where you sit on that axis. Get the estimate wrong on the high side, and it places you past the crossover when you are really far to the left of it. The full cost arithmetic behind both curves is in cost estimation models.

Diagnosing the Overestimate #

Take a query that joins a filtered set of orders to their customers:

EXPLAIN (ANALYZE)
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'DISPUTED' AND o.region = 'APAC';
Hash Join  (cost=680.00..14250.00 rows=48000 width=40)
           (actual time=4.2..5.1 rows=6 loops=1)     -- ← estimate 48000, actual 6
  Hash Cond: (o.customer_id = c.customer_id)
  ->  Seq Scan on orders o  (cost=0.00..12000.00 rows=48000 width=8)
                            (actual time=0.9..4.0 rows=6 loops=1)
        Filter: ((status = 'DISPUTED') AND (region = 'APAC'))
        Rows Removed by Filter: 499994
  ->  Hash  (cost=520.00..520.00 rows=12000 width=36)   -- ← built over 12000 customers
        ->  Seq Scan on customers c  (actual rows=12000 loops=1)

The smoking gun is rows=48000 estimated versus rows=6 actual on the outer Seq Scan. The planner believed two independent selectivities — status = 'DISPUTED' and region = 'APAC' — combined to leave tens of thousands of rows. In reality the two predicates are correlated (disputed orders cluster in a few regions), so only six rows survive. Believing 48,000 rows, the planner built a hash table over 12,000 customers. For six outer rows, six indexed lookups would have been far cheaper.

Step-by-Step: Fix Statistics First, Force Nothing #

Step 1 — Compare estimated vs actual on the outer input #

Run EXPLAIN (ANALYZE) and read the outer scan of the join. A large gap between estimated rows and actual rows is the signal that the join choice rests on a bad number.

Step 2 — Treat statistics as the root cause #

Do not reach for a planner flag yet. An overestimate that large almost always comes from correlated predicates or stale statistics, both of which are fixable at the source so every query benefits.

Step 3 — Refresh and extend statistics #

For a single stale column, ANALYZE is enough. For correlated columns like status and region, create multivariate extended statistics so the planner stops multiplying the two selectivities as if independent:

CREATE STATISTICS orders_status_region (dependencies)
  ON status, region FROM orders;
ANALYZE orders;

With the dependency captured, the planner’s estimate for the combined predicate drops toward the true handful of rows.

Step 4 — Guarantee a cheap inner probe #

A nested loop is only cheap if the inner side is an indexed lookup. Confirm the join key is indexed:

CREATE INDEX CONCURRENTLY idx_customers_customer_id
  ON customers (customer_id);

Without this index the inner side becomes a Seq Scan per outer row — the catastrophic nested loop this whole page warns against.

Step 5 — Confirm the alternative with a diagnostic toggle #

Only to measure the counterfactual, disable hash joins for the session and re-time:

SET LOCAL enable_hashjoin = off;   -- diagnostic only, never leave this in production

EXPLAIN (ANALYZE)
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'DISPUTED' AND o.region = 'APAC';

If the nested-loop plan’s actual time is clearly lower, you have confirmed the intended plan. Then reset the flag and rely on the corrected statistics to produce it naturally.

Step 6 — Validate the natural plan #

Re-run EXPLAIN (ANALYZE) with hash joins re-enabled. The planner should now estimate a small outer count and choose the nested loop on its own — the durable outcome.

Before/After Plan Comparison #

-- BEFORE: overestimate → hash join builds over 12000 customers for 6 outer rows
Hash Join  (actual time=4.2..5.1 rows=6 loops=1)   estimate rows=48000
  ->  Seq Scan on orders  (actual rows=6)  Rows Removed by Filter: 499994
  ->  Hash  ->  Seq Scan on customers  (actual rows=12000)

-- AFTER: extended stats fix the estimate → indexed nested loop, no hash build
Nested Loop  (actual time=0.9..1.0 rows=6 loops=1)  estimate rows=6
  ->  Seq Scan on orders  (actual rows=6)  Rows Removed by Filter: 499994
  ->  Index Scan using idx_customers_customer_id on customers
        Index Cond: (customer_id = o.customer_id)   -- ← 6 cheap probes, loops=6

The hash build over 12,000 customers is replaced by six Index Cond probes, and the join’s actual time drops accordingly.

Common Pitfalls #

Forcing enable_hashjoin = off in production. The flag applies to every join in the session, so one query’s fix silently degrades others that legitimately need hash joins. Use it only to measure, then fix the statistics.

A nested loop with no inner index. If the inner join key is not indexed, each outer row triggers a full Seq Scan, and a nested loop over even a modest outer count becomes catastrophic — the exact opposite of what you want. Always confirm the inner Index Scan before preferring a nested loop.

Ignoring LATERAL joins. A LATERAL subquery is evaluated once per outer row and is effectively a nested loop by construction. If its inner scan is not indexed on the correlation key, the same per-row cost explosion applies; index the correlated column just as you would a plain join key.

Fixing one query instead of the estimate. Rewriting a single query to hint a nested loop leaves the underlying correlated-column misestimate in place, so the next query hits the same wall. Extended statistics fix the estimate for the whole workload.

Frequently Asked Questions #

When is a nested loop faster than a hash join? A nested loop wins when the outer side produces few rows and the inner join key is indexed, so each outer row costs one cheap index lookup. A hash join must first build a hash table over an entire input, which is wasted work if the outer side has only a handful of rows. For small outer cardinalities, loops × cheap_lookup beats the hash build.

Should I set enable_hashjoin = off in production? No. enable_hashjoin = off is a diagnostic to confirm the nested-loop alternative is genuinely cheaper, not a production fix. It disables hash joins for every join in the session and can wreck unrelated queries. Fix the root cause by correcting the statistics behind the outer overestimate and adding the inner index the nested loop needs.

Why did the planner pick a hash join when a nested loop is faster? Because it overestimated the outer row count. If it believes the outer will emit tens of thousands of rows, per-row index lookups look expensive and a single hash build looks cheaper. When the outer actually emits a few rows, that estimate was wrong, and refreshing statistics — especially extended statistics for correlated predicates — usually flips the plan back to a nested loop.

Related