Parameter · Query Tuning / Planner Method Configuration
enable_material
Enables the planner's use of materialization.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
In a nested loop, the inner side may be read once for every outer row. A Materialize node saves the inner rows in memory (or a temp file) the first time, so later passes are cheap. This switch stops the planner from adding such nodes, except where they are needed for correctness.
For DBAs and operators
Materialize stores its input's output in a tuplestore limited by work_mem, spilling to a temp file beyond that. It helps when re-running the child would be expensive (a scan with a filter, a subquery) and is cheap when the child is small.
Measured on 18.6: a join with no usable join key, t JOIN u ON t.a + u.c > 100 WHERE u.id < 10, planned a Nested Loop with Materialize over the 9 rows of u, cost 32,281. With enable_material = off the planner flipped the loop and rescanned all of t for each of the 9 outer rows: cost 58,192.
The switch only affects optional materialization chosen for performance. Materialization that is required for correctness (for example for a merge join inner input whose scan cannot mark and restore its position) still happens. Not to be confused with CTE materialization (WITH … AS MATERIALIZED) or enable_memoize, which caches results per parameter value.
How to change it
Any user can change it for their session with SET.