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.

Practice

1. What does CTE materialization control in PostgreSQL?

easy

A. The data type of columns in the CTE

B. The order of execution of SQL statements

C. The type of join used in the query

D. Whether the CTE results are stored temporarily or inlined into the main query

Solution

1. Step 1: Understand CTE basics

   A Common Table Expression (CTE) is a temporary result set used within a query.

2. Step 2: Identify materialization role

   Materialization controls if PostgreSQL stores the CTE result temporarily or inlines it into the main query for optimization.

3. Final Answer:

   Whether the CTE results are stored temporarily or inlined into the main query -> Option D

4. Quick Check:

   CTE materialization = store or inline [OK]

Hint: Materialization means store or inline CTE results [OK]

Common Mistakes:

• Confusing materialization with join types
• Thinking it controls execution order
• Assuming it changes data types

2. Which of the following is the correct syntax to force PostgreSQL to store a CTE result instead of inlining it?

easy

A. WITH cte_name AS (SELECT * FROM table_name) MATERIALIZED

B. WITH cte_name AS NOT MATERIALIZED (SELECT * FROM table_name)

C. WITH cte_name AS MATERIALIZED (SELECT * FROM table_name)

D. WITH cte_name AS MATERIALIZED SELECT * FROM table_name

Solution

1. Step 1: Recall correct CTE syntax with materialization

   PostgreSQL syntax to force materialization is WITH cte_name AS MATERIALIZED (query).

2. Step 2: Check options for syntax correctness

   WITH cte_name AS MATERIALIZED (SELECT * FROM table_name) matches the correct syntax; others have misplaced keywords or missing parentheses.

3. Final Answer:

   WITH cte_name AS MATERIALIZED (SELECT * FROM table_name) -> Option C

4. Quick Check:

   Materialized keyword goes right after AS [OK]

Hint: Use AS MATERIALIZED right before the query in parentheses [OK]

Common Mistakes:

• Placing MATERIALIZED outside parentheses
• Using NOT MATERIALIZED to force storage
• Omitting parentheses around the query

3. Given the query:

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

What is the effect of MATERIALIZED here?

medium

A. The CTE result is stored temporarily before filtering by id < 10

B. The CTE is inlined and filtered directly without storing

C. The query will produce a syntax error

D. The CTE is ignored and the main query runs alone

Solution

1. Step 1: Understand MATERIALIZED effect on CTE

   Using MATERIALIZED forces PostgreSQL to store the CTE result before the outer query uses it.

2. Step 2: Analyze query filtering

   The outer query filters the stored CTE result by id < 10, so filtering happens after storage.

3. Final Answer:

   The CTE result is stored temporarily before filtering by id < 10 -> Option A

4. Quick Check:

   MATERIALIZED = store then filter [OK]

Hint: MATERIALIZED stores CTE before outer query filters [OK]

Common Mistakes:

• Thinking filtering happens before storing
• Assuming MATERIALIZED causes syntax error
• Believing CTE is ignored

4. Consider this query:

WITH cte AS NOT MATERIALIZED (SELECT * FROM orders WHERE amount > 100) SELECT * FROM cte WHERE amount < 500;

Why might this query fail or behave unexpectedly?

medium

A. NOT MATERIALIZED is not supported in this PostgreSQL version causing syntax error

B. The CTE is stored and filtering is delayed causing slow performance

C. The query ignores the WHERE clause inside the CTE

D. The CTE is materialized twice causing duplicate rows

Solution

1. Step 1: Check PostgreSQL support for NOT MATERIALIZED

   NOT MATERIALIZED was introduced in PostgreSQL 12; older versions do not support it and cause syntax errors.

2. Step 2: Analyze query behavior if unsupported

   If the version is older, the query fails with syntax error due to unrecognized keyword.

3. Final Answer:

   NOT MATERIALIZED is not supported in this PostgreSQL version causing syntax error -> Option A

4. Quick Check:

   NOT MATERIALIZED requires PostgreSQL 12+ [OK]

Hint: Check PostgreSQL version for NOT MATERIALIZED support [OK]

Common Mistakes:

• Assuming NOT MATERIALIZED always works
• Thinking filtering is ignored
• Believing CTE duplicates rows

5. You have a complex query with a CTE that is used multiple times in the main query. You want to optimize performance by avoiding repeated execution of the CTE. Which materialization behavior should you choose and why?

hard

A. Use NOT MATERIALIZED to inline the CTE and let PostgreSQL optimize repeated execution

B. Use MATERIALIZED to store the CTE result once and reuse it multiple times

C. Avoid using CTEs and write subqueries instead for better performance

D. Use MATERIALIZED to force PostgreSQL to re-execute the CTE each time

Solution

1. Step 1: Understand repeated CTE usage

   If a CTE is referenced multiple times, storing its result once avoids repeated computation.

2. Step 2: Choose materialization behavior

   Using MATERIALIZED forces storing the CTE result, so it is computed once and reused, improving performance.

3. Final Answer:

   Use MATERIALIZED to store the CTE result once and reuse it multiple times -> Option B

4. Quick Check:

   MATERIALIZED = store once, reuse many [OK]

Hint: Store CTE once with MATERIALIZED to reuse efficiently [OK]

Common Mistakes:

• Thinking NOT MATERIALIZED avoids repeated execution
• Believing MATERIALIZED causes repeated execution
• Avoiding CTEs unnecessarily