CTE Performance (Materialize)

CTE Performance (Materialize) is a SQL statement in the CTE (WITH) category. PostgreSQL 12+: controls whether a CTE is materialized (executed once, results cached) or inlined. The syntax is WITH cte AS MATERIALIZED (SELECT ...) .... It returns materialized/inlined CTE. A typical example: WITH expensive_cte AS MATERIALIZED ( SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id ) SELECT e.name, e.salary, ec.avg_sal FROM employees e JOIN expensive_cte ec ON e.dept_id = ec.dept_id WHERE e.salary < ec.avg_sal; -- The CTE is evaluated ONCE, results stored -- Prevents re-execution when referenced multiple times WITH simple_cte AS NOT MATERIALIZED ( SELECT * FROM active_users ) SELECT * FROM simple_cte WHERE age > 18; -- NOT… A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures).