"Paragon Corporation" <lr@pcorp.us> writes:
> 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.
Indeed, and that is exactly what happens in every PG release for time out
of mind, if that's actually how you're coding it without using any method
that would prevent flattening of the subquery. Check it with EXPLAIN
VERBOSE if you doubt that. The patch I committed two weeks ago has no
impact whatsoever on this example, because that's not a table-free subquery.
I suspect that (a) you are using OFFSET 0 in the subquery, (b) you already
have ST_Simplify marked as volatile, or (c) the performance hit from
multiple evaluations isn't as drastic as you think.
regards, tom lane