CTE materialization behavior in PostgreSQL

Introduction

CTE materialization controls how PostgreSQL processes common table expressions (CTEs). It affects query speed and memory use.

When you want to break a complex query into simpler parts for clarity.

When you need to reuse the same subquery multiple times in a query.

When you want to control whether the CTE is executed once or multiple times.

When optimizing query performance by deciding if intermediate results should be stored.

When debugging query plans to understand how PostgreSQL handles CTEs.

Syntax

PostgreSQL

WITH cte_name AS MATERIALIZED (
  SELECT ...
)
SELECT * FROM cte_name;

By default, PostgreSQL materializes CTEs, meaning it runs the CTE once and stores the result.

You can use MATERIALIZED or NOT MATERIALIZED to force or prevent this behavior.

Examples

This forces PostgreSQL to run the CTE once and store the results before the main query.

PostgreSQL

WITH cte AS MATERIALIZED (
  SELECT id, name FROM users WHERE active = true
)
SELECT * FROM cte;

This tells PostgreSQL to inline the CTE, potentially running it multiple times or optimizing it with the main query.

PostgreSQL

WITH cte AS NOT MATERIALIZED (
  SELECT id, name FROM users WHERE active = true
)
SELECT * FROM cte WHERE id > 10;

Default behavior: PostgreSQL materializes the CTE unless NOT MATERIALIZED is specified.

PostgreSQL

WITH cte AS (
  SELECT id FROM orders WHERE amount > 100
)
SELECT * FROM cte;

Sample Program

This query uses a materialized CTE to first get recent orders, then counts orders per customer.

PostgreSQL

WITH recent_orders AS MATERIALIZED (
  SELECT order_id, customer_id FROM orders WHERE order_date > '2024-01-01'
)
SELECT customer_id, COUNT(*) AS order_count FROM recent_orders GROUP BY customer_id;

Important Notes

Materialized CTEs can improve performance if the CTE is used multiple times.

Non-materialized CTEs allow PostgreSQL to optimize the query better but may run the CTE multiple times.

Use EXPLAIN to see how PostgreSQL handles CTEs in your queries.

Summary

CTE materialization controls if PostgreSQL stores intermediate results or inlines the CTE.

Use MATERIALIZED to force storing results, NOT MATERIALIZED to allow inlining.

Choosing the right behavior can help optimize query speed and resource use.