Nested Loop in PostgreSQL EXPLAIN

What it does

A Nested Loop takes one row from its first child (the outer side, drawn on top) and runs its second child (the inner side) to find the matching rows, then takes the next outer row and runs the inner side again. With 300 outer rows, the inner side runs 300 times; the plan shows that as loops=300.

That sounds slow, and it is when the inner side is a full table scan. But usually the inner side is an index lookup that uses the outer row’s value as its key (Index Cond: (id = o.customer_id)), so each loop costs a few page reads. For a handful of outer rows, nothing beats it: there’s no hash table to build and nothing to sort, and the first joined row comes out immediately.

It’s also the only join method that can handle any join condition: <, BETWEEN, LIKE, or no condition at all (a cross join). Hash and merge joins need an equality.

Variants: Nested Loop Left Join, Nested Loop Semi Join (for EXISTS and IN, stops at the first match), Nested Loop Anti Join (for NOT EXISTS).

When the planner picks it

• The outer side is small (or estimated to be small) and the inner side has an index on the join column.
• A LIMIT means only the first few joined rows are needed.
• The join condition isn’t an equality, so hash and merge joins can’t be used. The inner side is then often a Materialize node, or a Memoize node when the same keys repeat.

Reading its numbers

Nested Loop  (cost=0.71..1829.70 rows=273 width=28) (actual time=0.249..8.628 rows=300.00 loops=1)
  ->  Index Scan using orders_created_at_idx on orders o  (… rows=273 …) (actual … rows=300.00 loops=1)
  ->  Index Scan using customers_pkey on customers c  (cost=0.29..5.26 rows=1 width=18) (actual time=0.022..0.022 rows=1.00 loops=300)
        Index Cond: (id = o.customer_id)
        Index Searches: 300
        Buffers: shared hit=774 read=126

• Inner side loops=300: it ran once per outer row.
• Inner rows and actual time are per loop: 1 row and 0.022 ms each time. Multiply by loops for the total: 300 rows, about 6.6 ms. This is the most misread number in EXPLAIN; see loops and actual time.
• Inner Buffers and Index Searches are totals across all loops: 900 pages, 300 index descents.
• Estimated outer rows (rows=273) against actual (300.00): the number to check first. The planner chose a nested loop because it expected few outer rows; if the actual count is far higher, the plan was built on a wrong assumption.
• Join Filter and Rows Removed by Join Filter (not in this plan): join conditions checked for every outer–inner pair, rather than used to look rows up. A large Rows Removed by Join Filter means the loop compared a lot of pairs for nothing.

PostgreSQL 18 prints per-loop rows with two decimals, so an inner side that finds a match 95 times in 100 shows rows=0.95. Older versions round it to rows=1.

When it’s a problem

The pattern to look for: the outer side’s estimate is small (often rows=1), the actual is thousands, and the inner side shows the same huge number in loops. Each loop is cheap; tens of thousands of them aren’t. In the example below the planner expected 1 customer and got 26,279, so it ran 26,279 index lookups, visited 525,580 table pages one at a time, and spent 648 ms.

Fixes, roughly in order:

1. Give the planner conditions it can estimate. Functions and casts on columns hide them from the column statistics: date_trunc('year', created_at) = '2023-01-01' gets a default guess. The equivalent range, created_at >= '2023-01-01' AND created_at < '2024-01-01', uses the histogram. In the example, that rewrite alone fixed the estimate and the plan (648 ms → 239 ms).
2. Refresh statistics. Run ANALYZE after bulk changes. For skewed columns, raise the statistics target: ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000; ANALYZE t;.
3. Tell it about correlated columns. When two conditions are related (city and postcode, say), the planner multiplies their selectivities and underestimates. CREATE STATISTICS … (dependencies) on the pair fixes that.
4. Make each loop cheaper. If the nested loop is right but the inner side is a scan, add an index on the inner table’s join column.
5. As a test only, SET enable_nestloop = off in your session shows what the alternative plan costs. Don’t leave it on: it affects every join in the session.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE customers, orders;

A good nested loop

Refunded orders from one week, with the customer’s name:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded' AND o.created_at >= '2024-03-01' AND o.created_at < '2024-03-08';

