Re: BUG #12869: PostGIS 2.2 can't compile against 9.5 dev branch

Sorry for cross post. I've cc'd PostGIS dev group on this for input -- For
PostGIS folks - link to related issue that started this conversation here:
http://trac.osgeo.org/postgis/ticket/3080

>> "Paragon Corporation" <lr(at)pcorp(dot)us> writes:
>> Guys I tried increasing cost with the ST_Reclass function to 2000 and it
didn't help. The function is called for every single output. The only
thing that helps is setting this to VOLATILE as Tom suggests. I guess we

>> can do that as some sort of fix for these kinds of functions though I'm
not sure if that would cause other issues.

> I think that would be a very bad idea; it would foreclose optimizations
that you *do* want. Much better,
> if you are relying on single-evaluation behavior for a non-volatile
function, is to use one of the documented optimization fences: either a CTE,
or an OFFSET 0 in a sub-select.

The CTE / OFFSET solution requires the end-user to do this, which means a
lot of people using PostGIS may suffer severe performance issues after
upgrading to PostgreSQL 9.5 unless they change their code which is something
I'd like to avoid.

> TBH, this particular example does not fill me with concern, because
> (a) it's obviously artificial, and (b) you'd really never notice if the
function got evaluated 3 times not once, if you hadn't put in that NOTICE.
> I grant that there may be cases where you're worried about avoiding
multiple evaluations *per row* over some large number of rows, but to
discuss that type of problem we'd have to see what your coding

> habits are like for such cases. An immutable function being fed
constants is not going to create that type of problem.

> regards, tom lane

The particular constant example I presented was simply to provide an example
that exhibits this behavior without having to require PostGIS to replicate
it.

The more common use case (the ones I am really worried about are cases where
we have a function that takes a large composite object and outputs another
which then gets passed to another PostGIS function that cuts off small
pieces.

Here one that come to mind

SELECT ST_GeometryN(newgeom,1) As geom1, ST_GeometryN(newgeom,2) As geom2
FROM (
SELECT ST_Simplify(geom,25) As newgeom
FROM sometable) As foo;

If I am not mistaken -- please correct me if I am wrong. The above example
would cause ST_Simplify to be called for each ST_GeometryN. If that were
the case, this would be a huge PostGIS performance killer for a lot of folks
as this pattern is very common and we have a lot of expensive functions that
do this kind of thing.

-- PostGIS raster is filled with a ton of these. The ST_Reclass is just one
example of these (and one that just happened to be tripped up by this change
because it happened to have a NOTICE in it) -- things like ST_MapAlgebra
that are very intensive functions that output often get used later by other
functions concern me even more.

Thanks,
Regina