Batched nested-loop joins in Aurora DSQL EXPLAIN plans

When a query joins a small outer input to an inner input that Aurora DSQL can scan efficiently from storage, Aurora DSQL can choose a Nested Loop (Batched Join) plan. This join type reduces round trips between the compute and storage layers by grouping multiple outer rows together before probing the inner side.

A standard nested loop processes one outer row at a time and runs the inner scan again for every row. A batched nested-loop join collects a batch of outer rows, builds the inner scan work for the entire batch, and then joins the returned inner rows back to the matching outer rows.

How batched nested-loop joins work

1. Aurora DSQL reads a batch of rows from the outer side of the join.

2. If the inner side is an Index Scan or Index Only Scan with an Index Cond parameterized on the outer side, Aurora DSQL binds the condition for each outer row and combines the resulting index scan keys into one batched inner scan. Other join predicates don't drive the inner scan.

3. An Index Scan or Index Only Scan without an outer-parameterized Index Cond behaves like a Full Scan or Sequential Scan. Aurora DSQL has no parameterized index scan keys to combine and runs one inner scan per outer batch instead of one inner scan per outer row.

4. Aurora DSQL streams rows from the inner side as they arrive for the batch. It doesn't materialize the entire inner result for the batch before joining.

5. When the inner Index Scan or Index Only Scan has an outer-parameterized Index Cond, Aurora DSQL uses the Recheck Cond to match each returned inner row back to the applicable outer rows in the batch. Aurora DSQL then applies any remaining join predicates. Without an outer-parameterized Index Cond, Aurora DSQL applies the join predicates directly as it matches each streamed inner row against the outer batch.

6. For left and anti joins, Aurora DSQL tracks which outer rows matched and emits unmatched rows after the inner scan for the batch finishes.

This approach is most useful when the inner side is an index scan with a parameterized Index Cond, because the combined scan keys provide targeted storage access for the entire outer batch.

When Aurora DSQL uses this join

Aurora DSQL considers batched nested-loop joins when the inner side of the join is a physical scan node, meaning an Index Scan, Index Only Scan, Full Scan, or Sequential Scan, and batching is expected to cost less than running the inner scan for each outer row. In practice, the greatest benefit typically comes from a smaller outer input and an inner index scan with an Index Cond parameterized on the outer side.

If another plan shape is cheaper, such as a hash join or merge join, Aurora DSQL chooses that plan instead. To compare plan shapes during tuning, you can disable batched nested-loop joins for the current session:

SET dsql.enable_batched_nestloop = off;

How to read batched nested-loop join output

The following query uses the sample transaction and account tables from Reading Aurora DSQL EXPLAIN plans:

EXPLAIN
SELECT t.account_id, a.balance
FROM transaction t
LEFT JOIN account a
  ON t.account_id = a.customer_id
 AND a.balance > CASE
                    WHEN t.description LIKE 'fee%' THEN 0
                    ELSE 100
                  END
WHERE t.transaction_date >= '2025-01-01'
  AND (a.status = 'active' OR a.customer_id IS NULL)
ORDER BY t.account_id;

An EXPLAIN plan can include output similar to the following:

Sort
  Sort Key: t.account_id
  -> Nested Loop (Batched Join)
       Filter: (((status)::text = 'active'::text) OR (customer_id IS NULL))
       Join Type: Left
       Recheck Cond: (customer_id = t.account_id)
       Join Filter: (balance > CASE WHEN (t.description ~~ 'fee%'::text) THEN '0'::numeric ELSE '100'::numeric END)
       -> Full Scan (btree-table) on transaction t
            -> Storage Scan on transaction t
                 Filters: (transaction_date >= '2025-01-01 00:00:00'::timestamp without time zone)
                 -> B-Tree Scan on transaction t
       -> Index Only Scan using idx1 on account a
            Index Cond: (customer_id = t.account_id)

Nested Loop (Batched Join)

Shows that Aurora DSQL is batching outer rows before probing the inner side of the join.

Index Cond

Shows the predicate used to probe the inner side for the current batch. When the inner side is an Index Scan or Index Only Scan, this predicate often references columns from the outer side of the join.

Filter

Shows a condition applied to the result of the join. In this example, the WHERE (a.status = 'active' OR a.customer_id IS NULL) condition appears here. Under standard SQL left join semantics, this condition can't be pushed into the inner scan because doing so would change the query results. Aurora DSQL displays it as a filter on the join node. By contrast, the condition on t.transaction_date references only the outer table, so it appears under the outer physical scan.

Join Type

Shows the join semantics for the batched join, such as Left, Semi, or Anti.

Recheck Cond

Appears only when the inner side is an Index Scan or Index Only Scan with at least one Index Cond parameterized on the outer side of the join. For each returned inner row, Aurora DSQL runs the recheck against every outer row in the batch to determine which outer rows produced that probe and should join with that inner row.

Join Filter

Shows join predicates that Aurora DSQL evaluates after it has matched an inner row back to candidate outer rows in the batch. These predicates affect which row pairs join, but they don't drive the storage probe the way Index Cond does.

A preceding sort node

Can appear when the query needs ordered output. Batched nested-loop joins don't preserve the same output order guarantees as a standard nested loop, so Aurora DSQL can add an explicit sort before returning the final result set.