Nested Loop  (cost=0.71..1829.70 rows=273 width=28) (actual time=0.249..8.628 rows=300.00 loops=1)
  Buffers: shared hit=889 read=126
  ->  Index Scan using orders_created_at_idx on orders o  (cost=0.42..393.75 rows=273 width=18) (actual time=0.053..1.776 rows=300.00 loops=1)
        Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-08 00:00:00+00'::timestamp with time zone))
        Filter: (status = 'refunded'::text)
        Rows Removed by Filter: 9780
        Index Searches: 1
        Buffers: shared hit=115
  ->  Index Scan using customers_pkey on customers c  (cost=0.29..5.26 rows=1 width=18) (actual time=0.022..0.022 rows=1.00 loops=300)
        Index Cond: (id = o.customer_id)
        Index Searches: 300
        Buffers: shared hit=774 read=126
Planning:
  Buffers: shared hit=132
Planning Time: 1.272 ms
Execution Time: 8.682 ms

The estimate (273) is close to the actual (300), and each of the 300 customer lookups costs three pages.

A nested loop built on a bad estimate

Customers who signed up in 2023, with a case-insensitive name match, and their order totals:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE date_trunc('year', c.created_at) = '2023-01-01'
  AND lower(c.name) LIKE 'customer%'
GROUP BY c.id;

GroupAggregate  (cost=1450.54..1450.70 rows=1 width=36) (actual time=561.170..645.856 rows=26279.00 loops=1)
  Group Key: c.id
  Buffers: shared hit=604690 read=95 written=87, temp read=1354 written=1361
  ->  Sort  (cost=1450.54..1450.59 rows=20 width=10) (actual time=561.153..593.904 rows=525580.00 loops=1)
        Sort Key: c.id
        Sort Method: external merge  Disk: 10832kB
        Buffers: shared hit=604690 read=95 written=87, temp read=1354 written=1361
        ->  Nested Loop  (cost=4.58..1450.11 rows=20 width=10) (actual time=0.035..497.819 rows=525580.00 loops=1)
              Buffers: shared hit=604690 read=95 written=87
              ->  Seq Scan on customers c  (cost=0.00..1368.00 rows=1 width=4) (actual time=0.024..26.108 rows=26279.00 loops=1)
                    Filter: ((lower(name) ~~ 'customer%'::text) AND (date_trunc('year'::text, created_at) = '2023-01-01 00:00:00+00'::timestamp with time zone))
                    Rows Removed by Filter: 23721
                    Buffers: shared hit=368
              ->  Bitmap Heap Scan on orders o  (cost=4.58..81.91 rows=20 width=10) (actual time=0.003..0.016 rows=20.00 loops=26279)
                    Recheck Cond: (c.id = customer_id)
                    Heap Blocks: exact=525580
                    Buffers: shared hit=604322 read=95 written=87
                    ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=20 width=0) (actual time=0.001..0.001 rows=20.00 loops=26279)
                          Index Cond: (customer_id = c.id)
                          Index Searches: 26279
                          Buffers: shared hit=78742 read=95 written=87
Planning:
  Buffers: shared hit=13 read=4 written=4
Planning Time: 0.362 ms
JIT:
  …
Execution Time: 648.246 ms

Both conditions wrap the column in a function, so the planner guessed each one keeps a small fraction of rows and multiplied the guesses: rows=1. In reality 26,279 customers matched. The inner side ran 26,279 times, and the Sort above, sized for 20 rows, spilled half a million rows to disk.

Written so the statistics apply:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.created_at >= '2023-01-01' AND c.created_at < '2024-01-01'
  AND c.name ILIKE 'customer%'
GROUP BY c.id;

Finalize HashAggregate  (cost=24494.28..24824.38 rows=26408 width=36) (actual time=227.463..237.468 rows=26279.00 loops=1)
  …
        ->  Hash Join  (cost=1573.10..15250.61 rows=220067 width=10) (actual time=42.020..134.442 rows=175193.33 loops=3)
              Hash Cond: (o.customer_id = c.id)
              ->  Parallel Seq Scan on orders o  (cost=0.00..12583.67 rows=416667 width=10) (actual time=0.007..22.132 rows=333333.33 loops=3)
              ->  Hash  (cost=1243.00..1243.00 rows=26408 width=4) (actual time=41.981..41.982 rows=26279.00 loops=3)
                    ->  Seq Scan on customers c  (cost=0.00..1243.00 rows=26408 width=4) (actual time=0.229..30.496 rows=26279.00 loops=3)
                          Filter: ((created_at >= '2023-01-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-01-01 00:00:00+00'::timestamp with time zone) AND (name ~~* 'customer%'::text))
                          Rows Removed by Filter: 23721
Execution Time: 238.745 ms

Estimated 26,408, actual 26,279. With the right numbers, the planner chose a parallel Hash Join and the query took 239 ms instead of 648 ms.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts, which is exactly what makes a plan like the second one stand out: rows=1 estimated, 26,279 actual.