From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, 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:39:11 |
Message-ID: | 20170504163911.twb2otkbwfz52onx@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2017-05-04 09:34:19 -0700, David G. Johnston wrote:
> 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;
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-05-04 16:48:25 | Re: statement_timeout is not working as expected with postgres_fdw |
Previous Message | David G. Johnston | 2017-05-04 16:34:19 | Re: CTE inlining |