[HN Gopher] Good CTE, Bad CTE
___________________________________________________________________
Good CTE, Bad CTE
Author : radimm
Score : 166 points
Date : 2026-03-30 07:11 UTC (1 days ago)
(HTM) web link (boringsql.com)
(TXT) w3m dump (boringsql.com)
| vlaaad wrote:
| Use 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.
| radimm wrote:
| OP here, damn - that's a very good point. Can't believe I
| missed it.
| iainmerrick wrote:
| From the headline, I thought it might be about sports-related
| concussions!
|
| I was morbidly curious what a "good CTE" could possibly be...
| QuantumNomad_ wrote:
| 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_encephalopa
| t...
| iainmerrick wrote:
| 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!
| alistairSH wrote:
| Yep, that's it.
|
| The NFL in the US has famously gone to great lengths to
| downplay the impact of CTE on current and retired
| players. And there have been several famous players who
| literally lost their minds as they aged, and we now know
| that was due to CTE. Something like 90% of ex-NFLers have
| it. The number is still really bad for collegiate
| players. And even high school players are at risk.
|
| It was to the point that Will Smith starred in a movie
| about it.
| https://en.wikipedia.org/wiki/Concussion_(2015_film)
| tialaramex wrote:
| 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.
| swasheck wrote:
| i appreciate the way you took the feedback. i saw the domain
| name and immediately knew the content and context. the
| article did not disappoint. i come from a heavy mssql
| background with some postgres sprinkled in, but my current
| company is migrating our mssql fleet to pg and it's nice to
| have a technical foundation and article to be able to truly
| understand and pass the differences and similarities then how
| the two platforms handle workload. Traditionally it's just
| been multiple sides, mercilessly criticizing each other for
| their deficiencies, but each platform has its own strengths
| and its own gaps. I'm excited to be a part of this migration,
| and I appreciate technical articles such as this that help me
| articulate the broader challenges to both our executive level
| levels, and our developers.
|
| edit: syntax. voice to text was liberal with the comma abuse
| xxs wrote:
| I read the article before the abbreviate definition inclusion
| as its very opening. I had never met the abbreviation before.
|
| It'd be quite surprising the WITH statement in top a query to
| be the first feature to learn/use past basic SQL. Is it
| personal experience in some industry?
| tclancy wrote:
| Agreed. I was relieved to see this wasn't written by Cam
| Skatteboro.
| lizknope wrote:
| Yeah, I thought I was on a sports site. Cam thinks CTE isn't
| real and is "all in your head." Technically correct about the
| all in your head part.
| cpfohl wrote:
| Your comment could have been _more_ helpful without the first
| sentence. SAME content, same correction, less superiority:
| "CTE is an overloaded term, in this article ......"
|
| This is a valuable comment, don't ruin it with sarcasm and
| rudeness.
| NooneAtAll3 wrote:
| > Your comment could have been more helpful without the first
| sentence.
|
| no it wouldn't
|
| the whole point is to critique the post
| mcdonje wrote:
| While generally a fair critique, the site does have "SQL" in
| its name.
| da_chicken wrote:
| Eh, almost every link on the homepage has an initialism or
| acronym in the title, and roughly none of them are actually
| defining the term they're using. Indeed, not to point fingers,
| your own submissions make the same mistake.
|
| Sure, yes, OP should (and now has) defined the term. But at the
| same time it's reasonable to expect that someone reading a blog
| post on BoringSQL.com would already know the term just as much
| as we could expect people interested in Clojure would know what
| a REPL is.
| qwertydog wrote:
| 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)
| radimm wrote:
| Thanks - I will recheck later today.
| dspillett wrote:
| I wrangle databases by day, and do martial arts of an evening.
| Two arenas where CTEs can cause significant headaches!
| yen223 wrote:
| 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.
| solumunus wrote:
| Improved readability is definitely the primary benefit.
| bob1029 wrote:
| > 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 reflection, etc.
|
| 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.
| hans_castorp wrote:
| > 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
| uwemaurer wrote:
| 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/)
| siddboots wrote:
| 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.
| nraynaud wrote:
| 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.
| mcdonje wrote:
| There'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
| scythmic_waves wrote:
| That was also a great read, thanks!
| oveja wrote:
| To the author of the article. This was a really nice and
| educating read. You made me finally understand recursive CTEs,
| the org chart was a really good example. Thank you.
| ctippett wrote:
| Not to sound like a broken record, but I need to echo what many
| other replies have already said: great article.
|
| Embarassingly, despite thinking of myself as pretty knowledgeable
| with SQL, I had no idea you could nest DML statements _inside_ a
| CTE. I always assumed /used DML statements as the final statement
| after a CTE was defined. I'm not sure if or when I might use this
| in the future, but it's neat to learn something new (and to be
| humbled at the same time).
| jt-hill wrote:
| Wow it just keeps going. Extremely thorough.
| jpalomaki wrote:
| Sometimes easy performance trick is to split the CTE to separate
| queries, put the results to unlogged temporary tables and add
| whatever indexes the next step needs.
|
| Obviously makes only sense for stuff like analytical queries that
| are not running constantly.
| taeric wrote:
| Worth underlying the OLAP versus OLTP divide you are speaking
| to on the close, there.
| andsbf wrote:
| An issue that has arise for me in some situations is that for
| more expensive/reporting queries we point to a db replica,
| where temporary tables are not an option.
| intrasight wrote:
| I just wish that SQL Server had a materialize keyword like
| Postgres.
|
| I'll write some nice clean CTEs and then have to refactor it to
| temp tables due to the lack of materialization.
| faangguyindia wrote:
| I consulted at some Indian startups. Such bad queries were
| written which were costing them so much compute
|
| I just rewrote all queries with claude code and next day and
| compute decreased to 1/5th.
| BSTRhino wrote:
| Love CYEs, use them all the time, great article.
___________________________________________________________________
(page generated 2026-03-31 23:01 UTC)