Common Table Expressions
CTEs improve readability but introduce planner and recursion pitfalls. Audit new CTE-heavy queries before production.
//
Search across all documentation pages
CTEs improve readability but introduce planner and recursion pitfalls. Audit new CTE-heavy queries before production.
Assuming CTE is always inline - PostgreSQL may materialize WITH nodes (especially with recursion or certain references)..
Duplicate CTE references - Same CTE referenced twice may execute twice if not materialized..
Filtering after huge CTE - Large intermediate set then WHERE on outer query..
CTE chains too deep - Ten-step WITH hard to test..
Recursive CTE without cycle guard - Infinite recursion until work_mem exhausted..
When you need single evaluation of expensive CTE referenced multiple times.
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+.
Reviewed by Chris St. John·Last updated Jul 18, 2026