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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
