[postgis-users] Need help optimizing query

[Pedro Doria Meunier]

Hi All,

I have the following query:

SELECT name, road_type, 
(SELECT distance(transform(geomfromtext('POINT(-16.803986 
32.671131)',4326),32629), transform(geometry,32629)) 
WHERE 
transform(geometry,32629) && 
setsrid(expand(transform(geomfromtext('POINT(-16.803986 
32.671131)',4326),32629),20),32629)) as dist
from my_roads 
order by dist
LIMIT 1

(Note: this query is translated to "give me the road name and type for the 
nearest road up to 20 metres")

This takes ~>90ms

Explain Analyze tells me:
"Limit  (cost=3347.93..3347.93 rows=1 width=178) (actual time=90.404..90.404 
rows=1 loops=1)"
"  ->  Sort  (cost=3347.93..3383.30 rows=14149 width=178) (actual 
time=90.403..90.403 rows=1 loops=1)"
"        Sort Key: (subplan)"
"        ->  Seq Scan on my_roads  (cost=0.00..1063.47 rows=14149 width=178) 
(actual time=2.489..79.937 rows=14149 loops=1)"
"              SubPlan"
"                ->  Result  (cost=0.01..0.02 rows=1 width=0) (actual 
time=0.004..0.004 rows=0 loops=14149)"
"                      One-Time Filter: (transform($0, 32629) 
&& '0103000020757F00000100000005000000000000202C670CC100000000FBC84B41000000202C670CC1000000200FC94B4100000000EC650CC1000000200FC94B4100000000EC650CC100000000FBC84B41000000202C670CC100000000FBC84B41'::geometry)"
"Total runtime: 90.781 ms"

Note the SEQ SCAN ...
I *do* have all the indicies created (gist and projection wise)

Is this a sub-select thing? (Meaning using it bars the use of the indicies...)

All pointers welcome from the gurus out there... ;-)

Kind regards,

Pedro Doria Meunier
GSM: +351961720188
Skype: pdoriam

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081215/38f40f11/attachment.pgp>