Re: [HACKERS] CTE inlining

On 05/04/2017 12:34 PM, David G. Johnston wrote:
> On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan
> <andrew.dunstan@2ndquadrant.com
> <mailto: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;
>
>
> ​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.


Hadn't though about LATERAL, good point. Still, there will be other cases.

cheers

andrew


-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services