優化連接兩個大表的簡單查詢

我不明白為什麼以下查詢如此緩慢（使用 Postgresql 9.6）：

SELECT s.pkid AS pkid_site , s.geom AS geom_site , z.pkid AS pkidEmprise, z.geom AS geom_emprise, z.précision_contour
FROM traitements.sites_candidats AS s
JOIN traitements.zones_sites AS z
   ON z.pkid_site = s.pkid
WHERE s.statut = 'selected'
AND z.statut = 'selected';

表的結構如下：

CREATE TABLE traitements.sites_candidats (
   pkid serial PRIMARY KEY,
   statut varchar(255) NOT NULL, 
   geom geometry(Point, 2154)
); 
CREATE INDEX ON traitements.sites_candidats (statut);

CREATE TABLE traitements.zones_sites (
   pkid serial PRIMARY KEY,
   pkid_site integer NOT NULL,
   geom geometry(MultiPolygon,2154),
   statut varchar(100)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_idx_statut ON traitements.zones_sites (statut) ;
ALTER TABLE  traitements.zones_sites
   ADD CONSTRAINT zones_sites_references_sites_candidats FOREIGN KEY (pkid_site) REFERENCES traitements.sites_candidats(pkid) ON DELETE CASCADE;

（為了提高可讀性，我刪除了查詢中不涉及的幾列）。

結果EXPLAIN ANALYZE：

Hash Join  (cost=17709.29..152088.29 rows=147368 width=887) (actual time=137.074..879.884 rows=210708 loops=1)
 Hash Cond: (z.pkid_site = s.pkid)
 ->  Seq Scan on zones_sites z  (cost=0.00..85198.14 rows=210717 width=855) (actual time=0.140..384.964 rows=210708 loops=1)
       Filter: ((statut)::text = 'selected'::text)
       Rows Removed by Filter: 23
 ->  Hash  (cost=13433.16..13433.16 rows=210490 width=36) (actual time=136.772..136.772 rows=210708 loops=1)
       Buckets: 65536  Batches: 8  Memory Usage: 2191kB
       ->  Seq Scan on sites_candidats s  (cost=0.00..13433.16 rows=210490 width=36) (actual time=3.085..87.774 rows=210708 loops=1)
             Filter: ((statut)::text = 'selected'::text)
             Rows Removed by Filter: 90265
Planning time: 0.386 ms
Execution time: 888.436 ms

顯然，JOIN .. ON它會消耗時間，因為它會進行雜湊處理。

表traitements.sites_candidats有大約 300k 行（其中 210k 帶有statut='selected'）和traitements.zones_sites大約 210k 行（近 99% 的帶有 statut=‘selected’）。查詢時間超過 4 分鐘。

包括在內的綜合指數statut 可能會有所幫助。

由於只有statut = 'selected'感興趣的行，**部分索引**可能會有所幫助：

-- CREATE INDEX ON traitements.zones_sites (pkid_site)
-- WHERE statut = 'selected';  -- not useful for you!

CREATE INDEX ON traitements.sites_candidats (pkid)
WHERE statut = 'selected';     -- probably also not useful, yet

更小的索引，更少的寫入負載和碎片。

但是zones_sites，由於 has 中的大多數行statut = 'selected'，並且由於您從該表中獲取了幾列，並且（幾乎）所有行，Postgres 將堅持順序掃描作為最快的選項，並且唯一可以提供幫助的索引是 on sites_candidats-where 也〜 70% 的行符合條件。因此，使用上述索引的點陣圖索引掃描很可能仍然比對zones_sites.

提高性能的剩餘選項是僅索引掃描。考慮將唯一感興趣的其他列附加sites_candidats到索引中：

CREATE INDEX ON traitements.sites_candidats (pkid, geom)
WHERE statut = 'selected';

僅在滿足僅索引掃描的先決條件時才有用，並且如果表很大並且附加列很小，它會變得更有吸引力。有關的：

• PostgreSQL 是否在此 JOIN 中使用僅索引掃描？

在我對您最後一個問題的回答中了解更多關於僅索引掃描的資訊：

• 使用類型轉換優化查詢

不要期望太多，檢索210708行（根據您的EXPLAIN輸出）仍然需要一些時間。如果您實際上並不需要所有這些，請考慮LIMIT（結合ORDER BY）或其他謂詞來獲取更少的行並使其更快。

旁白：

我不會在標識符中使用重音字母（例如：）précision_contour。雖然這有效，但它引入了潛在的編碼問題。

為什麼要使用不同的數據類型statut？varchar(255), varchar(100). 似乎那些應該是一樣的。如果只有少數狀態是可能的，那麼將其enum設為指向表格的一個或一個很小的 FK 列statut- 以使一切變得更小更快。並且不易出錯。

你昨天的最後一個問題是 Postgres 9.5，但今天是 Postgres 9.6。你是剛升級嗎？如果是這樣，您是否ANALYZE在升級後執行？表統計資訊不結轉。有關的：

• 優化簡單 SELECT 查詢的緩慢性能

社區維基回答：

我認為您需要在兩個表上創建複合索引：(statut, pkid_site)和(statut, pkid)onzones_sites和sites_candidats分別。

(pkid_site, statut)或者，考慮到兩個表之間的外鍵，它可能會更好地建立索引。也就是說，我們有一個內連接，而不是左/外連接，所以首先進行常量過濾可能更安全。外鍵優先可能會導致掃描整個索引，而常量優先只掃描其中的一部分。

statut索引更改後，可能會刪除兩個表中欄位上的現有索引。

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