intersection in postgis takes hours and hours to run, why?

Question

I have a table with approximately 500,000 records that I am trying to intersect with a table that has about 30,000 records. It was running for 11 hours until I finally stopped it.

Why would this take so long?

I have spatial indexes on both tables and they are in UTM. I have noticed in the past when I have a very large amount of vertices things can run very slowly and so on occasion I will simplify. In this case the 30K record table has about 3.5 million points, whereas the 500K table has about 7 million. I can't simplify the 500K records table without losing the level of accuracy I need and I'm not sure simplifying the the 30K table will help. Below is the query I am running.

Is there something I am missing in terms of it taking so long to run?

I am running postgresql v9.5 with postgis v2.2.

SELECT p.gid, w.wetland_ty, 
CASE WHEN ST_CoveredBy(p.geom, w.geom) 
   THEN p.geom 
   ELSE 
    ST_Intersection(p.geom, w.geom) END as geom
into suffolk_co_ny_prcl_data_wetlands_intsct
FROM suffolk_co_ny_prcl_data AS p, suffolk_co_ny_wetlands AS w
where ST_Intersects(p.geom, w.geom);

Here are the results of EXPLAIN:

"Nested Loop  (cost=0.28..7568923.81 rows=11749404 width=415)"
"  ->  Seq Scan on suffolk_co_ny_prcl_data p  (cost=0.00..304746.03 rows=1229103 width=265)"
"  ->  Index Scan using suffolk_co_ny_wetlands_geom_idx on suffolk_co_ny_wetlands w  (cost=0.28..1.10 rows=1 width=150)"
"        Index Cond: (p.geom && geom)"
"        Filter: _st_intersects(p.geom, geom)"

Answer 1

This SQL query is just fine as it is. I use the same syntax myself for intersecting layers via PostGIS. Seek performance improvements elsewhere in your system.

I downloaded the wetlands shapefile and then scraped the 50,000 parcels off the Suffox county AGS server. I then popped them into PostGIS, transformed the wetlands from 5070 to 2263 and ran the query.

It took seven minutes on my machine resulting in 3,331 parcel/wetland intersections.

Answer 2

Three optimizations come to my mind:

• As already mentioned, get rid of `ST_CoveredBy as it's probably hurting rather than helping
• Make sure there are no indices on suffolk_co_ny_prcl_data_wetlands_intsct table, build them after inserting all the data
• If you don't mind losing your data on database crash set the suffolk_co_ny_prcl_data_wetlands_intsct table to UNLOGGED, that's gonna speed the inserts up a bit as well