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: Andreas Karlsson <andreas@proxel.se>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Merlin Moncure <mmoncure@gmail.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>, Robert Haas <robertmhaas@gmail.com>, 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-27T05:16:34Z
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:

 >> I also thought about that. But what I thought about it on reflection
 >> was: if the user explicitly wrote NOT MATERIALIZED, then we should
 >> assume they mean it.

 Tom> Ah, but the example I gave also had MATERIALIZED on the inner WITH.
 Tom> Why should the user not also mean that?

The inner WITH does get materialized, it just gets materialized twice.
If the user doesn't want that, then they can avoid using NOT MATERIALIZED
on the outer CTE; but if we force it to materialize the outer query,
then that leaves the user without recourse.

Consider a case like:

create view foo as
  with s as materialized (select something)
  select * from large l
  where l.foo in (select * from s) or l.bar in (select * from s);

with
  bar as not materialized (select * from foo)
select * from bar b1, bar b2 where b1.col='x' and b2.col='y';

In a case like this, materializing "s" twice may be far less expensive
than materializing the result of "select * from large..." without
benefit of pushed-down quals.

-- 
Andrew (irc:RhodiumToad)