Postgresql

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

  • January 28, 2016

一般來說,我是 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))。

有關的:

如果您的實際意圖是只保留結果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和其餘的騙子中最小的。細節:

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

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