Thanx for all the replies. The only thing I haven't tried is using the EXECUTE method in my plpgsql function. I did think of that, but it just seems so wrong to have to use such a workaround.
Unfortunately I have to use plpgsql because I need to process the results afterwards. I agree with Mark in that the planner is making the wrong choice. Is there a way I can tell the planner what index to use? The frustrating thing is that I've written lots of similar functions before and never had this issue. Obe, Regina wrote: > 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 > > > -- Christo Du Preez Senior Software Engineer Mecola IT Mobile: +27 [0]83 326 8087 Skype: christodupreez Website: http://www.locateandtrade.co.za _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
