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?
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
