Re: Early WIP/PoC for inlining CTEs

On 2018-07-24 18:03:43 -0500, Jeremy Finzel wrote:
> On Tue, Jul 24, 2018 at 5:28 PM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)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