[postgis-users] large intersection and resulting query plan

[Dylan Beaudette]

Hi,

I am performing a basic intersection between two very large geometries. The 
resulting query plan looks like this:

 Nested Loop  (cost=0.00..2761762.20 rows=429619 width=20202)
   Join Filter: _st_intersects(state_lu.wkb_geometry, m_polys.wkb_geometry)
   ->  Seq Scan on state_lu  (cost=0.00..13467.93 rows=198693 width=1158)
   ->  Index Scan using mapunit_spatial_idx on mapunit_poly m_polys  
(cost=0.00..13.66 rows=3 width=19044)
         Index Cond: (state_lu.wkb_geometry && m_polys.wkb_geometry)
         Filter: (state_lu.wkb_geometry && m_polys.wkb_geometry)

Both tables have spatial indices, and I am using PostgreSQL 8.3.1 and PostGIS 
1.3.3. I am using the ST_Intersects() test as the join condition between 
these two tables.

The query looks like this:

SELECT m_polys.areasymbol, m_polys.mukey,
-- crops and their codes
class1 || '-' || subclass1 || '-' || specond1 || '-' || irr_typ1pa as c1,
class1 || subclass1 as c1_code,
class2 || '-' || subclass2 || '-' || specond2 || '-' || irr_typ2pa as c2,
class2 || subclass2 as c2_code,
class3 || '-' || subclass3 || '-' || specond3 || '-' || irr_typ3pa as c3,
class3 || subclass3 as c3_code,
-- save county name and land use survey year
lower(county) as county, year,
ST_Intersection(dwr.state_lu.wkb_geometry, m_polys.wkb_geometry) as 
wkb_geometry
FROM
mapunit_poly as m_polys
JOIN
dwr.state_lu
-- join condition: only those polygons which completely intersect
ON ST_Intersects(dwr.state_lu.wkb_geometry, m_polys.wkb_geometry);

Is there any way to make this query more efficient, or is the 'seq scan' in 
the query plan unavoidable?

Thanks!

Dylan


-- 
Dylan Beaudette
Soil Resource Laboratory
http://casoilresource.lawr.ucdavis.edu/
University of California at Davis
530.754.7341