PostgreSQL 中的多表達式索引未按預期使用

我有一個定義了多個表達式索引的 PostgreSQL 9.3 表。當我使用explain查看哪些索引用於我的查詢時，我驚訝地發現 PostgreSQL 沒有使用與我的 WHERE 子句中的條件最匹配的索引。這似乎是表達式索引特有的問題。這是一個展示問題的 sqlfiddle 連結：

http://sqlfiddle.com/#!15/580e0/1

CREATE TABLE Table1
   ("a" int, "b" int, "c" int, "d" int)
;
CREATE index a ON Table1 (a);
CREATE index ab ON Table1 (a,b);
CREATE index bplus1 ON Table1 ((b+1));
CREATE index abplus1 ON Table1 ((a+1),(b+1));

基本的多列索引ab正在正確使用：

explain select * from Table1 where a = 1 AND b = 1

   |                                                       QUERY PLAN |
   |------------------------------------------------------------------|
   | Index Scan using ab on table1  (cost=0.13..8.15 rows=1 width=16) |
   |                                Index Cond: ((a = 1) AND (b = 1)) |

但是沒有使用類似的多列表達式索引 abplus1：

explain select * from Table1 where (a+1) = 2 AND (b+1) = 2


|                                                           QUERY PLAN |
|----------------------------------------------------------------------|
| Index Scan using bplus1 on table1  (cost=0.13..8.16 rows=1 width=16) |
|                                            Index Cond: ((b + 1) = 2) |
|                                                Filter: ((a + 1) = 2) |

奇怪的是，如果刪除了單列表達式索引，則會使用更具體的索引。請參閱此範例http://sqlfiddle.com/#!15/f4fda/1：

explain select * from Table1 where (a+1) = 2 AND (b+1) = 2


|                                                            QUERY PLAN |
|-----------------------------------------------------------------------|
| Index Scan using abplus1 on table1  (cost=0.14..8.15 rows=1 width=16) |
|                         Index Cond: (((a + 1) = 2) AND ((b + 1) = 2)) |

在我看來，單表達式和多表達式索引之間存在衝突。令人驚訝的是，使用基本（非表達式）索引時似乎沒有任何此類衝突。

有什麼辦法可以解決這個問題嗎？我想保留我的兩個表達式索引，因為每個索引都非常適合特定查詢。

在我看來，單表達式和多表達式索引之間存在衝突。

如果這就是您所說的“衝突”，那麼所有適用的索引都是候選索引。但這就是它的程度。Postgres 根據表和索引統計資訊結合成本設置來估計執行時間，最好的估計勝出。

FILTER如果 Postgres 期望您的兩個條件之一具有足夠的選擇性，它可能會採用匹配的單列索引並在第二個條件的步驟中過濾可能的（很少！）誤報。

但是，您在評論中提到的數據分佈肯定不是這種情況：

20,000 行。b=1 表示 10,000，a=1 表示 2。一行表示 a=1，b=1

@ypercube 已經在他的 sqlfiddle中證明您的測試應該使用多列索引。您描述的數據分佈更是如此。

如果您提供的輸出EXPLAIN (ANALYZE, BUFFERS)而不是 just EXPLAIN，我們可能會看到更多。我猜你在創建表達式索引後沒有執行**ANALYZE**，這是更新統計資訊所必需的。autovacuum 將在一段時間後啟動，但如果您在創建索引後立即執行測試（並且永遠不會用於臨時表！），則速度不夠快。

ANALYZE Table1;

想想看，我們（也就是：你我）之前解決了這個問題：

• 在具有現有數據的表上創建時未使用 PostgreSQL 部分索引

還有相關的：

• 未使用但影響查詢的索引

除此之外，Postgres 9.3 已經過時了。從那以後已經發布了 3 個主要版本。如果您遇到這樣的問題，我建議您測試目前版本，看看問題是否仍然存在。

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