[postgis-users] function speed

[Dmitri Maximovich]

Christo,

Select statement probably fetches only first N rows to show you on the 
screen and therefore it's appears fast. When you're putting condition on 
the distance Postgres actually should go through (almost) every single 
row to calculate it before returning result to you.

Generally you should be using additional condition on BOX3D when 
selecting based on distance from the given point, as described on 
Postgis documentation, then Postgres could take advantage of GIS index 
and it's much faster.

That's my limited understanding. Hope gurus will correct me if I'm wrong.

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?
> 
>