Hacker News Reader

Comment by nerdponx | Hacker News Reader

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction?

For that matter, having some kind of SQL-oriented macro/preprocessor language would be fantastic. I guess GPP (General Preprocessor, https://logological.org/gpp) is always an option.

View on HN

Even in databases without the same performance issues as PSQL, like SQL Server, "with clauses" can cause performance issues on their on own.

I recently refactored a long SQL query with a half-dozen with-expressions to a single query and increased the performance by an order of magnitude. They had used "with" to build up a query from a bunch of independent sets and then union them together and, while perfectly logical, it was a performance nightmare. In the end, I just took all the conditions that made up each query and combined it into one with the appropriate joins. I'd even argue that ultimately the finished product was easier to understand.

The trade-off is modularity. During development working with CTEs paves the way for establishing views later, possibly highlight recurring requirements or gaps in the schema, and the logical separation of general purpose sets (ie functions) from the specifics of a particular query is valuable.

Like in everything, a balance is ideal. For reporting/analysis/testing purposes, I think nesting views and CTEs is more productive in the long run, while production application code is probably best kept as straightforward as possible.

The reasoning was that it was easier to implement that way. CTEs can contain state changing behavior and for that reason they are treated as optimization fences in order to be as safe as possible.

I believe Tom Lane has since said that there isn't any reason it should stay that way and the community has never received any guarantee that this was going to stay the same. It just hasn't been implemented. In other words, they're seeking contributions. I'd do it myself if I were even a remotely capable C programmer, but I'm not.

This is how CTEs work in BigQuery (using standard SQL), for example. There are advantages and disadvantages both ways, but the benefit of making CTEs behave like subqueries is that the query engine can push down filters into table scans.

Last I looked, this was coming in either Postgres 10 or possibly the release after. I'm on mobile so I don't have a link, but I believe a new keyword is being added that will change the behaviour of "with".

Edit: Can't find anything. I could have sworn they said there were patches being worked on, however.

Regarding postgres `with`: I thought the same as you, that it'd be implemented as a macro, and only just found out that it's not the case thanks to your comment. I'd love to hear more about this decision.

Watch out—it's not that easy: http://modern-sql.com/feature/with/performance

For the uninitiated, what is GPP ?

Closest guess: General-purpose PreProcessor

https://github.com/logological/gpp/

That's it, sorry I wasn't clear.

Reply on news.ycombinator.com