News for hackers. Stuff that matters.
Re:Literate SQL by nerdponx on Sunday June 25, 2017 @10:26AM (#14630122)
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.

Re:Literate SQL by wvenable on Sunday June 25, 2017 @01:15PM (#14631027)
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.

Re:Literate SQL by mtone on Sunday June 25, 2017 @01:59PM (#14631255)
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.

Re:Literate SQL by saosebastiao on Sunday June 25, 2017 @02:25PM (#14631376)
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.

Re:Literate SQL by havermeyer on Sunday June 25, 2017 @02:52PM (#14631512)
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.
Re:Literate SQL by lobster_johnson on Sunday June 25, 2017 @01:25PM (#14631089)
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.

Re:Literate SQL by theprotocol on Sunday June 25, 2017 @10:54AM (#14630254)
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.
Re:Literate SQL by MarkusWinand on Sunday June 25, 2017 @12:15PM (#14630711)
Watch out—it's not that easy: http://modern-sql.com/feature/with/performance
Re:Literate SQL by gjvc on Sunday June 25, 2017 @10:30AM (#14630142)
For the uninitiated, what is GPP ?
Re:Literate SQL by okket on Sunday June 25, 2017 @10:41AM (#14630199)
Closest guess: General-purpose PreProcessor

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

Re:Literate SQL by nerdponx on Sunday June 25, 2017 @10:59AM (#14630277)
That's it, sorry I wasn't clear.