Re: CTE Changes in PostgreSQL 12, can we have a GUC to get old behavior

On Fri, Feb 22, 2019 at 3:33 PM Regina Obe <lr(at)pcorp(dot)us> wrote:
> Historically PostGIS functions haven't been costed right and can't be
> because they rely on INLINING of sql functions which gets broken when too
> high of cost is put on functions. We have a ton of functions like these
> that return composite objects and this above function is particularly
> expensive so to have it call that 10 times is almost guaranteed to be a
> performance killer.

This is good evidence that using the cost to decide on inlining is a
terrible idea and should be changed.

> I know there is a new MATERIALIZED keyword to get the old behavior, but
> people are not going to be able to change their apps to introduce new
> keywords, especially ones meant to be deployed by many versions of
> PostgreSQL.
>
> That said IS THERE or can there be a GUC like
>
> set cte_materialized = on;
>
> to get the old behavior?

Behavior changing GUCs *really* suck. If we add such a GUC, it will
affect not only PostGIS but everything run on the server -- and we
made this change because we believe it's going to improve things
overall. I'm really reluctant to believe that it's right to encourage
people to go back in the opposite direction, especially because it
means there will be no consistency from one PostgreSQL system to the
next.

I think there are probably other ways of fixing this query that won't
have such dramatic effects; it doesn't really seem to need to use
WITH, and I bet you could also tweak the WITH query to prevent
inlining. I also think Andres's question about why this gets inlined
in the first place is a good one; the (m).* seems like it ought to be
counted as a multiple reference.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company