> [!NOTE] 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; > ``` > >  > > ```text > 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 > ``` > >  > > <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>