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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [email protected] 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
