Horton

Re: Early WIP/PoC for inlining CTEs

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeremy Finzel <finzelj@gmail.com>, Andrew Gierth <andrew@tao11.riddles.org.uk>, pgsql-hackers@postgresql.org
Date: 2018-07-24T23:54:16Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Prevent inlining of multiply-referenced CTEs with outer recursive refs.

  2. Allow user control of CTE materialization, and change the default behavior.

  3. Split QTW_EXAMINE_RTES flag into QTW_EXAMINE_RTES_BEFORE/_AFTER.

  4. document when PREPARE uses generic plans

Hi,

On 2018-07-24 19:49:19 -0400, Tom Lane wrote:
> Andres Freund <andres@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 <andrew@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