Christo, If its not too forward to ask of you. Could you let us know the full plpgsql create function you have and also an example sql statement in which you are calling the function? Its kind of hard to think of workarounds when only seeing part of the story. Thanks, Regina
________________________________ From: [EMAIL PROTECTED] on behalf of Christo Du Preez Sent: Thu 5/31/2007 1:27 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] function speed 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 <http://www.locateandtrade.co.za/> _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
