[postgis-users] speeding up simple pt-in-poly lookups

[Puneet Kishor]

On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote:

> Chop up the continents into smaller pieces.
> 


hmmm... I am not sure I understand the above. And then what? UNION each smaller piece query? 


> On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor <punk.kish at gmail.com> wrote:
>> 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