如何讓 Postgres 在這裡使用正確的索引？

這是一個測試設置：

CREATE TABLE t (
   filename int,
   cropped bool not null default false,
   resized bool not null default false,
   create_date date not null default '1970-01-01'
);

INSERT INTO t
SELECT generate_series(1, 1000000);

UPDATE t SET cropped = true, resized = true
WHERE filename IN (SELECT filename FROM t ORDER BY random() LIMIT 900000);
UPDATE t SET resized = false
WHERE filename IN (SELECT filename FROM t WHERE cropped = true ORDER BY random() LIMIT 1000);

VACUUM FULL t;
ANALYZE t;

小提琴

數據現在看起來像這樣：

SELECT cropped, resized, count(*)
FROM t
GROUP BY 1,2;

我為其他查詢創建了一些部分索引，並為以下查詢創建了一個索引：

CREATE INDEX idx_cropped ON t(cropped) WHERE NOT cropped;
CREATE INDEX idx_resized ON t(resized) WHERE NOT resized;

CREATE INDEX specific ON t(cropped,resized) WHERE cropped AND NOT resized;

現在我執行我的查詢：

EXPLAIN ANALYZE
   SELECT count(*) FROM t WHERE cropped AND NOT resized AND create_date < CURRENT_DATE;

Aggregate  (cost=4001.25..4001.26 rows=1 width=8) (actual time=478.557..478.558 rows=1 loops=1)
 ->  Index Scan using idx_resized on t  (cost=0.29..3777.71 rows=89415 width=0) (actual time=478.177..478.480 rows=1000 loops=1)
       Filter: (cropped AND (create_date < CURRENT_DATE))
       Rows Removed by Filter: 100000

在 dbfiddle.uk 上需要 478 毫秒（在我的機器上它更快，但差異仍然可見）。

現在我刪除一個索引：

DROP INDEX idx_resized;

並再次執行相同的查詢，我得到了一個更好的計劃：

Aggregate  (cost=11876.27..11876.28 rows=1 width=8) (actual time=0.315..0.316 rows=1 loops=1)
 ->  Bitmap Heap Scan on t  (cost=35.50..11652.73 rows=89415 width=0) (actual time=0.054..0.250 rows=1000 loops=1)
       Recheck Cond: (cropped AND (NOT resized))
       Filter: (create_date < CURRENT_DATE)
       Heap Blocks: exact=6
       ->  Bitmap Index Scan on specific  (cost=0.00..13.15 rows=89415 width=0) (actual time=0.040..0.040 rows=1000 loops=1)

它使用索引specific並在 dbfiddle.uk 和我的機器上在不到一毫秒的時間內完成。

---

編輯：額外的謎團 - 當我不使用 UPDATE 而是使用 DEFAULT 設置值時，會選擇正確的索引。到底是怎麼回事？小提琴

首先，您idx_cropped沒有使用與查詢相同的過濾器，因此無法使用。

您的計劃告訴我們，它期望兩個可能的索引都返回 89415 行，這將由優化器根據它擁有的資訊（統計資訊和查詢）計算。您可以嘗試收集其他統計資訊。

因為它認為兩個索引的選擇性是相同的，所以它會選擇較小的索引（索引鍵中不包含兩列的索引）。

將這些列包含為索引鍵有點浪費——您已經知道它們是什麼，因為它們是索引過濾器的一部分。

相反，您可以使用create_datewhich 在查詢中被過濾。我(create_date) where cropped and not resized在這個展示中包含了一個索引https://dbfiddle.uk/?rdbms=postgres_14&fiddle=19b28197b2654b7492aa6a8a3ee44cd3

PostgreSQL 在這裡估計很差。或許你應該注意到 pgsql-hackers 列表，可能還有改進的餘地。

但直接的解決方案是創建真正是該查詢的最佳索引的索引：

CREATE INDEX ON t (create_date) WHERE cropped AND NOT resized;

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