[postgis-users] optimize query

[Paul Ramsey]

Pedro Doria Meunier wrote:

> select freguesia, distance(geomfromtext('POINT(-9.1533 
> 38.69686)',4326),f.geometry) as thedist
> from freguesias as f
> order by thedist
> limit 1;

> as you can see it returns the closest polygon to a given point.
> Problem: 4050 (**detailed**) polygons
  > exec time: 907 ms

> The Gist index was created, btw…
> Is there any way to optimize this query and avoid the dreaded seq scan?

Not in generality. If, however, you can say "the closest point will 
always be less than 'X' units from the point", then you can stick a 
bounding box index clause in to only scan the points within that 
distance. This approach can then be turned into a procedural hack that 
first tries with a small X, then doubles it if no results are returned, 
etc, until a result is returned.

-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632