On Dec 20, 2011, at 9:48 PM, Martin Davis wrote: > For more detail check out this thread on the same issue: > > http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html
Thanks. Chopping up my coverage into hundreds of small regions is the last avenue I want to try. Going by the text of that email, it seems that "few, large, regions with many vertices (may be) the problem." I will try generalizing my continents so that I have "few, large regions with *very few* vertices" and see if that speeds up the SELECTs. > > On 12/20/2011 5:28 PM, Puneet Kishor 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.k...@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 >> _______________________________________________ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> ----- >> No virus found in this message. >> Checked by AVG - www.avg.com >> Version: 2012.0.1890 / Virus Database: 2109/4692 - Release Date: 12/20/11 >> >> > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
