[postgis-users] Slow spatial join?

[Anders Larsson]

Hello!

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
rows=2 loops=609)
       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?

/Anders Larsson
Sweden