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.
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>)
I wrangle databases by day, and do martial arts of an evening. Two arenas where CTEs can cause significant headaches!
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.
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)
> 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.