I am running an area intersection query on two large multipolygon tables (a few million rows).
SELECT r.geom FROM public.my_table r WHERE EXISTS ( SELECT gid FROM public.my_table_intersection WHERE r.class = '2' AND st_intersects(r.geom, geom) AND ST_IsValid(r.geom) AND ST_IsValid(geom) AND (st_area(st_intersection(r.geom, geom))/st_area(r.geom)) > 0.1 );
The code appears to work with small test files but I have left it for ages with my main files with no result yet. I know that such processing on very large datasets can be very slow, is this sort of operation within PostGis's capabilities or am I pushing it too far? Is there perhaps an alternative way of doing such processing on very large data-sets? would ArcGIS be quicker?
EDIT: The table row size is 3.7 million for my_table and then 2.1 million for my_table_intersection with indexes on geom for both.
The query plan is:
"Nested Loop Semi Join (cost=0.41..50013102.40 rows=33 width=275)" " -> Seq Scan on my_table r (cost=0.00..1153671.74 rows=295608 width=275)" " Filter: ((modal_clas = 3::numeric) AND st_isvalid(geom))" " -> Index Scan using my_table_intersection_geom_gist on my_table_intersection (cost=0.41..165.22 rows=8 width=277)" " Index Cond: (r.geom && geom)" " Filter: (st_isvalid(geom) AND _st_intersects(r.geom, geom) AND ((st_area(st_intersection(r.geom, geom)) / st_area(r.geom)) > 0.1::double precision))"