PostgreSQL 規劃器對分佈不均勻的列的錯誤決策

事實

我有以下代表金融交易的簡單表格：

CREATE TABLE transaction (
   "user_id" text not null,
   "offset" serial not null,
   "tx_data" text not null
);

CREATE INDEX transaction_offset on transaction("offset");
CREATE INDEX transaction_user_offset on transaction("user_id", "offset");

• 表包含約十億行。
• 佔大部分交易的使用者很少（取自pg_stats）：

most_common_vals: {'userA', 'userB', .... 'userC'}
most_common_freq: {0.6, 0.07, ...., 0.00001}

• 第一筆交易userA是在抵消2_000_000
• 使用者的交易分佈不均。即使userA有很多交易，這些交易也是“批量”彼此靠近的。有一段時間使用者非常活躍，然後有一段時間完全不活躍。

問題

如果我只是嘗試按使用者查詢前 100 筆交易：

select * from transaction where user_id = 'someUser' order by "offset" limit 100

如果我查詢userC（僅佔交易的 0.0001），它會非常快並使用transaction_user_offset_idx索引：

Limit (cost=0.71..370.76 rows=100 width=294)
-> Index Scan using transaction_user_offset_idx on transaction (cost=0.71..112511575.20 rows=30404045 width=294)
Index Cond: (user_id = 'userC')

如果我查詢userA（佔交易的 60%），它會非常慢。查詢計劃器僅transaction_offset在它計算出的情況下使用，因為它userA是如此頻繁，因此為使用者使用任何索引並僅按順序過濾是沒有意義的：

Limit (cost=0.58..11.36 rows=100 width=294)
-> Index Scan using transaction_offset_idx on transaction (cost=0.58..359836920.47 rows=401760686 width=294)
Filter: (user_id = 'userA')

它太慢了，因為 table 非常大，第一行userA是 offset 2_000_000。因此，查詢必須依次遍歷 200 萬行，然後才能到達 for 的第一行userA。

db-fiddle 重現問題

我嘗試過的解決方案

• 降低random_page_cost。我不喜歡這種解決方案，因為在某些地方，順序掃描可能更理想。最重要的是，即使此參數設置為 1.0 頻率userA如此之高，查詢規劃器仍會選擇使用過濾器。
• 完全禁用使用者列的計劃器統計資訊。這適用於這個查詢（它總是使用transaction_user_offset索引），但感覺“hackish”，我無法預測它將如何影響其他查詢。

還有哪些其他可能的解決方案？

比僅僅為了一個查詢而修改整個列統計資訊更本地化的解決方案是在計劃時將實際常量隱藏在計劃器中，方法是將其放入虛擬子選擇中。

select * from transaction 
where "user_id" = (select 'userA') 
order by "offset" limit 100;

不過，我不明白為什麼計劃者一開始就沒有做正確的事情。我可以重現它，但對我來說，將 random_page_cost 降低到 1.1 就足以回到正確的計劃（而且考慮到我的硬體也恰好是有意義的）

$$ Edit $$我已經探索了更多關於計劃程序的內容。我想您已經知道嚴重錯誤，它不知道 user_id 和“offet”的順序是如何相關的。它認為要找到 100 個符合條件的行，它只需要掃描 transaction_offset 索引的 100/0.6 = 160 行，而不是像它那樣掃描 2000100 行。但是，這仍然會使該索引比更好的索引稍微貴一些，而不是便宜一些。但是那個一欄索引小於兩欄索引，並且索引大小確實在成本估算中起作用。一個微弱的角色，但足以打破平衡。 這確實讓我產生了一個頑皮的想法。如果將 NUL 字節 (ascii-0) 附加到 transaction_offset 索引，直到它與 transaction_user_offset 索引的大小相同，它應該刪除這個總大小差異作為計劃中的一個因素。在我手中，這足以讓計劃者重新開始做出正確的決定。這顯然不是應該在生產系統上隨便做的事情。

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