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: > > > About a year ago I was briefly in discussion/collaboration with Adam Sah > > regarding the topic of inlining CTEs into the query rather than treating > > them as optimization barriers. We didn't take it very far (he sent me > > some stuff, I wrote some stuff and sent it back, things kind of got > > dropped at that point); but there's been some recent discussion of this > > and some people have expressed an interest in seeing the code. > > > > So I'm posting the parts that I wrote for the benefit of anyone wanting > > to pick up the issue again. The assumption of this code is that some > > form of syntax would exist to mark materialized CTEs and set the > > "ctematerialized" flag. > > > > I haven't rebased this or tested it since last year; this patch is > > against b81eba6a65. > > > > 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. > > > > -- > > Andrew (irc:RhodiumToad) > > > > 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. > Curious what other RDBMSs do here? They largely inline by default. Greetings, Andres Freund
