2 ms·
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 o
by nerdponx 9y ago
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 https://logological.org/gpp) is always an option.
- gjvc 9y agoFor the uninitiated, what is GPP ?
- deleted 9y ago[deleted]
- theprotocol 9y agoRegarding 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.
- MarkusWinand 9y agoWatch out—it's not that easy: http://modern-sql.com/feature/with/performance http://modern-sql.com/feature/with/performance
- wvenable 9y agoEven 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.
- mtone 9y agoThe 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.
- lobster_johnson 9y agoLast 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.
- saosebastiao 9y agoThe 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.
- havermeyer 9y agoThis 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.