Materialize in PostgreSQL EXPLAIN

What it does

Materialize reads all rows from its child the first time it’s asked, keeps them in memory (in a tuplestore), and on every later scan returns them from there. If they don’t fit in work_mem, the tuplestore moves to a temporary file.

It’s there to make rescans cheap. The usual case is the inner side of a Nested Loop: the inner side is scanned once per outer row, and re-running a filtered scan or a join each time would cost far more than replaying stored rows. It also appears under a Merge Join when the join needs to step back over inner rows with duplicate keys and the child can’t do that itself.

Unlike Memoize, it stores one result for all rescans. It’s used when the inner side doesn’t depend on the outer row.

When the planner picks it

• The inner side of a nested loop doesn’t use any value from the outer row, typically because the join condition isn’t an equality (a.x < b.y, BETWEEN, range overlaps) or there’s no condition at all (a cross join).
• Rescanning the child directly would cost more than storing it, for example a filtered scan or a subquery.
• enable_material (on by default) lets you switch it off to compare plans.

Reading its numbers

->  Materialize  (cost=0.29..8.72 rows=19 width=10) (actual time=0.000..0.001 rows=20.00 loops=2000)
      Storage: Memory  Maximum Storage: 17kB
      Buffers: shared hit=3
      ->  Index Scan using products_pkey on products p1  (… rows=20.00 loops=1)

• loops=2000: how many times it was scanned: once per outer row.
• rows=20.00: rows returned per scan (an average). Here every scan returned the same 20 rows.
• The child’s loops=1: the child ran once. Every other scan came from the stored copy, which is the point.
• actual time=0.000..0.001: per scan. Replaying 20 rows from memory takes a microsecond.
• Storage: Memory Maximum Storage: 17kB (PostgreSQL 18): where the stored rows lived and how much space they took. Storage: Disk means they didn’t fit in work_mem and went to a temporary file. Versions 14–17 don’t print this line.
• Buffers: the child’s page reads, which happen once.

When it’s a problem

Materialize itself is rarely slow. The cost is in the Nested Loop above it, which compares every outer row with every stored row. Look at the nested loop’s Join Filter and Rows Removed by Join Filter: that’s how many pairs were compared for nothing.

1. Turn the condition into something indexable. In the example below, orders are matched to price bands with total >= low AND total < high: 1,000,000 orders × 10 bands, 9 out of 10 comparisons discarded. Because the bands are regular, floor(total / 100) * 100 computes the band directly, and a single scan with a HashAggregate replaces the join (584 ms → 283 ms). For irregular ranges, an index on the inner table’s range column lets a nested loop look up matching rows instead of comparing all of them; range types with a GiST index are the general tool for overlap joins.
2. Check for a missing join condition. A Materialize under a nested loop with no Join Filter at all is a cross join. If you didn’t mean one, a condition is missing from the query.
3. Storage: Disk (on 18) means the stored rows didn’t fit in work_mem. Raise it for the query if the rescans are slow.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE products (
  id    int PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);
INSERT INTO products
SELECT i, 'Product ' || i, round((i % 500) + 0.99, 2)
FROM generate_series(1, 100000) AS i;
VACUUM ANALYZE products;

Pairs of products where one costs more than twice the other, among the first 20 and the first 2,000:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p1.id, p2.id
FROM products p1 JOIN products p2 ON p2.price > p1.price * 2
WHERE p1.id <= 20 AND p2.id <= 2000;

Nested Loop  (cost=0.58..727.51 rows=12242 width=8) (actual time=0.064..6.909 rows=38240.00 loops=1)
  Join Filter: (p2.price > (p1.price * '2'::numeric))
  Rows Removed by Join Filter: 1760
  Buffers: shared hit=5 read=18
  ->  Index Scan using products_pkey on products p2  (cost=0.29..76.12 rows=1933 width=10) (actual time=0.039..0.590 rows=2000.00 loops=1)
        Index Cond: (id <= 2000)
        Index Searches: 1
        Buffers: shared hit=2 read=18
  ->  Materialize  (cost=0.29..8.72 rows=19 width=10) (actual time=0.000..0.001 rows=20.00 loops=2000)
        Storage: Memory  Maximum Storage: 17kB
        Buffers: shared hit=3
        ->  Index Scan using products_pkey on products p1  (cost=0.29..8.62 rows=19 width=10) (actual time=0.004..0.005 rows=20.00 loops=1)
              Index Cond: (id <= 20)
              Index Searches: 1
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=118 read=5
Planning Time: 0.418 ms
Execution Time: 8.043 ms

The 20 cheap products were read once and replayed 2,000 times: 40,000 comparisons, 38,240 matches. PostgreSQL 17.11 chose the same plan and printed the same Materialize without the Storage line.

