I have three spatial datasets - mitch_ibra has about 2700 polygons, hev has ~1,200,000 polygons and prop_p1 with about 180,000. For each polygon in mitch_ibra I want to know how many polygons of prop_p1 are inside, with a minimum size of 5ha or overlaps the mitch_ibra polygon by 5ha. Finally I want the number of these polygons intersect polygons from the hev table.
WITH region AS (SELECT geom FROM mitch_ibra WHERE sub_name_7 = 'Tingha Plateau' AND lscape_nam = 'Dumaresq Channels') SELECT COUNT(props.id) FROM (SELECT DISTINCT initprop.id FROM (SELECT id,geom FROM prop_p1 WHERE st_area(ST_transform(geom,3308))>50000) AS initprop JOIN region ON st_intersects(initprop.geom,region.geom), (SELECT hev.geom FROM hev JOIN region ON ST_Intersects(hev.geom,region.geom)) AS hevsel WHERE CASE WHEN ST_Within(initprop.geom,region.geom) THEN ST_Area(ST_Transform(initprop.geom,3308)) ELSE ST_Area(ST_Transform(ST_Intersection(initprop.geom,region.geom),3308)) END > 50000 AND ST_Intersects(initprop.geom,hevsel.geom)) AS props
Above is my query so far, which gives me a processing time of about 658s for a region with 1300 polygons selected in the prop_p1 subquery. All have tables have GIST indexes. How can I optimize my indexes/query to reduce processing time. Is this as good as I'm going to get? (I have to run this over 300 different selections in the WITH statement)