Postgres 有時使用劣質索引來表示 WHERE a IN (…) ORDER BY b LIMIT N

我們有一個大約 50 億行的 PostgreSQL 表，它養成了一個討厭的習慣，即缺少正確的索引並對某些LIMIT操作進行主鍵掃描。

該問題通常表現在一個ORDER BY .. LIMIT ..子句（Django 分頁中的常見模式）上，其中LIMIT是由索引匹配的結果的一些相對較小的子集。一個極端的例子是這樣的：

SELECT * FROM mcqueen_base_imagemeta2 
 WHERE image_id IN ( 123, ... )
 ORDER BY id DESC
 LIMIT 1;

其中該IN子句中的項目約為 20，與索引匹配的總行數image_id為 16。

顯示EXPLAIN它錯過了image_id索引，而是對 5B 行進行了 PK 掃描：

限制（成本=0.58..4632.03 行=1 寬度=28）
-> 使用 mcqueen_base_imagemeta2 上的 mcqueen_base_imagemeta2_pkey 向后索引掃描（成本=0.58..364597074.75 行=78722 寬度=28）
過濾器：(image_id = ANY ('{123, ...}'::bigint[]))

如果LIMIT增加到2，它將按預期工作：

限制（成本=7585.92..7585.93 行=2 寬度=28）
-> 排序（成本=7585.92..7782.73 行=78722 寬度=28）
排序鍵：id DESC
-> 在 mcqueen_base_imagemeta2 上使用 mcqueen_base_imagemeta2_image_id_616fe89c 進行索引掃描（成本=0.58..6798.70 行=78722 寬度=28）
索引條件：(image_id = ANY ('{123, ...}'::bigint[]))

這也發生在索引匹配約 3000 行並且限制設置為 100 的查詢上，因此在現實世界的 REST API 分頁中很容易發生這種情況。

表定義為：

mcqueen=# \d mcqueen_base_imagemeta2
                                      Table "public.mcqueen_base_imagemeta2"
     Column       |           Type           |                              Modifiers                               
-------------------+--------------------------+----------------------------------------------------------------------
id                | bigint                   | not null default nextval('mcqueen_base_imagemeta2_id_seq'::regclass)
created_at        | timestamp with time zone | not null
image_id          | bigint                   | not null
key_id            | smallint                 | not null
source_version_id | smallint                 | not null
Indexes:
   "mcqueen_base_imagemeta2_pkey" PRIMARY KEY, btree (id)
   "mcqueen_base_imagemeta2_image_id_616fe89c" btree (image_id)
   "mcqueen_base_imagemeta2_key_id_a4854581" btree (key_id)
   "mcqueen_base_imagemeta2_source_version_id_f9b0513e" btree (source_version_id)
Foreign-key constraints:
   "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b" FOREIGN KEY (image_id) REFERENCES mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED
   "mcqueen_base_imageme_key_id_a4854581_fk_mcqueen_b" FOREIGN KEY (key_id) REFERENCES mcqueen_base_metakey(id) DEFERRABLE INITIALLY DEFERRED
   "mcqueen_base_imageme_source_version_id_f9b0513e_fk_mcqueen_b" FOREIGN KEY (source_version_id) REFERENCES mcqueen_base_metasourceversion(id) DEFERRABLE INITIALLY DEFERRED

在調整方面，我充其量只是個新手，但我認為統計資訊的預設值不符合該表的大小，因此它天真地認為 PK 掃描比索引掃描快。

它認為它會找到 78722，但它確實找到了 16，所以這將導致一些糟糕的計劃。

當 in-list 中的值不存在於 stats 表的 MCV 列表中時，它會使用 n_distinct 值來猜測它們的頻率，這可能是錯誤的（你沒有回答我的問題）。這樣做的方法是獲取 MCV 頻率列表中未涵蓋的元組的數量，並將其除以 MCV 列表中未列出的不同值的數量。所以基本上ntuples * (1-sum of MCF) / (n_distinct - length of MCF)。這個簡化的公式忽略了 NULL。

正如@ErwinBrandstetter 建議的那樣，您可以通過增加統計樣本大小來增加MCV 列表的大小來改善這種情況。這也可能會提高 n_distinct 估計的準確性。但是對於 60 億行，可能無法將樣本量增加足夠多。此外，如果 image_id 與可能出現在同一頁面中的重複值聚集在一起，那麼 PostgreSQL 使用的採樣方法在計算 n_distinct 時會存在很大偏差，這很難通過增加樣本大小來修復。

解決此問題的更簡單方法可能是手動修復 n_distinct ：

alter table mcqueen_base_imagemeta2 alter column image_id set (n_distinct=1000000000);
analyze mcqueen_base_imagemeta2;

這種方法不會像增加樣本量那樣增加 ANALYZE 所需的時間或儲存空間，而且也更有可能成功。

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