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