Re: Early WIP/PoC for inlining CTEs

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. I'd also not favor changing the semantics
for CTEs that are read more than once by the parent query. 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.

So if we're going to go in this direction, that's pretty much how
I'd envision it working.

>> Curious what other RDBMSs do here?

> They largely inline by default.

Even for multi-referenced CTEs?

regards, tom lane