加快索引掃描向後查詢

我的應用程序正在執行以下 psql 查詢，並且執行速度極慢：

SELECT COUNT(*) 
FROM (
 SELECT 1 AS one 
 FROM "large_table" 
 WHERE "large_table"."user_id" = 123 
 ORDER BY "large_table"."id" desc 
 LIMIT 1 OFFSET 30
) subquery_for_count;

當我更改為ORDER BY時ASC，它的執行速度快了 100 倍。我在 id 上有預設的主鍵索引，並且我已經嘗試按 desc 順序為 id 添加一個額外的索引，但它似乎沒有任何區別。

當我執行解釋分析時，我看到它正在對慢查詢 ( desc) 使用反向索引掃描。我嘗試為我的會話手動禁用索引掃描，發現查詢在 40 秒內而不是 2 分鐘內執行，這是一個顯著的改進。

知道在按 DESC 排序時我可以做些什麼來嘗試提高此查詢的速度嗎？我已經讀過對於 b-tree 索引，無論排序順序如何，它通常都應該為您提供相同的性能，但情況似乎並非如此。

您的查詢必須使用“id”上的索引以隱含順序掃描索引，然後過濾掉“user_id”不等於 123 的所有內容，在找到 31 個在過濾器中倖存的行後停止。在一個方向上它會很快找到 31 個這樣的行，在另一個方向上需要在 31 個存活之前過濾掉大量行（因為從該端開始的行中沒有/很少有 user_id=123）。

您可以通過對查詢進行 EXPLAIN (ANALYZE, BUFFERS) 來輕鬆確認這一理論。

這基本上與索引掃描的順序無關。如果您為 123 選擇了一個具有相反屬性的值（它們都發生在索引的邏輯末尾而不是邏輯開頭），那麼情況就會相反。指定 DESC 將解決問題，而不是導致問題。

知道在按 DESC 排序時我可以做些什麼來嘗試提高此查詢的速度嗎？

您的查詢似乎毫無意義。計數不是依賴於順序的活動。這可能不是您真正的查詢。那麼誰知道我們的建議是否會轉移到您的實際查詢中呢？此查詢最直接的修復方法是在 (user_id, id) 上建構多列索引。然後不會一一過濾掉任何行，因為它們將通過索引的操作被批量刪除。

文件中對此進行了討論（https://www.postgresql.org/docs/current/indexes-ordering.html）：

預設情況下，B-tree 索引以升序儲存它們的條目，最後是空值。這意味著對列 x 的索引進行前向掃描會產生滿足 ORDER BY x 的輸出（或更詳細地說，ORDER BY x ASC NULLS LAST）。索引也可以向後掃描，產生滿足 ORDER BY x DESC 的輸出（或更詳細地說，ORDER BY x DESC NULLS FIRST，因為 NULLS FIRST 是 ORDER BY DESC 的預設值）。

您可以通過在創建索引時包含選項 ASC、DESC、NULLS FIRST 和/或 NULLS LAST 來調整 B-tree 索引的順序

……剪……

您可能想知道為什麼要提供所有四個選項，因為兩個選項以及向後掃描的可能性將涵蓋 ORDER BY 的所有變體。在單列索引中，這些選項確實是多餘的，但在多列索引中它們可能很有用。

如果查詢計劃器選擇了多列索引，您可能處於文件描述的情況。如果它使用單列索引，那麼您觀察到的性能是不尋常的。

我建議您創建一個降序索引，看看是否可以提高性能：

CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

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