Materialize

A Materialize reads its input once and keeps the rows, in memory or in a temp file when they outgrow work_mem, for the node above to read again. It appears most often as the inner side of a Nested Loop when there is no index to look rows up with. The loop rescans the inner input once per outer row, and a buffer rescans faster than a Seq Scan. JOIN commerce.stores s ON s.country = c.country, with 40 stores and no index on country, is the shape when the planner expects a handful of customers on the outer side.

Why it matters

Fine when the inner set is small, as 40 stores is, and the loops are few. It is the problem when the loops are many, because every outer row scans the whole kept set. The figure is rows out of the Materialize, reported per loop, times the loops. 40 rows × 412,000 customers is 16,480,000 comparisons for a join returning 412,000 rows. A set that outgrows work_mem goes to a temp file. Since PostgreSQL 14 Memoize caches inner results per key instead of the whole set.

What to do

1. Check whether the join condition can use an index. CREATE INDEX ON commerce.stores (country) turns the rescans into Index Scans.
2. Check the outer row estimate. If the loops ran 338× more than planned, ANALYZE the outer table and the planner moves to a Hash Join.
3. If the set is large and the loops few, it is doing its job. Leave it.

In the Plan tab

Materialize is drawn as its own node between the loop and its input, with ↻ ×N under its band for the loops it was rescanned in. A set that outgrew work_mem carries the spill label and the finding Materialize spilled to disk.