使用類型轉換優化查詢

儘管有我能想到的所有索引，但我的查詢花費了太多時間。

最終，我意識到JOIN .. ON查詢的內部之一是將列的內容轉換為另一種數據類型，因為 table 中的列A是 typevarchar而 table 的匹配列B是 type integer。

我更改了我的程式碼以使用一個臨時表，在其中我從表中插入我需要的行A並將列從varchar轉換為integer。

它將查詢速度提高了大約 1000 倍！

表zone_site：

CREATE TABLE traitements.zones_sites (
   pkid serial PRIMARY KEY,
   pkid_site integer NOT NULL,
   origine varchar(50) NOT NULL, 
   origine_id varchar(255) NOT NULL,  -- can't be converted to int
   catégorie varchar(255) NOT NULL,
   horodatage timestamp NOT NULL,
   geom geometry(MultiPolygon,2154),
   précision_contour varchar(100),
   statut varchar(100),
   détails_jsonb text,
   CONSTRAINT enforce_dims_geomloc CHECK (ST_NDims(geom) = 2)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_géométrie ON traitements.zones_sites USING GIST (geom);
CREATE INDEX zones_sites_précision_contour ON traitements.zones_sites (précision_contour);
CREATE INDEX zones_sites_idx_catégorie ON traitements.zones_sites (catégorie);
CREATE INDEX zones_sites_idx_origine ON traitements.zones_sites (origine);
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;

dba.SE 上的相關問題：

• 加入不同類型

不幸的是，該列traitements.zones_sites.origine_id不能是類型integer，因為它包含來自多個來源的標識符，其中一些不是整數。

原始查詢：

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM traitements.zones_sites AS emprises
JOIN parcelles ON parcelles.idpk::varchar = emprises.origine_id
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

修改後的程式碼：

CREATE TEMPORARY TABLE temp_emprises (
   pkid serial PRIMARY KEY,
   pkid_site integer NOT NULL,
   origine varchar(50) NOT NULL, 
   idpk_parcelle integer NOT NULL
);

INSERT INTO temp_emprises (pkid, pkid_site, origine, idpk_parcelle)
SELECT pkid, pkid_site, origine, origine_id::integer 
FROM traitements.zones_sites AS emprises
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

CREATE INDEX ON temp_emprises(idpk_parcelle);

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM temp_emprises AS emprises
JOIN parcelles ON parcelles.idpk = emprises.idpk_parcelle;

有更好的解決方案嗎？

適當的解決方案

您的評論（我添加到您的問題中）揭示了問題的根源：

不幸的是，該列traitements.zones_sites.origine_id不能是類型integer，因為它包含來自多個來源的標識符，其中一些不是整數。

粗略標準化的數據庫設計將避免在同一列中混合不同類型的數據。然後，您的真正整數值可能位於類型列中integer，其餘位於 (a)text列中。而且一開始不會有問題。

在堅持您目前的設計時

因為，正如您所說，該列origine_id可以轉換為整數以選擇查詢中的行，所以我建議使用這個更快的替代方法：創建部分錶達式索引：

CREATE INDEX zones_sites_idx_origine_part_int ON traitements.zones_sites ((origine_id::int))
WHERE catégorie = 'emprise_site'
AND   précision_contour = 'contour_inconnu'
AND   origine = 'xxxxxxxxx';

如果您INSERT的臨時表中的轉換保證有效，那麼這個索引也是如此。

稍作修改的查詢：

SELECT z.pkid, z.pkid_site, z.origine, p.idpar
FROM   traitements.zones_sites z
JOIN   parcelles               p ON p.idpk = z.origine_id::int
WHERE  z.catégorie = 'emprise_site'
AND    z.précision_contour = 'contour_inconnu'
AND    z.origine = 'xxxxxxxxx';

由於部分索引還完美地支持您的查詢，因此相比之下，這應該使您目前的解決方案看起來像慢動作。它也更簡單、更可靠。

您需要的唯一其他索引是一個 on parcelles(idpk)。

由於您需要的唯一其他列parcelles是idpar，如果該表很大並且寫入負載很低，因此您可以從中獲取僅索引掃描，則（附加）多列索引parcelles(idpk, idpar)會使其更快。

更快，然而，在即將到來的Postgres 11 中，真正的覆蓋索引帶有一個INCLUDEcolumn：

與程式碼範例相關的答案：

• 具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快？

想像一下，如果您不創建臨時表，它仍然會快多少，但是：

SELECT emprises.pkid, emprises.pkid_site , emprises.origine, parcelles.idpar
   FROM traitements.zones_sites AS emprises
   JOIN parcelles
       ON parcelles.idpk = emprises.origine_id::integer
   WHERE emprises.catégorie = 'emprise_site'
   AND emprises.précision_contour = 'contour_inconnu'
   AND emprises.origine = 'xxxxxxxxx'

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