### CTE Scan ![[assets/ex_ctescan.png|256]] ### The Explain Trace ```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 (...) ``` --- - **Description**: Scans a Common Table Expression (CTE). - **Performance**: Depends on whether the CTE is materialized (stored in memory/disk) or inlined into the main query. - **Factors**: Size and complexity of the CTE, and available `work_mem`. - **Cost**: Materialization cost of the CTE + scan cost. ![[assets/ex_cte_scan.svg|256]] - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/IO/BufFile/BufFileRead|IO: BufFileRead]] - [[Workloads/IO/BufFile/BufFileWrite|IO: BufFileWrite]] - [[Workloads/LWLock/Buffers/SharedTupleStore|LWLock: SharedTupleStore]] - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]]