The last of the three join-strategy toggles, and in some ways the most important, because the nested loop is both PostgreSQL’s simplest join and the source of its single most notorious performance disaster. The three algorithms were introduced in enable_hashjoin; this closes the set. Default on, context user, same family framing as enable_async_append: a diagnostic instrument, not a tuning knob.
The simplest join, and the only universal one
A nested loop join is exactly what it sounds like: for each row in the outer relation, scan the inner relation for rows that match the join condition. Two loops, one inside the other. In the naive form it’s O(outer × inner) — for every one of the outer rows you do a full pass over the inner side — which is ruinous when both sides are large. But it has one property no other join has, and that property is why it can never be fully switched off: it is the only join method that works with any join condition at all.
Hash join and merge join both require an equality condition; they are built around matching equal values. A nested loop doesn’t care what the condition is — it just evaluates the predicate for each pair of rows, so it handles inequality joins (a.x < b.y), range conditions, and cross joins that the other two literally cannot execute. This is why enable_nestloop = off discourages rather than forbids: it adds disable_cost to nested-loop paths, but if a query has no equality join condition, the nested loop is the only physical option and you’ll get one regardless. As Tom Lane put it on the mailing list about exactly this situation, when a user was baffled that nested loops persisted after disabling them: “the planner has no other choice.”
The nested loop also has a genuinely good side that its reputation obscures. When the outer relation is small and the inner relation has an index on the join key, the inner “scan” isn’t a scan at all — it’s an index lookup, and the loop does a handful of cheap indexed probes. This is the workhorse join of normalized OLTP: fetch a few rows from one table, look up their matches in another by indexed foreign key, done. In that shape the nested loop is not just acceptable but optimal, beating the setup cost of building a hash table or sorting for a merge. The problem is never the nested loop as such; it’s the nested loop applied to the wrong row counts.
The disaster: a nested loop on a bad estimate
Here is the most common serious performance failure in all of PostgreSQL, and it is worth recognizing on sight. The planner estimates that the outer side of a nested loop will return a small number of rows — say five. Five iterations of an indexed inner lookup is cheap, so the nested loop looks like the best plan, and the planner picks it. But the estimate was wrong: the outer side actually returns five hundred thousand rows. Now the “cheap” inner lookup runs five hundred thousand times, and a plan that was costed as nearly instant takes minutes or hours. The join is where all the time goes, but the join is not the cause — the cause is a row misestimate that happened at a scan below it.
This is the single most valuable thing to internalize about join plans: a catastrophic nested loop is almost always a symptom of a bad cardinality estimate, not a bad choice of join algorithm. The EXPLAIN (ANALYZE) signature is unmistakable once you know it. Look at the inner side of the Nested Loop and compare its estimated rows to loops= — a loops= value in the hundreds of thousands, or an inner node whose actual rows dwarfs its estimate, is the tell. The outer node will show a small estimated row count and a huge actual count, and the ratio between them is the size of the planner’s mistake.
Symptoms that warrant flipping it
Reach for SET enable_nestloop = off when a query is far slower than expected and its EXPLAIN (ANALYZE) shows a Nested Loop whose inner side ran a huge number of loops, with a large gap between estimated and actual rows on the outer side. Disable nested loops for the session, re-run, and the planner will fall back to a hash or merge join that scans each side once instead of looping; if that plan is dramatically faster, you’ve confirmed the nested loop was the wrong call for the true row counts.
But — and this is the whole discipline of the family in one instruction — the fix is almost never to leave enable_nestloop = off. The switch has told you the plan was wrong; it has not told you why, and the why is a cardinality misestimate. Fix that: ANALYZE the tables, raise default_statistics_target or the per-column statistics on the columns being misestimated, or use extended statistics (CREATE STATISTICS) if the underestimate comes from correlated columns the planner assumes are independent. When the estimate improves, the planner stops choosing the disastrous nested loop on its own, which is the durable fix. Disabling nested loops globally is actively dangerous: it distorts every plan in the database, forces the planner away from the correct OLTP nested-loop-with-index that most of your queries depend on, and — because the switch only adds cost rather than truly forbidding the node — can make things worse on queries whose only viable plan is a nested loop, by warping the cost comparisons around it. Diagnose with the switch, then fix the statistics and set it back.
That completes the three join strategies. If you’ve read all three, the shape of join tuning should now be clear: the planner is choosing among nested loop, hash, and merge on the basis of cost estimates, those estimates come from statistics, and when it picks wrong the answer is almost always to correct what it knows about your data — not to take one of its three tools away.