[postgis-users] Slow spatial join?

[Paul Ramsey]

That's not a  lot of data, and your explain shows that the index  
portion of the problem finished up pretty fast. That just leaves your  
regions. Are they really big? (lots and lots of vertices?)

Try using Contains(country.the_geom, occurencies.the_geom) instead of  
Within and see what changes.

P

On 20-May-07, at 1:36 AM, Anders Larsson wrote:

> 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
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users