[postgis-users] large intersection and resulting query plan

[Dylan Beaudette]

On Tuesday 02 September 2008, Mark Cave-Ayland wrote:
> Dylan Beaudette wrote:
> > 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
>
> Hi Dylan,
>
> Unfortunately I think this is unavoidable when using a join with 2
> column arguments; the way I see it is that you have to scan through at
> least 1 column completely in order to determine whether it has any
> intersection with the other (Hashing and sorting for Hash Joins/Merge
> Joins cannot really help you here in multi-dimensional space).
>
> If there is an extra constraint you can add to a WHERE clause based upon
> another (non-geometry) field value, you may find that things can start
> to work better...
>
>
> ATB,
>
> Mark.

Thanks for the tips Mark. In the end the query took over 8 hours, which is not 
all that long considering the size.

Cheers,

Dylan

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