鑒于以下 ddl
CREATE TABLE test
(
c1 SMALLINT NOT NULL,
c2 INTERVAL NOT NULL,
c3 TIMESTAMP NOT NULL,
c4 VARCHAR NOT NULL,
PRIMARY KEY (c1, c2, c3)
);
CREATE INDEX test_index ON test (c3, c2);
以下查詢
SELECT *
FROM test
WHERE c2 = '1 minute'::INTERVAL
ORDER BY c3
LIMIT 1000
在 PostgreSQL 13.3 中給出以下查詢計劃
Limit (cost=0.43..49.92 rows=1000 width=60)
-> Index Scan using test_index on test (cost=0.43..316739.07 rows=6400526 width=60)
Index Cond: (c2 = '00:01:00'::interval)
考慮到test_index有這個 order 中的列(c3, c2)，為什么 postgres 可以使用這個索引有效地過濾c2和排序？c3據我了解，出現的列ORDER BY必須是索引定義中的最后一個，否則將不會使用索引。在以下情況下它也同樣有效ORDER BY c3 DESC
uj5u.com熱心網友回復：
如果沒有實際的運行統計資訊 ( EXPLAIN ANALYZE)，我們不知道它是否有效。我們只知道規劃者認為它比替代方案更有效。
通過以所需的順序尋址已經存在的行，它可以過濾掉那些不符合 c2 條件的行，然后在累積 1000 個通過條件的行時停止。它認為它會在僅讀取大約 1/6000 的索引后完成此操作。
該計劃沒有明確說明該索參考于提供排序。我們可以根據沒有排序節點來推斷。PostgreSQL 知道如何在任一方向上跟蹤索引，這就是為什么如果順序是 DESC，索引也可以作業的原因。
無論效率如何，主要來自提前停止和避免排序。c2='00:01:00'::interval 上的過濾效率不是很高。如果它知道條件為真，它就不能跳轉到索引的部分，而是必須掃描索引并單獨評估索引元組以過濾它們。但至少它可以對索引元組應用過濾器，而不需要訪問表元組，這樣可以節省大量的隨機IO。（我認為，如果該計劃能以某種方式區分跳轉到索引的使用和索引內過濾的使用，那將是一個好主意，但這說起來容易做起來難）。
僅在 c3 上的索引仍然可以按順序讀取并提前停止，但它必須訪問每個元組的表，即使是那些最終在 c2 上失敗的元組。更好的索引應該是 on (c2,c3)。這樣它就可以跳轉到滿足 c2condition 的索引部分，然后 c3 在該部分內按順序讀取。
uj5u.com熱心網友回復：
它可能效率不高。
但是，優化器選擇對索引進行過濾。
這意味著它將讀取根據預期順序排序的索引條目，但并非所有這些條目都是有用的。這就是它c2 = '00:01:00'::interval在索引掃描上添加過濾謂詞的原因。
也許丟棄條目的索引掃描的成本仍然低于表掃描，特別是考慮到它最多只能保持1000。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/497096.html
標籤：PostgreSQL