Re: CTE inlining

On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan <
andrew(dot)dunstan(at)2ndquadrant(dot)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;
>

​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.

The now idiomatic solution​ to the above is to use LATERAL so the above CTE
is no longer actually a required workaround.

David J.