CTE vs subquery performance in PostgreSQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

Output of CTE vs Subquery with Aggregation

Given the table sales with columns region and amount, what is the output of these two queries?

Query 1 uses a CTE:

WITH regional_totals AS (
  SELECT region, SUM(amount) AS total
  FROM sales
  GROUP BY region
)
SELECT * FROM regional_totals WHERE total > 1000;

Query 2 uses a subquery:

SELECT region, total FROM (
  SELECT region, SUM(amount) AS total
  FROM sales
  GROUP BY region
) AS sub WHERE total > 1000;

Assuming the sales table has:

region | amount
-------+--------
East   | 500
East   | 600
West   | 400
West   | 300
South  | 1200

A Empty result for Query 1; [{"region": "South", "total": 1200}] for Query 2

B [{"region": "East", "total": 1100}, {"region": "South", "total": 1200}] for both queries

C Both queries return empty result

D [{"region": "East", "total": 1100}] for Query 1; empty result for Query 2

💡 Hint

Both CTE and subquery here calculate the same aggregation and filter the same way.

Both queries compute the sum of amounts grouped by region, then filter regions with totals over 1000. The results are the same: East (1100) and South (1200).

🧠 Conceptual

intermediate

Performance difference between CTE and subquery in PostgreSQL before version 12

Which statement best describes the performance behavior of CTEs compared to subqueries in PostgreSQL before version 12?

A CTEs act as optimization fences and are materialized, which can hurt performance.

B CTEs are always inlined and optimized like subqueries.

C Subqueries are materialized by default, while CTEs are always optimized away.

D Both CTEs and subqueries are always materialized and never optimized.

💡 Hint

Think about how PostgreSQL treats CTEs as separate query blocks before version 12.

Before PostgreSQL 12, CTEs are optimization fences and are materialized, meaning the database computes and stores the CTE result before the outer query runs. This can hurt performance compared to subqueries, which can be inlined and optimized.

📝 Syntax

advanced

Identify the syntax error in CTE usage

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

A WITH cte AS (SELECT id FROM users) SELECT id FROM cte;

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

C WITH cte AS SELECT * FROM users SELECT * FROM cte;

D WITH cte AS (SELECT * FROM users WHERE active = true) SELECT * FROM cte;

💡 Hint

Check the parentheses around the CTE query.

Option C is missing parentheses around the CTE query, which is required syntax in PostgreSQL.

❓ optimization

advanced

Optimizing repeated subqueries vs CTEs

You have a complex subquery used multiple times in a large query. Which approach is generally better for performance in PostgreSQL 13 and later?

A Repeat the subquery inline multiple times for better optimization.

B Avoid CTEs because they always cause performance degradation.

C Use temporary tables instead of CTEs or subqueries.

D Use a CTE to compute the subquery once and reuse the result.

💡 Hint

PostgreSQL 12+ can inline CTEs unless marked MATERIALIZED.

In PostgreSQL 12 and later, CTEs can be inlined or materialized. Using a CTE to compute a complex subquery once and reuse it can improve performance by avoiding repeated computation.

🔧 Debug

expert

Why does this query with CTE run slower than the subquery version?

Consider these two queries on a large table orders with an index on customer_id:

Query 1 (CTE):

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

Query 2 (subquery):

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

Query 1 runs significantly slower than Query 2. What is the most likely reason?

A The CTE forces materialization, preventing index usage and causing a full scan.

B The subquery version has a syntax error and does not run.

C The CTE query is missing a GROUP BY clause causing incorrect aggregation.

D The subquery version disables parallelism causing slower execution.

💡 Hint

Think about how PostgreSQL treats CTEs as optimization fences before version 12 and how that affects index usage.

The CTE is materialized as a temporary result, which disables index scans and parallelism on the base table. The subquery can be optimized and use indexes, making it faster.