> [!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=1.57..1201975.39 rows=80000006 width=54) > Merge Cond: (a1.species_id = a2.species_id) > -> Index Scan using idx_animals_species_id on animals a1 (...) > -> Materialize (cost=1.29..1012.65 rows=20000 width=27) > -> Index Scan using idx_animals_species_id on animals a2 (...) > ``` > > <table> > <tr> > <td rowspan="5" 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>Factors</b></td><td>Size of intermediate results and the number of times the inner relation must be re-scanned.</td></tr> > <tr><td><b>Cost</b></td><td><code>`materialization cost + cpu_tuple_cost * number of tuples`</code></td></tr> > <tr><td><b>Operates on</b></td><td><a href="Structures/Result Set">Result Set</a></td></tr> > <tr><td><b>Workloads</b></td><td><a href="Workloads/IO/BufFile/BufFileRead">IO: BufFileRead</a>, <a href="Workloads/IO/BufFile/BufFileWrite">IO: BufFileWrite</a>, <a href="Workloads/LWLock/Buffers/BufferContent">LWLock: BufferContent</a></td></tr> > <tr><td colspan="3"><b>Description</b>: Stores intermediate results in memory (or temporary files) for reuse by a parent node.</td></tr> > </table>