為什麼這個 PostGIS 查詢需要這麼長時間才能執行？

一般來說，我是 PostGIS 和 SQL 的新手。我正在對多邊形執行查詢，我希望它只選擇不同的幾何形狀，然後修復（ST_MakeValid()）那些不同的幾何形狀。從而在修復無效幾何的過程中去除重複的幾何。但是，查詢需要很長時間才能執行（幾天）。這是 SQL 語句：

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

分別執行兩個 select 語句很快。大約 1 分鐘makevalid和 15 秒的distinct陳述。

1. 為什麼執行合併語句需要這麼長時間？
2. 關於改進查詢以使其更快的任何建議？

注意：列上有一個索引geom。

您的查詢不必要地複雜，可以解開到這個 100% 等效的查詢：

SELECT gid, id, ST_MakeValid(geom) AS geom
FROM   schema.table_polygons
WHERE  geom IS NOT NULL;

原始文件和這個文件都沒有刪除任何重複項（除了可能刪除一些帶有geom IS NULL，但我懷疑這是你的意圖）。

可能的誤解

關於子句中的輸出列名存在一個常見的誤解WHERE——您只能引用輸入列名。所以，geom在：

WHERE geom IN ...

引用原始輸入列而不是輸出列（的結果ST_MakeValid(geom)）。

有關的：

• PostgreSQL Where 計數條件

如果您的實際意圖是只保留結果ST_MakeValid(geom)等於任何目前現有geom條目的行：

SELECT gid, id, ST_MakeValid(t.geom) AS geom 
FROM   schema.table_polygons t
WHERE  EXISTS (
  SELECT 1
  FROM   schema.table_polygons
  WHERE  geom = ST_MakeValid(t.geom)
  );

我添加了一個表別名和表限定來澄清什麼是什麼。

你提到的索引只會幫助這個變體。

geom如果您的實際意圖是在修復值後合併重複行：

SELECT DISTINCT ON (ST_MakeValid(t.geom))
      gid, id, ST_MakeValid(geom) AS geom 
FROM   schema.table_polygons t
ORDER  BY ST_MakeValid(t.geom), gid, id;

您沒有定義如何打破平局以及從一組欺騙中保留哪一行。我保留gid每組中最小的那一行。id和其餘的騙子中最小的。細節：

• 在每個 GROUP BY 組中選擇第一行？

*旁白：*將模式稱為“模式”就像將您的兒子命名為“兒子”並希望沒有更多的兒子到來。換句話說：不要。

引用自：https://dba.stackexchange.com/questions/127580