I am new to PostGIS and SQL in general. I am running a query on polygons which, I hope, only selects distinct geometries and then repair (ST_MakeValid()
) those distinct geometries. Thereby removing duplicate geometries in the process of fixing invalid geometries. However, the query takes really long to run (days). This is the SQL statement:
CREATE TABLE schema.table_geomfix AS
SELECT gid,id, ST_MakeValid(geom) as geom
FROM schema.table_polygons
WHERE geom IN (SELECT DISTINCT geom FROM schema.table_polygons);
Running the two select statements separately is quick. About 1 minute for the makevalid
and 15 seconds on the distinct
statement.
- Why does it take so long to run the combined statement?
- Any advice for improving the query in order to make it faster?
NOTE: There is an index on the geom
column.
explain (analyze, verbose)
– a_horse_with_no_name Jan 28 '16 at 10:01distinct
in the sub-query is useless and can be removed (but Erwin's query is even better) – a_horse_with_no_name Jan 28 '16 at 10:03