[postgis-users] Re: Transform Performance
cfis at interserv.com
Wed Oct 26 13:17:45 PDT 2005
Very interesting. Following your lead I created this function and then
ran it through the scenarios you discuss below.
CREATE OR REPLACE FUNCTION
testme(value int) RETURNS int
RAISE INFO 'testme: %', value;
LANGUAGE plpgsql IMMUTABLE;
I thought, but was wrong, that in this case the function would only be
select testme(MyField) from MyTable;
I had hoped that Postgresql would cache the value of the input
parameter, see that testme had already been invoked for that parameter,
and skip the evaluation. However, it does not, the method get called
for every record. From the Postgresql docs (emphasis is mine):
"An IMMUTABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments forever. This category
allows the optimizer to pre-evaluate the function when a query calls it
with *constant* arguments."
> POSSIBLE OPTIMIZATIONS
> First optimization that comes to mind is reducing scans by making
> outer-level get_proj4_from_srid() calls take a constant argument:
> transform_geometry(g, get_proj4_from_srid(find_srid(..)),
Perhaps a quick simple way to do this is to create an overloaded version
of transform that takes both the source srs and targer srs as constants.
That would get rid of all the SRID calls, and all but two of the
get_proj4_from_srid calls. Thus it would be a lot faster, but not much
harder to use.
More information about the postgis-users