Materialize

Description

Stores its child output so the rows can be read again.

Core node tag

T_Material

Structured EXPLAIN Node Type

Materialize

Inputs

One child plan

Output

Materialized tuples

Executor initializer

ExecInitMaterial

Memory mechanism

tuplestore

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Materialize.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

This node creates a tuplestore with work_mem. The tuplestore can move stored tuples to temporary files; this does not make work_mem a cap on every allocation made by the node.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: none extracted from this node implementation.

Same-version manual discussion

Notice that here the planner has chosen to “ materialize ” the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.

Examples from this manual build

Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.

In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
               Index Cond: (unique2 < 10)

Executor implementation notes

As long as we are at the end of the data collected in the tuplestore, we collect one new row from the subplan on each call, and stash it aside in the tuplestore before returning it. The tuplestore is only read if we are asked to scan backwards, rescan, or mark/restore.

If first time through, and we need a tuplestore, initialize it.

Allocate a second read pointer to serve as the mark. We know it must have index 1, so needn't store that.

If we are not at the end of the tuplestore, or are going backwards, try to fetch a tuple from tuplestore.

When reversing direction at tuplestore EOF, the first gettupleslot call will fetch the last-added tuple; but we want to return the one before that, if possible. So do an extra fetch.

EXPLAIN identity in core source

case T_Material:
			pname = sname = "Materialize";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans
• enable_material
• work_mem

Documentation and source

• src/backend/commands/explain.c:1516
• src/backend/executor/execProcnode.c:315
• src/backend/executor/nodeMaterial.c
• src/include/nodes/plannodes.h
• src/backend/utils/sort/tuplestore.c
• PostgreSQL 18.6 · using-explain

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• MemoizeMemoize