Horton

Re: Early WIP/PoC for inlining CTEs

Andrew Gierth <andrew@tao11.riddles.org.uk>

View thread
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Robert Haas <robertmhaas@gmail.com>, Andreas Karlsson <andreas@proxel.se>, Andres Freund <andres@anarazel.de>, Thomas Munro <thomas.munro@enterprisedb.com>, David Fetter <david@fetter.org>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2019-02-03T08:14:44Z
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

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 Tom> After further reflection I really don't like Andrew's suggestion
 Tom> that we not document the rule that multiply-referenced CTEs won't
 Tom> be inlined by default. That would be giving up the principle that
 Tom> WITH calculations are not done multiple times by default, and I
 Tom> draw the line at that. It's an often-useful behavior as well as
 Tom> one that's been documented from day one, so I do not accept the
 Tom> argument that we might someday override it on the basis of nothing
 Tom> but planner cost estimates.

The case that springs to mind is when a CTE with grouping is then joined
multiple times in the main query with different conditions. If the
planner is able to deduce (e.g. via ECs) that restrictions on grouped
columns can be pushed into the CTE, then inlining the CTE multiple times
might be a significant win. But if that isn't possible, then inlining
multiple times might be a significant loss.

If such a query is made into a view, then (given your position) the
decision of whether to inline has to be made at view creation time,
which doesn't seem desirable (even if we have to put up with it for the
present).

-- 
Andrew (irc:RhodiumToad)