Common Table Expressions

CTEs improve readability but introduce planner and recursion pitfalls. Audit new CTE-heavy queries before production.

How to Use This List

• Review during SQL PR for hidden materialization or recursion depth.
• EXPLAIN ANALYZE after adding CTE chains.
• Prefer readability; refactor when proven slow.

Planner Behavior (1-3)

1. Assuming CTE is always inline - PostgreSQL may materialize WITH nodes (especially with recursion or certain references)..

   • Why it happens: Planner chooses based on cost; verify with EXPLAIN..
   • Fix: undefined.

2. Duplicate CTE references - Same CTE referenced twice may execute twice if not materialized..

   • Why it happens: Materialize with AS MATERIALIZED in PG 12+ when needed..
   • Fix: undefined.

3. Filtering after huge CTE - Large intermediate set then WHERE on outer query..

   • Why it happens: Push filters into earliest CTE step possible..
   • Fix: undefined.

Readability Traps (4-5)

1. CTE chains too deep - Ten-step WITH hard to test..

   • Why it happens: Break into views or staged temp tables for ETL..
   • Fix: undefined.

2. Recursive CTE without cycle guard - Infinite recursion until work_mem exhausted..

   • Why it happens: Track visited nodes; set max recursion depth..
   • Fix: undefined.

FAQs

Related

• Advanced SQL Basics - CTE intro
• Recursive CTEs - hierarchies

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.