<HTML dir=ltr><HEAD><TITLE>Re: [postgis-users] function speed</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3086" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText53462 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Christo,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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?</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV><FONT face=Arial size=2></FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>Its kind of hard to think of workarounds when only seeing part of the story. </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Thanks,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>Regina</FONT></DIV>
<DIV dir=ltr><BR></DIV>
<DIV dir=ltr>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr><FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Christo Du Preez<BR><B>Sent:</B> Thu 5/31/2007 1:27 PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] function speed<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Thanx for all the replies.<BR><BR>The only thing I haven't tried is using the EXECUTE method in my plpgsql<BR>function. I did think of that, but it just seems so wrong to have to use<BR>such a workaround.<BR><BR>Unfortunately I have to use plpgsql because I need to process the<BR>results afterwards. I agree with Mark in that the planner is making the<BR>wrong choice. Is there a way I can tell the planner what index to use?<BR><BR>The frustrating thing is that I've written lots of similar functions<BR>before and never had this issue.<BR><BR>Obe, Regina wrote:<BR>> If you can write your function as an sql language function instead of<BR>> plpgsql function you would be better off. I had similar problems before<BR>> and it turned out that writing it as a plpgsql function did not utilize<BR>> the indexes where as converting the function to use sql language, the<BR>> planner was then able to plan better and utilize the indexes.<BR>><BR>> -----Original Message-----<BR>> From: postgis-users-bounces@postgis.refractions.net<BR>> [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of<BR>> Christo Du Preez<BR>> Sent: Thursday, May 31, 2007 11:47 AM<BR>> To: PostGIS Users Discussion<BR>> Subject: [postgis-users] function speed<BR>><BR>> I wonder if someone can perhaps shed so light on a very strange issue.<BR>><BR>> I'm not sure if this is a postgis or postgres question.<BR>><BR>> I've written a function that takes a geometry and a couple of other<BR>> arguments then in the function I simply select from a table with 6.5mil<BR>> rows using<BR>><BR>> WHERE the_geom && $1<BR>> AND distance($1 ,g.the_geom) < $2<BR>><BR>> It takes forever, but if I only execute the select statement it's very<BR>> fast.<BR>><BR>> WHERE the_geom && geomfromtext('POLYGON((-180.0 -90.0, -180.0 90.0,<BR>> 180.0 90.0, 180.0 -90.0, -180.0 -90.0))')<BR>> AND distance(geomfromtext('POLYGON((-180.0 -90.0, -180.0 90.0, 180.0<BR>> 90.0, 180.0 -90.0, -180.0 -90.0))'),the_geom) < 201.25<BR>><BR>> Now the really strange thing is when I hardcode the function it's also<BR>> fast.<BR>><BR>> Is there someone that could shed some light on this?<BR>><BR>><BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>> -----------------------------------------<BR>> The substance of this message, including any attachments, may be<BR>> confidential, legally privileged and/or exempt from disclosure<BR>> pursuant to Massachusetts law. It is intended<BR>> solely for the addressee. If you received this in error, please<BR>> contact the sender and delete the material from any computer.<BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR>> <BR><BR>--<BR>Christo Du Preez<BR><BR>Senior Software Engineer<BR>Mecola IT<BR>Mobile: +27 [0]83 326 8087<BR>Skype: christodupreez<BR>Website: <A href="http://www.locateandtrade.co.za/">http://www.locateandtrade.co.za</A><BR><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>