Re: [HACKERS] CTE inlining

On 2017-05-04 09:34:19 -0700, David G. Johnston wrote:
> On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan <
> andrew.dunstan@2ndquadrant.com> wrote:
> 
> >
> > Yeah, the idea that this won't cause possibly significant pain is quite
> > wrong. Quite by accident I came across an example just this morning where
> > rewriting as a CTE makes a big improvement.
> >
> > I wrote this query:
> >
> >     select (json_populate_record(null::mytype, myjson)).*
> >     from mytable;
> >
> >
> > It turned out that this was an order of magnitude faster:
> >
> >     with r as
> >     (
> >        select json_populate_record(null::mytype, myjson) as x
> >        from mytable
> >     )
> >     select (x).*
> >     from r;

That's a very specific issue, that you can just as well solve with CTEs
though.  IIRC Tom and I were discussing changing this - independently of
anything CTE releated - so it doesn't end up calling the function
multiple times. We e.g. could now quite easily put the
json_populate_record(null::mytype, myjson) into something *like* a
nodeProjectSet node, and handle the column accesses via the nromal
projection.


> ​Except I suspect we at least have a chance to detect the above and not
> de-optimize it by evaluating "json_populate_record" once for every column
> in mytype.

That's what happens with the non CTE version - don't think it's super
likely we'd end up doing that for CTEs; I'd even consider it a bug.

Greetings,

Andres Freund