為什麼 COALESCE 阻止在 varchar 上使用索引，而不是在文本列上？

想像一個包含來自兩個不同表COALESCE的兩列的視圖。varchar

標的varchars資產在兩個表中都有索引。

在 Postgres 11.6 中，根據結果過濾此視圖COALESCE不使用索引，而是進行表掃描。

但是，如果我將列更改為text，在同一列上過濾的完全相同的視圖，則索引將按您的預期使用。

例子

假設我有一個隨時間變化的某些標識符的測量值表。還有一個包含估計值的幾乎相同的表：

CREATE TABLE measured (
 id int,
 ts timestamp,
 identifier character varying,
 measured_value int
);
CREATE INDEX ON measured(identifier);

CREATE TABLE estimated (
 id int,
 ts timestamp,
 identifier character varying,
 estimated_value int
);
CREATE INDEX ON estimated(identifier);

每個表有 100 萬行數據：

INSERT INTO measured
SELECT
   generate_series(1, 1000000),
   to_timestamp((random() * 100000)::int),
   left(md5(random()::text), 2),
   random() * 10;

INSERT INTO estimated
SELECT
   generate_series(1, 1000000),
   to_timestamp((random() * 100000)::int),
   left(md5(random()::text), 2),
   random() * 10;

identifier正如我們所期望的那樣，僅對任一表中的列進行過濾都會使用索引。

我們有一個視圖，它返回所有測量數據和所有估計數據，並組合成標識符和時間戳相同的單行：

CREATE VIEW combined AS
SELECT
   COALESCE(measured.ts, estimated.ts) AS ts,
   COALESCE(measured.identifier, estimated.identifier) AS identifier,
   measured_value,
   estimated_value
FROM measured
FULL OUTER JOIN estimated ON measured.identifier = estimated.identifier
                        AND measured.ts = estimated.ts;

此組合視圖在對列進行過濾時執行表掃描identifier：

EXPLAIN ANALYZE
SELECT * FROM combined
WHERE identifier = 'ab';

但是如果我們將數據類型更改為identifierto text，過濾視圖會按預期使用索引。

這是一個錯誤嗎？

為方便起見，我將整個腳本粘貼在這裡

我可以確認問題。即使在 Postgres 13 中。即使在：

SET enable_seqscan = off;

…如果可能的話，它會強制索引。準確地說，使用索引，但作為完整索引掃描 - 避免順序掃描的最後手段。是否有意義地使用索引的標誌是索引條件而不是EXPLAIN輸出中的過濾器。像：

Index Cond: (identifier = 'ab'::text)

代替：

Filter: ((COALESCE(measured.identifier, estimated.identifier))::text = 'ab'::text)

單獨的索引掃描，沒有索引條件，“使用”索引但不是以有用的方式：

->  Index Scan using measured_identifier_idx on measured 

更簡單的查詢

USING對於初學者，讓我們使用連接條件中的子句來簡化基本查詢：

CREATE VIEW combined AS
SELECT ts
    , identifier
    , m.measured_value
    , e.estimated_value
FROM   measured m
FULL   JOIN estimated e USING (identifier, ts);

等效，除了連接列identifier和ts輸出列列表只添加一次，這只會是有益的，特別是因為SELECT *現在提供唯一的列名。

但它對索引問題沒有任何作用。

研究

text這當然與字元串類型中“首選”的事實有關。varchar與 二進制兼容text，基本上只是一個別名，或者更確切地說，varchar類似於具有基本類型的域text。列上的索引在內部varchar使用該類型text。

這使得 Postgrestext在使用索引時添加了一個強制轉換（實際上這是一個無操作）。在EXPLAIN輸出中變得很明顯，即使是最簡單的查詢：

EXPLAIN SELECT * FROM combined WHERE identifier = 'ab';

...
Index Cond: ((identifier)::text = 'ab'::text)
...

這種無操作的投射text成為一路上的障礙。不確定查詢計劃者在哪裡失去了索引仍然適用的信心。從一開始就使用text顯然可以解決問題。

視圖是通過查詢重寫規則實現的。我認為我們可以排除規則係統的任何參與。我可以單獨使用查詢重現問題，而無需VIEW（在 Postgres 13 中）：

db<>在這裡擺弄

同樣，索引與數據類型一起使用text：

db<>在這裡擺弄

但只有COALESCE和FULL OUTER JOIN。

為了確保，我用一integer列測試了相同的場景。結果與text：

db<>在這裡擺弄

結論

COALESCE結合FULL OUTER JOIN似乎得到了查詢計劃器的特殊處理，以便索引適用。它在表達式中用於計算輸出列identifier，在您的原始查詢中顯式或在我的簡化查詢中隱式計算。這似乎與 a 的連接條件在FULL OUTER JOIN內部實現的方式相匹配。由於某種原因，這在varchar使用時會下降 - 很可能是因為添加了（邏輯上不相關的）強制轉換為::text.

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