Nested Loop Join — outer row drives inner probe

Join shape controls

Outer rows (customers): 50000

The driving side: each of these rows triggers an inner probe.

Inner rows per probe (orders): 8

Average orders rows checked for each customer row.

Example query this animation executes

SELECT c.customer_id, o.order_id
FROM   customers c
JOIN   orders o ON o.customer_id = c.customer_id
WHERE  c.country = 'US';

This lesson isolates the Nested loop operator itself: one outer row is chosen, then the inner side is probed, and repeated. This is the exact background behavior behind EXPLAIN's Nested loop node.

What you'll see in the animation

Left side is the outer (driving) customers input. One highlighted customer means one loop iteration.
For each highlighted customer, the arrow moves to the orders side and shows the concrete rows checked in that probe.
The operator repeats this pattern until all outer rows are consumed.
Top-line cost is row-pair comparisons, which grows with outer_rows × inner_rows_per_probe (the operator's real background work).

0.0s 0.0s

Nested loop cost model

Outer rows ?Rows from the driving side of the join. The nested loop runs once per outer row.

—

Inner rows per probe ?Average rows checked on the inner side each time one outer row is processed.

—

Row-pair comparisons ?Approximate work for this operator: outer_rows × inner_rows_per_probe.

—

Complexity ?Nested loop cost grows multiplicatively with both inputs.

O(n · m)

Nested loop growth curve (log-log)

Learn more — why this operator can get expensive

The Nested loop operator itself is simple: pick one outer row and run an inner probe. The expensive part is repetition. If either side grows, total row-pair checks grow quickly.

If the inner probe is a selective index lookup, inner_rows_per_probe stays small and Nested loop can be very fast. If the inner side scans many rows per outer row, work explodes.