[postgis-users] Re: Transform Performance

[Charlie Savage]

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
AS $$
BEGIN
	RAISE INFO 'testme: %', value;
	RETURN value;
END;
$$
LANGUAGE plpgsql IMMUTABLE;


I thought, but was wrong, that in this case the function would only be 
called once:

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(..)),
> 		get_proj4_from_srid(4326));
> 

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.

Thanks,

Charlie