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

easy📝 Conceptual Q11 of 15

PostgreSQL - Common Table Expressions

ACTEs and subqueries have identical performance.

BSubqueries always run slower than CTEs.

CCTEs act as optimization fences, potentially slowing queries.

DSubqueries cannot be used inside SELECT statements.

Step-by-Step Solution

Solution:

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.
Step 2: Compare with subqueries
Subqueries can be inlined and optimized with the rest of the query, often leading to better performance.
Final Answer:
CTEs act as optimization fences, potentially slowing queries. -> Option C
Quick Check:
CTEs before v12 block optimization = A [OK]

Quick Trick: Remember: CTEs block optimization before PostgreSQL 12 [OK]

Common Mistakes:

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free