When tuning PostgreSQL performance, most developers focus on indexes, joins, memory settings, and vacuum parameters. But hidden among the planner controls is a small yet influential GUC: enable_material.
Although rarely discussed, this parameter plays a subtle but critical role in how PostgreSQL executes nested loops, handles subqueries, buffers intermediate results, and reuses expensive computations.
What Is a Materialize Node?
A Materialize node is a buffering operator in a PostgreSQL execution plan. It temporarily stores the results of a subplan—usually in memory, spilling to disk if needed—so the executor can reuse the data without re-running the underlying computation.
Why Materialization Exists
Materialization exists in PostgreSQL to solve a combination of performance and correctness challenges that arise during query execution. One of the primary reasons is to avoid repeated computation: when a subplan needs to be scanned multiple times—such as the inner side of a nested loop—materializing its results once is significantly faster than re-running the underlying scan or function repeatedly.
Materialization also serves as a buffering layer to break pipeline dependencies between plan nodes that cannot stream tuples directly, enabling the executor to operate efficiently even when certain operations require all input tuples upfront. Another important role is stabilizing volatile functions. Additionally, materialization supports execution requirements such as sorted scans, backward scans, or operations that require full rewinding of results; in these cases, PostgreSQL must temporarily buffer the results in memory or a workfile to allow multiple passes over the data.
Because of these responsibilities, materialization functions both as a correctness mechanism—ensuring guarantees around volatile functions, ordering, and multi-pass requirements—and a performance optimization, reducing repeated work and improving join efficiency. While the planner will sometimes insert Materialize nodes purely to accelerate execution, in other situations, they are mandatory to maintain the logical correctness and stability of the query result.
What Does enable_material Control?
enable_material is a planner GUC:
SET enable_material = on;
It tells PostgreSQL whether the query planner should consider using Materialize nodes for optional optimizations.
When enable_material = ON (default)
PostgreSQL freely inserts Materialize nodes when it believes they improve performance.
Example use-cases:
- Speeding up nested loops
Materialize lets PostgreSQL store the inner side of a nested loop in memory so it doesn’t have to re-scan or re-compute it for every outer row. This can dramatically reduce repeated work and improve performance.
- Caching results of expensive joins
If a join produces a small intermediate result, Materialize can capture that once and reuse it instead of re-running the join logic repeatedly.
- Reducing repeated table scans
When the same table or subquery needs to be scanned multiple times, Materialize stores the output so PostgreSQL avoids hitting the table again and again.
- Buffering small lookup tables
If a small relation is used many times (e.g., reference tables or lookup sets), Materialize loads it into memory once, making repeated lookups faster.
When enable_material = OFF
The planner avoids adding Materialize nodes unless they are required to guarantee correctness.
Effects:
- Lower memory usage
Without materialization, PostgreSQL streams rows directly through the plan instead of storing them in memory, reducing temporary memory consumption.
- More streaming/pipelined plans
The planner is forced to pass rows from one node to the next without buffering. This makes the plan more “real-time” but can increase repeated work.
- Potentially slower nested loops
Since the inner side cannot be cached, PostgreSQL may need to re-execute expensive inner operations for each outer row.
- More repeated execution of expensive subplans
Subqueries, joins, or scans that would normally be cached must be re-run every time they are needed, which can increase total execution time.
This setting is mostly used for debugging, understanding planner behavior, comparing pipelined vs. buffered execution strategies
It is not recommended for production, unless you have a very specific reason.
Summary
enable_material is a small but important planner toggle that influences how PostgreSQL buffers intermediate results.
Enabling enable_material allows PostgreSQL to store and reuse intermediate results, which leads to faster nested loop joins, avoids repeated execution of expensive subplans, and produces more stable, predictable execution plans. Disabling it reduces memory usage by forcing PostgreSQL to stream data instead of buffering it, but this can severely hurt performance on many workloads—especially those involving nested loops—since the planner may repeatedly re-execute costly subqueries that would otherwise be materialized.
Under the hood, the GUC affects only the planner, modifying how MaterialPath objects are created and costed. The executor simply follows the plan tree it receives.