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.