### Materialize ![[assets/ex_materialize.png|256]] ### The Explain Trace ```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 (...) ``` --- - **Description**: Stores intermediate results in memory (or temporary files) for reuse by a parent node. - **Performance**: Prevents redundant execution of subplans; uses memory and potentially disk if the set is large. - **Factors**: Size of intermediate results and the number of times the inner relation must be re-scanned. - **Cost**: `materialization cost + cpu_tuple_cost * number of tuples` - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/IO/BufFile/BufFileRead|IO: BufFileRead]] - [[Workloads/IO/BufFile/BufFileWrite|IO: BufFileWrite]] - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]]