This is probably a really basic question... my ST_Within or ST_Intersects
selecting points in a continent are way too slow (both take upward of 200 secs).
SELECT Count(c_id)
FROM c, continents n
WHERE ST_Intersects(c.the_geom, n.the_geom) AND
n.continent = 'North America';
Both tables have gist indexes on the geometries. The above query has the
following plan
"Aggregate (cost=9.66..9.67 rows=1 width=4)"
" -> Nested Loop (cost=0.00..9.66 rows=1 width=4)"
" Join Filter: _st_intersects(c.the_geom, n.the_geom)"
" -> Seq Scan on continents n (cost=0.00..1.10 rows=1 width=32)"
" Filter: ((continent)::text = 'North America'::text)"
" -> Index Scan using pbdb__collections_the_geom on collections c
(cost=0.00..8.30 rows=1 width=104)"
" Index Cond: (c.the_geom && n.the_geom)"
The table c has approx 120K rows, and the continents table has 8
rows.Suggestions on how I can improve this? Yes, the computer is otherwise very
swift and modern.
--
Puneet Kishor
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
