[postgis-users] large intersection and resulting query plan

[Mark Cave-Ayland]

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.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063