[postgis-users] function speed

[Mark Cave-Ayland]

On Thu, 2007-05-31 at 17:47 +0200, Christo Du Preez wrote:
> 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?

My guess is that what's happening is due to the fact that PostgreSQL
compiles your function as it's loaded using CREATE FUNCTION. At this
point in time, the planner has no idea of the actual value/range of
values you are going to pass into it, and guesses based on its internal
statistics which execution plan is going to be the best to use for that
function. Obviously in your case it is making the wrong choice.

If you are using PL/PGSQL then the easiest work around is to generate
the SQL statement as a string and then EXECUTE it - this causes the
statement to be re-planned for every execution.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk