On 5 May 2017 06:04, "Andreas Karlsson" <andr...@proxel.se> wrote:
On 05/04/2017 06:22 PM, Andrew Dunstan wrote: > 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; > I do not know the planner that well, but I imagined that when we remove the optimization fence that one would be evaluated similar to if it had been a lateral join, i.e. there would be no extra function calls in this case after removing the fence. Sort of. PostgreSQL has a wart around (x).* expansion where it's essentially macro-expanded into (x).a, (x).b, (x).c, ... Now, if x is a function call, PG will merrily execute it n times for its n output columns. Andres is working on fixing this. And it's trivially worked around with a lateral query ; the above would be better written as select (x).* from mytable cross join lateral json_populate_record(null::mytype, myjson) as x; So this example just abuses our optimiser hint behaviour for CTEs to avoid solving a planner issue (why project policy is against hints). But there's already a solution. I'm finding it increasingly hilarious watching people vociferously defending their one precious (semi-documented) query/optimiser hint in PostgreSQL. The one we don't admit is a hint, but treat as one by avoiding optimising across it when it's safe to do so. We can't remove or change our precious hint because we need it to solve production issues. But we don't have hints because then people wouldn't report planner/optimiser issues, would lock in bad plans and then complain about it, etc. Just like what's happening here. And people are leaping to defend it, lest we risk exposing performance issues by changing anything, even though all we're doing is documenting what is already so. Hey. Crazy idea for backward compat to address Tom's complaint that adding explicit syntax would require people who wanted the old behaviour to make their queries incompatible with pg10 and below. Add the "MATERIALIZED" keyword or whatever. The back patch the keyword as a no-op, since that's what we already do in back branches. I can't see anything that could possibly break in that context so long as we only go as far back as it was already a keyword elsewhere. We could at least add it to pg10.
