From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Serge Rielau <serge(at)rielau(dot)com>, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>, Craig Ringer <craig(dot)ringer(at)2ndquadrant(dot)com>, Ilya Shkuratov <motr(dot)ilya(at)ya(dot)ru>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Mario Becroft <mb(at)true(dot)group> |
Subject: | Re: CTE inlining |
Date: | 2017-05-04 16:34:19 |
Message-ID: | CAKFQuwawh+p0-XR6_wcOuHsi7ppk7_=q8ZRLsD4qbd0NDxyGwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2017-05-04 16:39:11 | Re: CTE inlining |
Previous Message | Andrew Dunstan | 2017-05-04 16:22:36 | Re: CTE inlining |