> **CTE Scan** > <table> > <tr> > <td width="25%"><img src="assets/ex_ctescan.png"></td> > <td>Scans a Common Table Expression whose result is being evaluated separately. In PostgreSQL 18, an eligible side-effect-free CTE referenced once is normally folded into the parent query; multiply referenced CTEs normally remain materialized. <code>MATERIALIZED</code> and <code>NOT MATERIALIZED</code> can influence the decision when semantics permit.</td> > </tr> > </table> > > ```sql > -- Forcing materialization of a CTE > EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) > WITH t AS MATERIALIZED (SELECT * FROM animals) > SELECT * FROM t; > ``` > >  > > <!-- literal-explain-plan > Captured EXPLAIN provenance for the adjacent reader-facing visual plan. > Canonical capture metadata lives in scratch/actual_operation_plans.json. > > CTE Scan on t (cost=174.00..374.00 rows=10000 width=48) (actual time=0.003..0.788 rows=10000 loops=1) > Output: t.id, t.name, t.species_id, t.created_at > Buffers: shared hit=74 > CTE t > -> Seq Scan on public.animals (cost=0.00..174.00 rows=10000 width=27) (actual time=0.003..0.320 rows=10000 loops=1) > Output: animals.id, animals.name, animals.species_id, animals.created_at > Buffers: shared hit=74 > Planning: > Buffers: shared hit=79 > Planning Time: 0.171 ms > Execution Time: 0.996 ms > --> > > > <!-- > 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_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>Cost</b></td><td>Materialization cost of the CTE + scan cost.</td></tr> > </table>