CTE vs subquery performance in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a CTE named recent_orders that selects orders from the last 30 days.

PostgreSQL

WITH recent_orders AS (SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '[1] days') SELECT * FROM recent_orders;

Drag options to blanks, or click blank then click option'

A7

B60

C15

D30

Attempts:

3 left

💡 Hint

Common Mistakes

Using an interval longer than 30 days changes the result set.

Forgetting to use INTERVAL keyword causes syntax errors.

The CTE filters orders from the last 30 days using the interval '30 days'.

2fill in blank

medium

Complete the code to select customer_id and total order count using a subquery.

PostgreSQL

SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count FROM customers WHERE customer_id = [1];

Drag options to blanks, or click blank then click option'

A123

B'abc'

C"123"

D'123'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numeric IDs causes type mismatch errors.

Using string values when customer_id is numeric.

The customer_id is a number, so it should be used without quotes in the WHERE clause.

3fill in blank

hard

Fix the error in the CTE query that calculates average order amount per customer.

PostgreSQL

WITH avg_order AS (SELECT customer_id, AVG(order_amount) AS avg_amount FROM orders GROUP BY [1]) SELECT * FROM avg_order;

Drag options to blanks, or click blank then click option'

Aorder_id

Bcustomer_id

Corder_date

Dorder_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by order_date returns average per day, not per customer.

Grouping by order_amount causes syntax errors.

The GROUP BY clause must use customer_id to calculate average order amount per customer.

4fill in blank

hard

Fill both blanks to write a query that compares performance by selecting orders using a CTE and a subquery.

PostgreSQL

EXPLAIN ANALYZE WITH recent_orders AS (SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '[1] days') SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM recent_orders WHERE total > [2]);

Drag options to blanks, or click blank then click option'

A30

B100

C50

D7

Attempts:

3 left

💡 Hint

Common Mistakes

Using too large intervals or totals may skew performance results.

Using incorrect interval format causes syntax errors.

The CTE filters recent orders within 30 days, and the subquery filters orders with total greater than 50.

5fill in blank

hard

Fill all three blanks to write a query that uses a CTE to calculate total sales per product, filters by minimum sales, and orders results.

PostgreSQL

WITH sales_summary AS (SELECT product_id, SUM(quantity * price) AS total_sales FROM sales GROUP BY [1]) SELECT product_id, total_sales FROM sales_summary WHERE total_sales > [2] ORDER BY [3] DESC;

Drag options to blanks, or click blank then click option'

Aproduct_id

B1000

Ctotal_sales

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by order_date instead of product_id changes the aggregation.

Ordering by order_date does not sort by sales amount.

The query groups by product_id, filters products with sales over 1000, and orders by total_sales descending.

Practice

1. What is a common performance difference between CTEs and subqueries in PostgreSQL before version 12?

easy

A. CTEs and subqueries have identical performance.

B. Subqueries always run slower than CTEs.

C. CTEs act as optimization fences, potentially slowing queries.

D. Subqueries cannot be used inside SELECT statements.

Solution

1. Step 1: Understand CTE behavior in PostgreSQL before v12

   CTEs are treated as optimization fences, meaning the planner executes them separately and does not optimize across them.

2. Step 2: Compare with subqueries

   Subqueries can be inlined and optimized with the rest of the query, often leading to better performance.

3. Final Answer:

   CTEs act as optimization fences, potentially slowing queries. -> Option C

4. Quick Check:

   CTEs before v12 block optimization = A [OK]

Hint: Remember: CTEs block optimization before PostgreSQL 12 [OK]

Common Mistakes:

• Thinking subqueries are always slower
• Assuming CTEs and subqueries perform the same
• Believing subqueries can't be inside SELECT

2. Which of the following is the correct syntax to define a CTE in PostgreSQL?

easy

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

B. SELECT * FROM table_name WITH cte_name AS ();

C. CREATE CTE cte_name SELECT * FROM table_name;

D. WITH (SELECT * FROM table_name) AS cte_name SELECT * FROM cte_name;

Solution

1. Step 1: Recall CTE syntax

   CTEs start with WITH, followed by the CTE name, AS, and a query in parentheses.

2. Step 2: Validate each option

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; matches the correct syntax. Options A, B, and C have incorrect order or keywords.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; -> Option A

4. Quick Check:

   CTE syntax starts with WITH name AS (query) [OK]

Hint: CTE syntax always starts with WITH name AS (query) [OK]

Common Mistakes:

• Placing WITH after SELECT
• Using CREATE keyword for CTE
• Misplacing parentheses

3. Given the following queries, which one is likely to run faster in PostgreSQL 11?

-- 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;

medium

A. Query 1 runs faster because CTEs are always optimized.

B. Query 2 runs faster because subqueries allow better optimization in PostgreSQL 11.

C. Both queries run at the same speed.

D. Query 1 will cause a syntax error.

Solution

1. Step 1: Understand PostgreSQL 11 optimization

   In PostgreSQL 11, CTEs act as optimization fences, so Query 1 executes the CTE separately.

2. Step 2: Compare with subquery behavior

   Subqueries can be inlined and optimized with the main query, so Query 2 can be faster.

3. Final Answer:

   Query 2 runs faster because subqueries allow better optimization in PostgreSQL 11. -> Option B

4. Quick Check:

   PostgreSQL 11: subqueries optimize better than CTEs [OK]

Hint: Before v12, subqueries usually run faster than CTEs [OK]

Common Mistakes:

• Assuming CTEs always optimize well
• Thinking both queries have same speed
• Believing Query 1 has syntax error

4. Identify the error in this query using a CTE in PostgreSQL:

WITH recent_orders AS SELECT * FROM orders WHERE order_date > '2024-01-01'
SELECT * FROM recent_orders;

medium

A. Missing parentheses around the CTE query.

B. CTE name is invalid.

C. Missing semicolon after CTE.

D. CTEs cannot filter by date.

Solution

1. Step 1: Check CTE syntax

   CTE query must be enclosed in parentheses after AS keyword.

2. Step 2: Identify the missing parentheses

   The query lacks parentheses around SELECT * FROM orders WHERE ...

3. Final Answer:

   Missing parentheses around the CTE query. -> Option A

4. Quick Check:

   CTE query must be in parentheses [OK]

Hint: Always put CTE query inside parentheses after AS [OK]

Common Mistakes:

• Omitting parentheses around CTE query
• Thinking semicolon is required after CTE
• Believing CTE names can't be simple

5. You want to optimize a complex query in PostgreSQL 13 that uses multiple CTEs and subqueries. Which approach is best to improve performance?

hard

A. Avoid both CTEs and subqueries; use temporary tables instead.

B. Replace all CTEs with subqueries to allow better optimization.

C. Use only CTEs because subqueries are deprecated in PostgreSQL 13.

D. Keep CTEs because PostgreSQL 13 can inline them, improving speed.

Solution

1. Step 1: Understand PostgreSQL 13 CTE behavior

   Starting with PostgreSQL 12, CTEs can be inlined and optimized like subqueries.

2. Step 2: Evaluate optimization strategies

   Replacing CTEs with subqueries is not necessary; keeping CTEs maintains readability and performance.

3. Final Answer:

   Keep CTEs because PostgreSQL 13 can inline them, improving speed. -> Option D

4. Quick Check:

   PostgreSQL 12+ inlines CTEs for better optimization [OK]

Hint: PostgreSQL 12+ optimizes CTEs like subqueries [OK]

Common Mistakes:

• Thinking subqueries are deprecated
• Replacing CTEs blindly without testing
• Assuming temporary tables always improve speed