When the comparisons add up

Price bands:

CREATE TABLE price_bands (
  label text PRIMARY KEY,
  low   numeric NOT NULL,
  high  numeric NOT NULL
);
INSERT INTO price_bands
SELECT format('%s–%s', g * 100, (g + 1) * 100), g * 100, (g + 1) * 100
FROM generate_series(0, 9) AS g;
ANALYZE price_bands;

And 1,000,000 orders with totals from 0 to 999.99:

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  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;
VACUUM ANALYZE orders;

(Our test orders table also had a foreign key and two indexes; none is used here.) Counting orders per band:

EXPLAIN (ANALYZE, BUFFERS)
SELECT b.label, count(*)
FROM orders o
JOIN price_bands b ON o.total >= b.low AND o.total < b.high
GROUP BY b.label;

Finalize GroupAggregate  (cost=88733.62..88736.64 rows=10 width=17) (actual time=582.902..584.063 rows=10.00 loops=1)
  Group Key: b.label
  Buffers: shared hit=19 read=8334 written=129
  ->  Gather Merge  (cost=88733.62..88736.42 rows=24 width=17) (actual time=582.893..584.053 rows=30.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=19 read=8334 written=129
        ->  Sort  (cost=87733.60..87733.62 rows=10 width=17) (actual time=578.727..578.729 rows=10.00 loops=3)
              Sort Key: b.label
              Sort Method: quicksort  Memory: 25kB
              Buffers: shared hit=19 read=8334 written=129
              Worker 0:  Sort Method: quicksort  Memory: 25kB
              Worker 1:  Sort Method: quicksort  Memory: 25kB
              ->  Partial HashAggregate  (cost=87733.33..87733.43 rows=10 width=17) (actual time=578.652..578.654 rows=10.00 loops=3)
                    Group Key: b.label
                    Batches: 1  Memory Usage: 32kB
                    Buffers: shared hit=3 read=8334 written=129
                    Worker 0:  Batches: 1  Memory Usage: 32kB
                    Worker 1:  Batches: 1  Memory Usage: 32kB
                    ->  Nested Loop  (cost=0.00..85418.52 rows=462963 width=9) (actual time=0.349..528.382 rows=333333.33 loops=3)
                          Join Filter: ((o.total >= b.low) AND (o.total < b.high))
                          Rows Removed by Join Filter: 3000000
                          Buffers: shared hit=3 read=8334 written=129
                          ->  Parallel Seq Scan on orders o  (cost=0.00..12500.67 rows=416667 width=6) (actual time=0.247..34.491 rows=333333.33 loops=3)
                                Buffers: shared read=8334 written=129
                          ->  Materialize  (cost=0.00..1.15 rows=10 width=18) (actual time=0.000..0.000 rows=10.00 loops=1000000)
                                Storage: Memory  Maximum Storage: 17kB
                                Buffers: shared hit=3
                                ->  Seq Scan on price_bands b  (cost=0.00..1.10 rows=10 width=18) (actual time=0.056..0.057 rows=10.00 loops=3)
                                      Buffers: shared hit=3
Planning:
  Buffers: shared hit=249 read=13 written=13
Planning Time: 0.625 ms
Execution Time: 584.156 ms

loops=1000000 on the Materialize: one replay per order, summed over three processes. Each process removed 3,000,000 pairs. Computing the band instead:

EXPLAIN (ANALYZE, BUFFERS)
SELECT floor(total / 100) * 100 AS low, count(*)
FROM orders
GROUP BY 1 ORDER BY 1;

Sort  (cost=43452.51..43699.30 rows=98716 width=40) (actual time=282.013..282.015 rows=10.00 loops=1)
  Sort Key: ((floor((total / '100'::numeric)) * '100'::numeric))
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=132 read=8205 written=43
  ->  HashAggregate  (cost=30834.00..32561.53 rows=98716 width=40) (actual time=281.710..281.988 rows=10.00 loops=1)
        Group Key: (floor((total / '100'::numeric)) * '100'::numeric)
        Batches: 1  Memory Usage: 1057kB
        Buffers: shared hit=129 read=8205 written=43
        ->  Seq Scan on orders  (cost=0.00..25834.00 rows=1000000 width=32) (actual time=0.191..174.660 rows=1000000.00 loops=1)
              Buffers: shared hit=129 read=8205 written=43
Planning:
  Buffers: shared hit=12
Planning Time: 0.189 ms
Execution Time: 282.579 ms

Half the time, in one process instead of three.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts, so the nested loop doing the comparisons, rather than the Materialize under it, is what you look at first.