Bird
0
0

Why did PostgreSQL change CTE behavior starting from version 12 regarding performance?

hard📝 Conceptual Q10 of 15
PostgreSQL - Common Table Expressions
Why did PostgreSQL change CTE behavior starting from version 12 regarding performance?
ATo force all CTEs to materialize for consistency.
BTo disable CTEs in favor of temporary tables.
CTo remove support for subqueries entirely.
DTo allow CTEs to be inlined and optimized like subqueries by default.
Step-by-Step Solution
Solution:

  1. Step 1: Understand PostgreSQL 12 change

    PostgreSQL 12 changed CTEs to be inlined by default unless MATERIALIZED is specified.

  2. Step 2: Reason about performance impact

    This allows the planner to optimize CTEs like subqueries, improving performance.

  3. Final Answer:

    To allow CTEs to be inlined and optimized like subqueries by default. -> Option D

  4. Quick Check:

    PG12 CTEs inline by default for better optimization [OK]
Quick Trick: PG12+ inlines CTEs by default for speed [OK]
Common Mistakes:
  • Thinking CTEs always materialize after PG12
  • Believing subqueries were removed
  • Assuming CTEs are disabled in PG12

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes
Full-Text Search - Highlighting with ts_headline - Quiz 10hard Full-Text Search - @@ match operator - Quiz 12easy JSON and JSONB - Arrow operators (-> and ->>) - Quiz 11easy Joins in PostgreSQL - LEFT JOIN and RIGHT JOIN - Quiz 7medium Set Operations and Advanced Queries - Conditional INSERT with ON CONFLICT - Quiz 1easy Subqueries in PostgreSQL - ALL, ANY, SOME with subqueries - Quiz 8hard Subqueries in PostgreSQL - Why subqueries are needed - Quiz 12easy Subqueries in PostgreSQL - Scalar subqueries - Quiz 11easy Views and Materialized Views - Views with CHECK OPTION - Quiz 7medium Window Functions in PostgreSQL - PARTITION BY for grouping windows - Quiz 13medium