Good CTE, Bad CTE
by radimm on 3/30/2026, 7:11:11 AM
https://boringsql.com/posts/good-cte-bad-cte/
Comments
by: vlaaad
Use the term, never define the term, classic.<p>CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
3/31/2026, 7:43:18 AM
by: uwemaurer
Great article, I always like to structure my queries with CTEs and I was (wrongly) assuming it all gets inlined at the end. Sometimes it also gets complicated since these intermediate results can't be easily seen in a SQL editor. I was working on a UI to parse CTE queries and then execute them step by step to show the results of all the CTEs for easier understanding of the query (as part of this project <a href="https://github.com/sqg-dev/sqg/" rel="nofollow">https://github.com/sqg-dev/sqg/</a>)
3/31/2026, 9:14:56 AM
by: dspillett
I wrangle databases by day, and do martial arts of an evening. Two arenas where CTEs can cause significant headaches!
3/31/2026, 8:04:16 AM
by: yen223
I've always thought of CTEs as a code organisation tool, not an optimisation tool. The fact the some rdbms treats them as an optimisation fence was a bug, not a feature.
3/31/2026, 8:09:44 AM
by: qwertydog
Great post - thanks. I think the columns in the index you suggested in the pre-pg12 section are in the wrong order (that index would get used)
3/31/2026, 8:00:03 AM
by: bob1029
> Recursive CTEs use an iterative working-table mechanism. Despite the name, they aren't truly recursive. PostgreSQL doesn't "call itself" by creating a nested stack of unfinished queries.<p>If you want something that is more like actual recursion (I.e., depth-first), Oracle has CONNECT BY which does not require the same kind of tracking. It also comes with extra features to help with cycle detection, stack depth reflection, etc.<p>If your problem is aligned with the DFS model, the oracle technique can run circles around recursive CTEs. Anything with a deep hierarchy and early termination conditions is a compelling candidate.
3/31/2026, 8:11:27 AM