為什麼在子查詢中 ORDER BY 時沒有使用我的 PostgreSQL 表達式索引？
我有一個查詢，它從大約 3m 的表中獲取大約 80k 行。我一次只需要分頁十行，但使用
ORDER BY (a+b) DESC(a和b是integer類型列。)現在我的查詢大約需要 4 秒來完成。如果我
ORDER BY改為主鍵，它會避免 seq 掃描並花費 200 毫秒，我會很滿意。所以我在
(a+b) DESC.我的想法是
ORDER BY主鍵和主鍵都有索引（就像WHERE子句中的所有東西一樣 - 並且這些索引被使用得很好），所以子查詢應該能夠在沒有 seq 掃描的情況下完成它需要的一切，放棄外部查詢的 10 個索引主鍵，如果它需要 seq 掃描*，則只需要找到 10 行。無論如何，這並沒有什麼不同。
不幸的是，我無法分享完整的表格詳細資訊，但我可以說我的表格有 21 列，其中一些
text、二integer、幾timestamps甚至一列ts_vector。除了表達式之一，還有 4 個索引：PK（文本）是 btree，我還有另一個 btree 文本、一個 btree 時間戳和 tsvector 上的 gin 索引。此外：我還可以按索引時間戳和非主鍵索引文本排序，就像主鍵一樣，由於使用了索引，這將查詢時間減少到 200 毫秒。只是我的表達式索引在子查詢上下文中似乎無法以這種方式工作。
其他選項，似乎都不是很好：
- 為 (a+b) 添加計算列，以便索引僅位於一列上。如果它適用
ORDER BY於 pk 它應該適用於該列，對嗎？- 通過以某種方式猜測一個快樂的限制器
(a+b)由於其分佈而作弊。我不確定我怎麼總是知道，因為查詢輸入是動態的，但在這種情況下，如果我添加一個 simpleAND (a+b) > 1000000，我仍然會返回十行，但它只需要 200 毫秒——並將我的表達式索引用於 WHERE 子句seq 掃描通常在哪裡。（關於不知道，我可以開始做某種二進制搜尋式的事情，嘗試高數字並減少一半，直到我得到至少 10 行，然後每天呼叫它。如果我在不到 20 行內得到它嘗試，我仍然領先。但這超出了笨拙…）有任何想法嗎？如何讓它使用我的表達式索引並避免 seq 掃描？
我不理解上述內容的原因是文件（至少可以追溯到 9.4）明確地這樣說：
索引表達式的維護成本相對較高，因為必須在插入時和更新時為每一行計算派生表達式。但是，索引表達式在索引搜尋期間不會重新計算，因為它們已經儲存在索引中。
那麼到底如何比兩者都被索引時
other_column更便宜呢？ORDER BY``a+b關於查詢計劃，讓我們試著深入了解它的核心。我已經發布了一個後續問題。
查詢優化器通常根據它對錶及其數據的了解，以及它對如何獲取該數據的選項，找出如何盡可能快地限制查詢的可能行數。
它必須考慮的因素之一是必須載入多少頁面才能獲得所需的數據。從磁碟讀取頁面是 DBMS 所做的最昂貴的事情之一，所以如果它可以處理更少的頁面，那通常是一件好事。
您的查詢通常不包括對
a+b. 因此，該索引不會幫助選擇數據，也沒有理由載入它。一旦確定了要使用的行，使用
(a+b) DESC索引對它們進行排序是否有用？我只能看到兩種方法：
- 向後使用索引：我們不是根據 的值來辨識行
a+b，而是從 的最大值開始查看索引a+b，並檢查每個關聯的行以查看它是否是我們的候選行之一，當我們得到十行時停止。這要求我們載入一個未知數量或頁面的索引，然後查找行以查看它們是否存在於候選數據中。除非我們知道幾乎所有行都是候選行，否則我認為這不會很快。- 計算
a+b候選行的值，然後使用索引進行排序：由於將索引與表中的值匹配的方法之一是對a+b錶中的計算值進行排序a+b，這樣做更有意義聽起來就像引擎所做的那樣：a+b從候選行計算並直接對其進行排序，而無需費心載入索引頁面。當您添加涉及的搜尋條件時，
a+b圖片將完全改變。不必向後使用索引（a+b根據行查找值），它實際上需要根據a+b值查找行。如果它可以在檢查其他條件的同時保持數據的排序順序，那麼是的，它將使用該排序順序，而不必執行單獨的步驟來對數據進行排序。您還應該注意，如果您的主鍵是聚集索引，那麼數據實際上是基於該值儲存的；這會影響按此排序的難易程度。
另請注意，必須對整個返回數據集進行排序
a+b才能找到前十個a+b值。如果此查詢很關鍵，那麼您可以在覆蓋索引上使用變體。通常，這是一個包含查詢中使用的所有列的索引（無論是在
SELECT列表中，在子句中的表連接中FROM，在WHERE子句中，ORDER BY-所有使用的列）。請注意，查詢中的每一列不一定都必須用作索引本身的一部分。實際索引應覆蓋足夠多的列，以便在縮小要返回的行時有用。附加列通常包含在“葉”級別 - 它們不用於對錶中的行進行排序，但數據在索引中可用。
如果查詢中來自該表的所有列都是索引的一部分，或者包含在葉級別，則不需要實際引用該表；覆蓋索引包含所有需要的數據，並且通常會小於整個表，因此我們可以從索引中獲取所需的一切，並載入更少的頁面。
聽起來您要從表中恢復所有列，因此真正的覆蓋索引不一定有太大幫助。但是，覆蓋除僅在列表中的列之外的列的索引可能對您有用。我的意思是，該表中的所有列在子句、子句、任何或子句中使用，並且（在這種情況下）甚至是子句中的列。
SELECT``JOIN``WHERE``GROUP BY``HAVING``ORDER BY使用這樣的索引，引擎可以在不參考實際表的情況下找到它需要返回的所有行。由於我們已經包含了
ORDER BY子句中的列，它甚至應該能夠確定需要返回的 10 行（感謝您的LIMIT子句）。只涉及十行，引擎可能會發現使用索引辨識十行更快，然後在實際表中查找這 10 行以獲得要返回的完整值集。當許多不在它使用的索引中的列參與辨識要返回的實際行時，引擎更有可能依賴表掃描來至少部分查詢。它必須從表中檢索最終結果集，因此如果它認為如果使用索引就必須載入更多頁面，然後查找表中的行，而不是直接訪問表，它可能會選擇要做到這一點。
再次，請記住這是基於幾乎沒有具體資訊。如果查詢中不需要很多列（甚至是幾個非常大的列）來標識要返回的行，但它們只是最終
SELECT列表的一部分，這將最有效。關於此聲明：
但是，索引表達式在索引搜尋期間不會重新計算，因為它們已經儲存在索引中。
我認為您可能過於概括了它。如果沒有表達式索引，要搜尋所有行，其中
a+b >= 12000000,a+b必須為每一行計算。通過將此值編入索引，我們可以輕鬆辨識 的值a+b符合我們標準的行。但是，這並不意味著 的值a+b也是在表級別預先計算的。如果無法使用索引來辨識我們的行，那麼（如本答案前面所述）根本不會使用索引。並且，如果不使用索引，則a+b仍然需要計算。索引旨在根據索引值查找特定行，而不是根據特定行查找索引值。
首先，我不確定 for
ORDER BY是否會使用 80,000 行的索引。檢索了 8 萬行，那裡有 300 萬行。讓我們重新創建它並進行測試CREATE TABLE foo AS SELECT x::int AS a, (x%2*x)::int AS b FROM generate_series(1,3e6) AS gs(x); CREATE INDEX ON foo (a,(a+b)); SELECT a FROM foo WHERE a BETWEEN 1000000 AND 1080000 ORDER BY a+b; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=22950.75..23151.96 rows=80483 width=8) (actual time=44.812..47.980 rows=80001 loops=1) Sort Key: ((a + b)) Sort Method: quicksort Memory: 6823kB -> Bitmap Heap Scan on foo (cost=1709.38..16392.83 rows=80483 width=8) (actual time=14.349..27.506 rows=80001 loops=1) Recheck Cond: ((a >= 1000000) AND (a <= 1080000)) Heap Blocks: exact=355 -> Bitmap Index Scan on foo_a_expr_idx (cost=0.00..1689.26 rows=80483 width=0) (actual time=14.294..14.294 rows=80001 loops=1) Index Cond: ((a >= 1000000) AND (a <= 1080000)) Planning time: 0.148 ms Execution time: 52.209 ms (10 rows)所以你可以看到它很快就很愚蠢。由於您沒有向我們提供太多資訊，因此我不確定是什麼減慢了您的速度。但是，它不僅僅是索引掃描。我的猜測是 a+b 的索引查找根本不值得。但這是列表的問題。