CTE materialization behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - CTE materialization behavior

Start Query Execution

↓

Identify CTE

↓

Materialize CTE Result

↓

Use Materialized Result in Main Query

↓

Return Final Result

↓

End

The query execution first identifies the CTE, fully computes and stores its result (materializes it), then uses this stored result in the main query before returning the final output.

Execution Sample

PostgreSQL

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

This query first computes the CTE by selecting active users, materializes the result, then filters those with id > 10.

Execution Table

Step	Action	CTE Computation	Main Query Action	Output Rows
1	Start query execution	Not started	Not started	None
2	Compute CTE	Select active users: ids 5, 12, 15	Waiting for CTE	None
3	Materialize CTE result	Store rows (5,12,15)	Waiting for CTE	None
4	Execute main query using CTE	Materialized rows used	Filter id > 10	Rows with ids 12, 15
5	Return final result	CTE result reused	Output filtered rows	Rows with ids 12, 15
6	End query execution	CTE fully used	Main query done	Final output returned

💡 Query ends after main query uses the fully materialized CTE result.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
CTE Result	Empty	Rows (5,12,15) computed	Rows (5,12,15) stored	Rows (5,12,15) reused	Rows (5,12,15) reused
Main Query Output	Empty	Empty	Empty	Rows (12,15) filtered	Rows (12,15) output

Key Moments - 3 Insights

Why does the CTE compute all rows before the main query starts?

Because PostgreSQL materializes the CTE result first (see execution_table step 3), it fully computes and stores the CTE before the main query uses it.

Can the main query see changes made after the CTE is materialized?

No, since the CTE is materialized (stored) at step 3, the main query uses this fixed snapshot, ignoring any later changes.

What happens if the CTE is large?

Materializing a large CTE means storing many rows in memory or disk before the main query runs, which can impact performance (see variable_tracker for stored rows).

Visual Quiz - 3 Questions

Look at the execution_table, what rows does the CTE contain after step 3?

A Rows with ids 12, 15 only

B Rows with ids 5, 12, 15

C No rows yet

D All users regardless of active status

💡 Hint

Check the 'CTE Computation' column at step 3 in the execution_table.

At which step does the main query start filtering rows?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look at the 'Main Query Action' column in the execution_table.

If the CTE was not materialized, what would change in the execution flow?

A Main query would execute before CTE

B CTE result would be empty

C CTE would be computed repeatedly for each main query row

D No change at all

💡 Hint

Materialization means storing once; without it, the CTE might be recomputed multiple times.

Concept Snapshot

CTE Materialization in PostgreSQL:
- CTEs are computed fully and stored before main query runs.
- Main query uses this stored snapshot, not live data.
- Materialization can impact performance for large CTEs.
- Helps isolate CTE execution from main query.
- Syntax: WITH cte AS (SELECT ...) SELECT ... FROM cte;

Full Transcript

In PostgreSQL, when you use a Common Table Expression (CTE), the database first computes the CTE fully and stores its result. This process is called materialization. After the CTE is materialized, the main query runs using this stored data. This means the main query does not see any changes made after the CTE was computed. Materialization ensures the CTE result is stable and isolated, but it can affect performance if the CTE is large because all rows must be stored before continuing. The execution flow starts with query execution, then CTE computation and storage, followed by main query execution using the stored CTE result, and finally returning the output.