Re: Early WIP/PoC for inlining CTEs

Hi,

On 2018-07-24 19:49:19 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > 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:
> >>> 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