[postgis-users] PostGIS spatial query performance

[Alexander Pucher]

Hi,

I have a question concerning the performance of a spatial query in PostGIS.

-) I put all GMAP demo layers into my PostgreSQL/PostGIS database.
-) I created GIST indexes for all tables.

create index park_gist on park
using GIST (the_geom GIST_GEOMETRY_OPS)
.
.
.

-) Did vacuum analyze all tables.

OK, I want to query all parks in the GMAP demo that have a road running 
through them.

My SQL query:

SELECT park.*
FROM park, road
WHERE DISTANCE(park.the_geom, road.the_geom) < 2



EXPLAIN SELECT park.*
FROM park, road
WHERE DISTANCE(park.the_geom, road.the_geom) < 2

gives me the following output:

Nested Loop  (cost=0.00..8917.66 rows=23736 width=163)
  ->  Seq Scan on park  (cost=0.00..7.46 rows=46 width=131)
  ->  Seq Scan on road  (cost=0.00..170.48 rows=1548 width=32)


the query works fine and gives me the correct result after.....about 6 
minutes ( on my 1Ghz Notebook)!!

I expected the result to be there within seconds?!?

Something wrong with the indexes??

Any help is welcome.

regards
alex




-- 
________________________________________________________

Institut fuer Geographie und Regionalforschung
Universitaet Wien
Kartografie und Geoinformation

Departement of Geography and Regional Research
University of Vienna
Cartography and GIS

Universitaetstr. 7, A-1010 Wien, AUSTRIA

Tel: (+43 1) 4277 48644
Fax: (+43 1) 4277 48649
E-mail: alexander.pucher at univie.ac.at

FTP: ftp://ftp.gis.univie.ac.at
WWW: http://www.gis.univie.ac.at/karto
--------------------------------------------------------
Atlas of Eastern and Southeastern Europe: http://www.aos.ac.at
--------------------------------------------------------

M$ is not the answer. M$ is the question!
No is the answer -- Eric Naggum