FRESH

Hacker News

Home

Good CTE, Bad CTE

195 points by radimm

by vlaaad

5 subcomments

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.

by atombender

0 subcomment

I mostly use CTEs for organization these days, and in rare cases to express queries which cannot be written without them.
These days I often write queries like this (especially when doing exploratory ad hoc queries, but also in apps) even when it's not necessary to use a CTE:
```
    WITH
      a AS (
        SELECT ... FROM ... WHERE ... etc.
      )
    SELECT * FROM a
```
The first CTE query defines the input, and the main query just reads from it. Subsequent subqueries invoke steps on that input to group, filter, join, and so on.
This has a bunch of nice benefits. For example, it allows me to add steps incrementally, and to "comment out" a step I can simply change the next step's input to read from the preceding step. Each step can be read and understood in isolation.
I work a lot with Postgres, ClickHouse, and SQLite, and generally find that the database inlines and optimizes CTEs, and challenges mostly concern performance traps (like IN or EXISTS) that allly to non-CTE situations as well.

by yen223

2 subcomments

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.

by oveja

0 subcomment

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.

by mcdonje

1 subcomments

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

by bob1029

1 subcomments

> 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.

by dspillett

0 subcomment

I wrangle databases by day, and do martial arts of an evening. Two arenas where CTEs can cause significant headaches!

by uwemaurer

1 subcomments

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/)

by qwertydog

1 subcomments

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)

by jpalomaki

2 subcomments

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.

by faangguyindia

1 subcomments

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.

by ctippett

0 subcomment

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).

by intrasight

0 subcomment

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.

by jt-hill

0 subcomment

Wow it just keeps going. Extremely thorough.

by BSTRhino

1 subcomments

Love CYEs, use them all the time, great article.