[postgis-users] function speed

[Obe, Regina]

If you can write your function as an sql language function instead of
plpgsql function you would be better off.  I had similar problems before
and it turned out that writing it as a plpgsql function did not utilize
the indexes where as converting the function to use sql language, the
planner was then able to plan better and utilize the indexes. 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Christo Du Preez
Sent: Thursday, May 31, 2007 11:47 AM
To: PostGIS Users Discussion
Subject: [postgis-users] function speed

I wonder if someone can perhaps shed so light on a very strange issue.

I'm not sure if this is a postgis or postgres question.

I've written a function that takes a geometry and a couple of other
arguments then in the function I simply select from a table with 6.5mil
rows using

    WHERE the_geom && $1
      AND distance($1 ,g.the_geom) < $2

It takes forever, but if I only execute the select statement it's very
fast.

    WHERE the_geom && geomfromtext('POLYGON((-180.0 -90.0, -180.0 90.0,
180.0 90.0, 180.0 -90.0, -180.0 -90.0))')
    AND distance(geomfromtext('POLYGON((-180.0 -90.0, -180.0 90.0, 180.0
90.0, 180.0 -90.0, -180.0 -90.0))'),the_geom) < 201.25

Now the really strange thing is when I hardcode the function it's also
fast.

Is there someone that could shed some light on this?


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.