Re: BUG #12869: PostGIS 2.2 can't compile against 9.5 dev branch - Mailing list pgsql-bugs

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@pcorp.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