Hi, On 2018-07-24 19:49:19 -0400, Tom Lane wrote: > Andres Freund <and...@anarazel.de> writes: > > On 2018-07-24 18:03:43 -0500, Jeremy Finzel wrote: > >> On Tue, Jul 24, 2018 at 5:28 PM Andrew Gierth <and...@tao11.riddles.org.uk> > >> wrote: > >>> Posted for discussion, further development, criticism, whatever; feel > >>> free to include this (with credit) in any relevant patch. Consider this > >>> released under the PG license. > > >> In our environment we often want this to be a fence. For example it can > >> be used to only have smaller numbers of joins in each cte and not hit the > >> join collapse limit, or when we really know more about the subquery than > >> the optimizer and have something really specific there . So in general I > >> would not want the default functionality to change all of the queries we > >> have already written with this in mind. I do however like the idea of this > >> feature being an option, but I would question whether it perhaps worked the > >> other way around where you have to mark a CTE as not being a fence. > > > This essentially has been discussed already: > > http://archives.postgresql.org/message-id/5351711493487900%40web53g.yandex.ru > > My read of the concensus (in which I am in the majority, so I might be > > biased) is that we do want inlining to be the default. We were thinking > > that it'd be necessary to provide a way to force inlining on the SQL > > level for individual CTEs. > > We can't inline wCTEs (those containing insert/update/delete) without > risk of semantics change.
Right. > I'd also not favor changing the semantics for CTEs that are read more > than once by the parent query. I think medium term it'd be good to do a cost based analysis for that. I think it's fine to not do that immediately, but we should imo keep that in mind. > However, a singly-referenced SELECT CTE could reasonably be treated as > equivalent to a sub-select-in-FROM, and then you would have the same > mechanisms for preventing inlining as you do for those cases, > e.g. OFFSET 0. And sticking in OFFSET 0 would be backwards-compatible > too: your code would still work the same in older releases, unlike if > we invent new syntax for this. I still think this is just doubling down on prior mistakes. > >> Curious what other RDBMSs do here? > > > They largely inline by default. > > Even for multi-referenced CTEs? I don't know. Greetings, Andres Freund
