Nested Loop

Description

Rescans the inner plan for outer rows and evaluates the join qualifications.

Core node tag

T_NestLoop

Structured EXPLAIN Node Type

Nested Loop

Inputs

Outer and inner child plans

Output

Joined tuples according to the selected join type

Executor initializer

ExecInitNestLoop

Memory mechanism

unclassified

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Nested Loop.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: none extracted from this node implementation.

Same-version manual discussion

Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.

In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes.

Examples from this manual build

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

Let's try joining two tables, using the columns we have been discussing:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
               Index Cond: (unique2 < 10)

Executor implementation notes

old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.

It scans the inner relation to join with current outer tuple.

If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.

NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.

NULL is also returned if there is no tuple from inner relation.

EXPLAIN identity in core source

case T_NestLoop:
			pname = sname = "Nested Loop";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans
• enable_nestloop

Documentation and source

• src/backend/commands/explain.c:1421
• src/backend/executor/execProcnode.c:297
• src/backend/executor/nodeNestloop.c
• src/include/nodes/plannodes.h
• PostgreSQL 18.6 · parallel-plans
• PostgreSQL 18.6 · using-explain

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• HashHash
• Hash JoinHashJoin
• Merge JoinMergeJoin