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 
    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)"

2 Answers 2


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.

enter image description here


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.