CTE Materialization Hints (PG)

CTE Materialization Hints (PG) is a SQL statement in the CTE (WITH) category. PostgreSQL 12+: MATERIALIZED forces CTE to be executed once (like a temp table). NOT MATERIALIZED inlines it (like a subquery). The syntax is WITH cte AS MATERIALIZED (...)/WITH cte AS NOT MATERIALIZED (...). It returns materialization hint. A typical example: -- MATERIALIZED: compute once, store result WITH expensive AS MATERIALIZED ( SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id ) SELECT e.name, e.salary, exp.avg_sal FROM employees e JOIN expensive exp ON e.dept_id = exp.dept_id WHERE e.salary > exp.avg_sal; -- NOT MATERIALIZED: inline into outer query WITH simple AS NOT MATERIALIZED ( SELECT * FROM employees WHERE status = 'active' ) SELECT * FROM simple WHERE department = 'IT';… A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query.