Re: CTE inlining

On 5 May 2017 06:04, "Andreas Karlsson" <andreas(at)proxel(dot)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.