I try to do 2 spatial queries to detect overlap, using the function ST_Overlaps to detect a partial overlap and the function ST_Equal to detect duplicate polygons. Both are extremely slow on my PostGIS database of +60k polygons. It takes up to an 1 hour to run each query... I use an INDEX to speed up the process, but it seems this is barely make the process more efficient. This is my query with ST_Equals to detect duplicate polygons:
CREATE INDEX IF NOT EXISTS indexpol ON tree_registration_areas USING gist(polygon);
cluster tree_registration_areas using indexpol;
WITH duplicate_polygons AS (
SELECT
a.identifier AS identifier_a,
b.identifier AS identifier_b
FROM tree_registration_areas a, tree_registration_areas b
WHERE ST_EQUALS(a.polygon::geometry, b.polygon::geometry)
AND a.identifier != b.identifier)
Any idea what could be the reason for the slowness of my queries? It cannot be that the 60k polygons cause this issue? If so, is there a way to do it more efficient?