[postgis-users] Slow spatial join?
anla2973 at student.uu.se
Sun May 20 01:36:44 PDT 2007
I am trying to join some points with regions.
I have one table(occurrencies) with 900 points and another table(coutry)
with 600 regions.
I use following SQL to get a countryname bound to each point.
SELECT occurrencies.name, country.name
FROM occurrencies, country
WHERE occurrencies.the_geom && country.the_geom
AND within(occurrencies.the_geom, country.the_geom)
This takes 50 sek.
I have spatial index on both columns and have vacuum analyzed both tables.
If I do an "explain analyze" I get following result:
Nested Loop (cost=0.00..3715.18 rows=526 width=30) (actual
time=6.311..46717.773 rows=371 loops=1)
Join Filter: within("inner".the_geom, "outer".the_geom)
-> Seq Scan on country (cost=0.00..50.09 rows=609 width=13200)
(actual time=0.012..1.243 rows=609 loops=1)
-> Index Scan using occurrencies_spatial_index on
occurrencies(cost=0.00..6.00 rows=1 width=51) (actual time=3.427..10.884
Index Cond: (occurrencies.the_geom && "outer".the_geom)
Filter: (occurrencies.the_geom && "outer".the_geom)
Total runtime: 46718.838 ms
I am using Postgres version 8.1
Is this time OK or what am I doing wrong?
More information about the postgis-users