On Fri, Feb 22, 2019 at 3:33 PM Regina Obe <l...@pcorp.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
