Parameter · Query Tuning / Planner Method Configuration
enable_nestloop
Enables the planner's use of nested-loop join plans.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
A nested loop takes each row from one side and looks for matches on the other side, usually with an index. It is unbeatable when the outer side has only a few rows, and terrible when the planner wrongly thinks it does. This switch can stop the planner from using it.
For DBAs and operators
Cost is roughly outer rows × cost of one inner lookup. With an index on the inner side and a handful of outer rows it is the cheapest join; it is also the only method for non-equality conditions (t.b < u.c) without a hash or merge key.
Measured on 18.6: u JOIN t ON t.id = u.tid WHERE u.id < 10 (8 outer rows) planned a Nested Loop with an inner index scan at cost 76. With enable_nestloop = off: a Hash Join (under Gather) reading all 200,000 rows of t, cost 4,900.
The classic support case: the planner estimates 1 outer row, there are really 100,000, and a nested loop runs for hours. SET enable_nestloop = off in the session is a useful diagnostic and an occasional per-query workaround (for example in a function with SET enable_nestloop = off attached), but the real fix is the estimate: ANALYZE, a higher statistics target, extended statistics (CREATE STATISTICS) for correlated columns. Setting it off server-wide breaks every query that needs a nested loop to be fast.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_hashjoin enable_mergejoin enable_memoize enable_material default_statistics_target