[postgis-users] function speed

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net 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: 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070531/a1ef4ded/attachment.html>