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

[Puneet Kishor]

On Dec 20, 2011, at 7:44 PM, Brian Stempin wrote:

> I've been out of the loop for a bit, but try this:
> 
> SELECT Count(c_id)
>       FROM c, continents n
>       WHERE c.the_geom && n.the_geom AND
>              ST_Intersects(c.the_geom, n.the_geom) AND
>               n.continent = 'North America';
> 
> The key line being:
> c.the_geom && n.the_geom
> 


If you see the EXPLAIN QUERY PLAN I posted (see below), ST_Intersects automatically does an && check. Nevertheless, I tried your specific suggestion above, and it took 201 seconds. No joy.




> Check out item 7.7:
> http://postgis.refractions.net/documentation/manual-1.5/reference.html
> 
> The && uses the bounding boxes to check if they overlap.  This is a very
> quick operation that might eliminate the need to further examine if two
> geoms are intersecting.
> 
> HTH,
> Brian
> 
> On Tue, Dec 20, 2011 at 8:28 PM, Puneet Kishor <punk.kish at gmail.com> wrote:
> 
>> 
>> 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
>>