> [!NOTE] CTE Scan > <table> > <tr> > <td width="25%"><img src="assets/ex_ctescan.png"></td> > <td>Scans a Common Table Expression (CTE) that has been materialized. By default, Postgres materializes CTEs (the 'optimization fence'), meaning the entire result of the WITH clause is computed and stored before being scanned by the main query.</td> > </tr> > </table> > > ```sql > -- Forcing materialization of a CTE > EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) > WITH t AS MATERIALIZED (SELECT * FROM animals) > SELECT * FROM t; > ``` > > ```text > CTE Scan on t (cost=348.00..748.00 rows=20000 width=48) (actual time=1.006..2.518 rows=20000 loops=1) > Output: t.id, t.name, t.species_id, t.created_at > Buffers: shared hit=148 > CTE t > -> Seq Scan on public.animals (...) > ``` > > <table> > <tr> > <td rowspan="5" width="25%"><img src="assets/ex_cte_scan.svg"></td> > <td><b>Performance</b></td><td>Depends on whether the CTE is materialized (stored in memory/disk) or inlined into the main query.</td> > </tr> > <tr><td><b>Factors</b></td><td>Size and complexity of the CTE, and available <code>work_mem</code>.</td></tr> > <tr><td><b>Cost</b></td><td>Materialization cost of the CTE + scan cost.</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/SharedTupleStore">LWLock: SharedTupleStore</a>, <a href="Workloads/LWLock/Buffers/BufferContent">LWLock: BufferContent</a></td></tr> > <tr><td colspan="3"><b>Description</b>: Scans a Common Table Expression (CTE).</td></tr> > </table>