CTE vs subquery performance in PostgreSQL - Quick Revision & Key Differences

Recall & Review

beginner

What is a Common Table Expression (CTE) in SQL?

A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps organize complex queries by breaking them into simpler parts.

beginner

How does a subquery differ from a CTE?

A subquery is a query nested inside another query, often used inline. A CTE is defined before the main query and can be referenced multiple times, improving readability and sometimes performance.

intermediate

Does PostgreSQL always optimize CTEs and subqueries the same way?

No. Before PostgreSQL 12, CTEs acted as optimization fences, meaning they were executed separately and results materialized. Subqueries could be inlined and optimized better. From PostgreSQL 12, CTEs can be inlined like subqueries unless marked with MATERIALIZED or NOT MATERIALIZED.

intermediate

When might a CTE perform worse than a subquery?

If the CTE is materialized (executed and stored) and used only once, it can cause extra work and slower performance compared to a subquery that the planner can inline and optimize.

beginner

What is one advantage of using CTEs despite potential performance costs?

CTEs improve query readability and maintainability by breaking complex queries into named parts. They also allow recursive queries, which subqueries cannot do.

Before PostgreSQL 12, how were CTEs treated in terms of query optimization?

A They acted as optimization fences and were materialized separately

B They were always inlined and optimized with the main query

C They were ignored by the query planner

D They were automatically converted to subqueries

Before PostgreSQL 12, CTEs were executed separately and their results stored, preventing some optimizations.

Which keyword forces a CTE to be materialized in PostgreSQL 12 and later?

A MATERIALIZED

B OPTIMIZE

C INLINE

D CACHE

The MATERIALIZED keyword forces the CTE to be executed and stored separately.

Which of the following is a benefit of using subqueries over CTEs?

A Better readability for complex queries

B Ability to write recursive queries

C Potentially better performance due to inlining

D Named temporary result sets

Subqueries can be inlined and optimized better, which can improve performance.

What is a common reason to choose a CTE despite possible performance costs?

A They always run faster than subqueries

B They allow breaking queries into understandable parts

C They reduce disk space usage

D They automatically index the data

CTEs improve query readability and maintainability.

In PostgreSQL 12+, what happens if you do not specify MATERIALIZED or NOT MATERIALIZED for a CTE?

A The query will fail

B The CTE is always inlined if possible

C The CTE is always materialized

D The planner decides whether to inline or materialize

PostgreSQL's planner chooses the best approach automatically.

Explain the performance differences between CTEs and subqueries in PostgreSQL, especially before and after version 12.

Think about how the query planner treats CTEs and subqueries differently across versions.

Describe when it might be better to use a subquery instead of a CTE for performance reasons.

Consider how materialization affects execution time.