CTE materialization behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple CTE query with aggregation

Consider the following PostgreSQL query using a CTE (Common Table Expression):

WITH sales_summary AS (SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id) SELECT * FROM sales_summary WHERE total_qty > 100;

What will be the output if the sales table has the following data?

product_id | quantity
-----------+---------
1          | 50
1          | 60
2          | 30
3          | 120
3          | 10

PostgreSQL

WITH sales_summary AS (SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY product_id) SELECT * FROM sales_summary WHERE total_qty > 100;

A

product_id: 1, total_qty: 110
product_id: 2, total_qty: 30
product_id: 3, total_qty: 130

B

product_id: 1, total_qty: 110
product_id: 3, total_qty: 130

C product_id: 3, total_qty: 130

D

product_id: 2, total_qty: 30
product_id: 3, total_qty: 130

💡 Hint

Sum the quantities per product and then filter those with total quantity greater than 100.

The CTE calculates total quantity per product. Product 1 has 50+60=110, product 2 has 30, product 3 has 120+10=130. Filtering total_qty > 100 returns products 1 and 3.

🧠 Conceptual

intermediate

CTE Materialization Behavior in PostgreSQL

In PostgreSQL versions before 12, how does the database handle CTEs by default?

Choose the correct description of CTE materialization behavior.

A CTEs are inlined into the main query and optimized together with it.

B CTEs are executed multiple times, once per each row of the main query.

C CTEs are always materialized as temporary result sets before the main query runs.

D CTEs are ignored and treated as subqueries without optimization.

💡 Hint

Think about whether the CTE is executed once or multiple times and if its result is stored.

Before PostgreSQL 12, CTEs are always materialized, meaning their result is computed and stored temporarily before the main query uses it. This can affect performance.

📝 Syntax

advanced

Identify the syntax error in a CTE query

Which of the following CTE queries will cause a syntax error in PostgreSQL?

A WITH cte AS SELECT id FROM users SELECT * FROM cte;

B WITH cte AS (SELECT id FROM users) SELECT * FROM cte;

C WITH cte AS (SELECT id FROM users) SELECT id FROM cte WHERE id > 10;

D WITH cte AS (SELECT id FROM users) SELECT COUNT(*) FROM cte;

💡 Hint

Check the syntax of the CTE definition, especially the parentheses.

Option A is missing parentheses around the CTE's SELECT statement, which is required syntax in PostgreSQL.

❓ optimization

advanced

Effect of MATERIALIZED and NOT MATERIALIZED on CTE performance

Given a CTE used multiple times in a query, which option best describes the effect of adding the keyword NOT MATERIALIZED in PostgreSQL 12+?

A The CTE is inlined and optimized with the main query, potentially executed multiple times.

B The CTE is executed once and its result stored temporarily for reuse.

C The CTE is ignored and replaced by a dummy empty result set.

D The CTE is executed multiple times regardless of usage.

💡 Hint

Consider how NOT MATERIALIZED affects query planning and execution.

NOT MATERIALIZED tells PostgreSQL to inline the CTE into the main query, allowing better optimization but possibly multiple executions.

🔧 Debug

expert

Diagnose unexpected query performance due to CTE materialization

A query uses a CTE that filters a large table and then joins it multiple times. The query runs slower than expected.

Which of the following is the most likely cause related to CTE materialization in PostgreSQL versions before 12?

A The CTE is materialized once, so the filtered result is reused efficiently.

B The CTE is ignored and the join uses the original large table multiple times.

C The CTE is inlined and optimized with the main query, causing repeated filtering.

D The CTE is materialized once, but the large intermediate result causes slow performance.

💡 Hint

Think about how materialization affects intermediate result size and reuse.

In PostgreSQL before 12, CTEs are materialized once. If the filtered result is still large, storing it can slow down the query despite reuse.

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