March 31, 2026
Spilling the CTE tea
Good CTE, Bad CTE
CTE Wars: Are those SQL “WITH” blocks tidy helpers or secret slowdowns
TLDR: PostgreSQL now treats CTEs (the “WITH” parts of SQL) more smartly, sometimes speeding things up instead of slowing them down. The crowd is split: some cheer the change as overdue, others say CTEs should be for readability only—plus nitpicks, jokes, and an Oracle cameo keep the comments spicy.
Grab your popcorn: the community is sparring over CTEs—those little “WITH” blocks in SQL that act like named mini-queries to tidy up messy statements. One dev opened with a snarky clapback—“Use the term, never define the term, classic”—then dutifully defined CTEs for the room. The big reveal: before PostgreSQL 12, CTEs were forced into a separate step, which could slow things down. Now, the database can sometimes fold them into the main query for speed. Translation: CTEs used to build walls; now they might be windows.
And that’s where the drama erupts. One camp insists CTEs are for readability, not speed—calling the old slowdown behavior “a bug, not a feature.” Another nitpicked the author’s suggested index order—because of course someone did. A martial-arts-meets-databases commenter joked that CTEs cause headaches in both his day job and dojo. Then an Oracle fan rolled in to flex: recursive CTEs “aren’t truly recursive”? Enter the throwback brag—“Oracle’s CONNECT BY”—like yelling “my dad could beat up your dad.” Memes flew about “optimization fences,” with folks debating whether CTEs are the hero you call for clarity or the villain quietly kneecapping performance. The verdict? CTEs are great—until they aren’t.
Key Points
- •CTEs were always materialized prior to PostgreSQL 12, acting as optimization fences that prevented predicate pushdown and index use.
- •Since PostgreSQL 12, CTEs can be inlined or materialized depending on how they are written and used, enabling better optimization.
- •Developers often use CTEs to impose perceived execution order, but the planner controls execution; misuse can harm performance.
- •A sample schema (customers, orders, orders_archive, employees) and synthetic data are provided to demonstrate planner behavior.
- •An EXPLAIN example shows that, in older PostgreSQL versions, a CTE is fully materialized and outer filters are applied afterward, limiting optimization.