[postgis-users] Re: Transform Performance

strk at refractions.net strk at refractions.net
Thu Oct 27 01:01:34 PDT 2005


On Wed, Oct 26, 2005 at 02:17:45PM -0600, Charlie Savage wrote:
> 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.

I was using just that:
	transform_geometry(geometry, text, text, integer)

In my mail I suggested an OPTIMAL version substituting
get_proj4_from_srid() and find_srid() calls with actual SRS
values.

Note that for mapserver use, the optimal would really be letting
the transform happen on client side, using PROJECTION option.
This would also allow the DBMS to use spatial index.

--strk;



More information about the postgis-users mailing list