On 2017-05-04 09:34:19 -0700, David G. Johnston wrote: > On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan < > andrew.duns...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
