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 https://github.com/sqg-dev/sqg/)
Good CTE, Bad CTE
11–20 of 47 posts
Re: Good CTE, Bad CTE
#12Great 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 https://githu…
I think your assumption about inlining is essentially correct. As far as I know postgres was the last major rdbms to have an optimiser fence around CTEs.
Re: Good CTE, Bad CTE
#13Earlier quoted context omitted.
OP here, damn - that's a very good point. Can't believe I missed it.
From the headline, I thought it might be about sports-related concussions! I was morbidly curious what a "good CTE" could possibly be...
As someone who is not much of a sports person, now I was wondering what CTE means in sports.
Seems to be this:
> Chronic traumatic encephalopathy (CTE) is a progressive neurodegenerative disease […]
> Evidence indicates that repetitive concussive and subconcussive blows to the head cause CTE. In particular, it is associated with contact sports such as boxing, American football, Australian rules football, wrestling, mixed martial arts, ice hockey, rugby, and association football.
https://en.wikipedia.org/wiki/Chronic_traumatic_encephalopat...
Re: Good CTE, Bad CTE
#14Use the term, never define the term, classic. 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.
Agreed. I was relieved to see this wasn’t written by Cam Skatteboro.
Re: Good CTE, Bad CTE
#15Earlier quoted context omitted.
From the headline, I thought it might be about sports-related concussions! I was morbidly curious what a "good CTE" could possibly be...
As someone who is not much of a sports person, now I was wondering what CTE means in sports. Seems to be this: > Chronic traumatic encephalopathy (CTE) is a progressive neurodegenerative disease […] > Evidence indicates that repetitive concussive and subconcussive blows to the head cause CTE. In particular, it is associated with contact sports such as boxing, American football, Australian rules football, wrestling, m…
Yeah - Muhammad Ali is the most famous victim (or at least likely victim, I don’t think he was officially diagnosed with CTE as it wasn’t well understood back then). In the UK, it’s gradually becoming recognised as a serious problem in rugby.
I assumed the C stood for Concussion. Wrong but also partly right!
Re: Good CTE, Bad CTE
#16Earlier quoted context omitted.
OP here, damn - that's a very good point. Can't believe I missed it.
From the headline, I thought it might be about sports-related concussions! I was morbidly curious what a "good CTE" could possibly be...
I was thinking "Compile Time Execution" like Rust's const, C++ consteval functions, Zig's comptime, that sort of thing. So the good/ bad made more sense but I was still on the wrong track, yeah a definition was appropriate.
Re: Good CTE, Bad CTE
#17I'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.
[deleted]
Re: Good CTE, Bad CTE
#18Great 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 https://githu…
I think your assumption about inlining is essentially correct. As far as I know postgres was the last major rdbms to have an optimiser fence around CTEs.
I concur, “the Germans” have created an algorithm that completely “see through” subqueries/CTEs when planning a query. The way the query is written has no bearing on the execution.
Re: Good CTE, Bad CTE
#19There's some good stuff in here. I didn't know about the issues an aggregation in a CTE can cause and haven't used EXISTS much.
Regarding recursive CTEs, you might be interested in how DuckDb evolved them with USING KEY: https://duckdb.org/2025/05/23/using-key
Re: Good CTE, Bad CTE
#20> 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. 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 refle…
> 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.
All that is supported with CTEs as well. And both Postgres and Oracle support the SQL standard for these things.
You can't choose between breadth first/depth first using CONNECT BY in Oracle. Oracle's manual even states that CTE are more powerful than CONNECT BY