對值為 NULL 的布爾值進行查詢時出現意外的 Seq 掃描

我有一個名為auto_reviewwhere column type is的數據庫列boolean。該欄位有一個索引，使用 ActiveRecord ORM 創建。

CREATE INDEX index_table_on_auto_renew ON table USING btree (auto_renew);

當我在欄位中查詢布爾值時，PG 按預期使用索引。

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" = 'f'
                                         QUERY PLAN
----------------------------------------------------------------------------------------------
Bitmap Heap Scan on table  (cost=51.65..826.50 rows=28039 width=186)
  Filter: (NOT auto_renew)
  ->  Bitmap Index Scan on index_domains_on_auto_renew  (cost=0.00..44.64 rows=2185 width=0)
        Index Cond: (auto_renew = false)
(4 rows)

當值為 時NULL，使用順序掃描。

EXPLAIN for: SELECT "table".* FROM "table"  WHERE "table"."auto_renew" IS NULL
                          QUERY PLAN
----------------------------------------------------------------
Seq Scan on table  (cost=0.00..1094.01 rows=25854 width=186)
  Filter: (auto_renew IS NULL)
(2 rows)

我很想知道這個選擇背後的原因。

通常，col IS NULL是（預設）b-tree 索引搜尋的可能候選者。手冊：

此外，索引列上的IS NULLorIS NOT NULL條件可以與 B 樹索引一起使用。

要獲得證據，請禁用順序掃描（僅在測試會話中！）：

SET enable_seqscan = OFF;

我在這裡引用手冊：

enable_seqscan (boolean)

啟用或禁用查詢計劃器對順序掃描計劃類型的使用。完全抑制順序掃描是不可能的，但是如果有其他可用的方法，關閉這個變數會阻止規劃器使用一個。預設為開啟。

然後再試一次：

EXPLAIN ANALYZE SELECT * FROM tbl WHERE auto_renew IS NULL;

這可能會導致點陣圖索引掃描比對錶的順序掃描慢。

重置或關閉會話（設置為會話本地）。

RESET enable_seqscan;

列上的索引boolean僅在某些情況下有用。計劃者僅在期望索引更快時才使用索引。計算基於您的成本設置和收集的統計數據ANALYZE。如果表的相當大一部分與您的條件匹配（大約 5% 或更多，具體取決於），則執行全表掃描通常會更快。

這使得列中的稀有值boolean成為純索引的唯一有用候選者。並且為此創建（更專業的）部分索引通常更有效- 如果查詢條件匹配，維護成本更低，更小，更快並且更容易使用。

如果您有很多查詢來查找行auto_renew IS NULL並且這種NULL情況不是很常見（和/或您需要某種排序順序），那麼此索引將有助於快速查找/排序這些行：

CREATE INDEX index_tbl_tbl_id_auto_renew_null ON tbl (tbl_id)
WHERE auto_renew IS NULL;

部分索引的條件必須在WHERE查詢的子句中或多或少準確地重複，以使查詢規劃器意識到索引是適用的。

索引列 ( tbl_id) 是任意選擇。重要的部分是WHERE條款。ORDER BY tbl_id此特定索引對於帶有或附加過濾器或連接的查詢最有效tbl_id。您可以將其設為多列索引。布爾列通常與其他列結合使用更有用。

另外：ORM 是經常無法充分發揮 RDBMS 潛力的拐杖。

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