[postgis-users] function speed

[Christo Du Preez]

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