[postgis-users] optimize query

[Rob Tester]

Add a where clause on the indexed column (presumably f.geometry).

 

WHERE f.geometry&& geomfromtext('POINT(-9.1533 38.69686)',4326) 

 

Rob.

 

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Pedro
Doria Meunier
Sent: Friday, March 09, 2007 9:04 AM
To: 'PostGIS Users Discussion'
Subject: [postgis-users] optimize query
Importance: High

 

Hi List,

 

I'm once again in need of guru advice. J

 

This is the query:

 

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?

 

Txs in advance!

 

Pedro Doria Meunier.

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070309/3096ac6b/attachment.html>