> **Materialize** > <table> > <tr> > <td width="25%"><img src="assets/ex_materialize.png"></td> > <td>Stores the intermediate results of a subplan in memory (or on disk) so they can be re-scanned efficiently. This is commonly used in Nested Loop joins to prevent re-executing the inner subplan for every row of the outer table.</td> > </tr> > </table> > > ```sql > -- Forcing a Merge Join with non-unique keys > SET enable_hashjoin = off; > SET enable_memoize = off; > > EXPLAIN (COSTS, VERBOSE) > SELECT * FROM animals a1 > JOIN animals a2 ON a1.species_id = a2.species_id; > ``` > >  > > <!-- literal-explain-plan > Captured EXPLAIN provenance for the adjacent reader-facing visual plan. > Canonical capture metadata lives in scratch/actual_operation_plans.json. > > Merge Join (cost=0.57..300996.07 rows=20000001 width=54) > Output: a1.id, a1.name, a1.species_id, a1.created_at, a2.id, a2.name, a2.species_id, a2.created_at > Merge Cond: (a1.species_id = a2.species_id) > -> Index Scan using idx_animals_species_id on public.animals a1 (cost=0.29..485.53 rows=10000 width=27) > Output: a1.id, a1.name, a1.species_id, a1.created_at > -> Materialize (cost=0.29..510.53 rows=10000 width=27) > Output: a2.id, a2.name, a2.species_id, a2.created_at > -> Index Scan using idx_animals_species_id on public.animals a2 (cost=0.29..485.53 rows=10000 width=27) > Output: a2.id, a2.name, a2.species_id, a2.created_at > --> > > > <!-- > Raw-capture provenance — separate run. > SQL, setup, dataset, settings, and scope: artifacts/chapter4_capture_matrix.json. > Target: PostgreSQL 18.x companion fixture. Cache state: uncontrolled. > Boundary: pg_wait_tracer backend execution root; client states are included when the chart shows them. > Fidelity: exact pg_wait_tracer export. Not the adjacent EXPLAIN run; compare state shape, not durations. > --> > >  > > <table> > <tr> > <td rowspan="2" width="25%"><img src="assets/ex_materialize.svg"></td> > <td><b>Performance</b></td><td>Prevents redundant execution of subplans; uses memory and potentially disk if the set is large.</td> > </tr> > <tr><td><b>Cost</b></td><td><code>`materialization cost + cpu_tuple_cost * number of tuples`</code></td></tr> > </table>