Postgres 版本

使用 PostgreSQL 10.3。

表定義

CREATE TABLE tickets (
 id bigserial primary key,
 state character varying,
 closed timestamp
);

CREATE INDEX  "state_index" ON "tickets" ("state")
 WHERE ((state)::text = 'open'::text));

基數

該表包含 1027616 行，其中 51533 行具有state = 'open'和closed IS NULL，或 5%。

state按預期使用索引掃描，條件為 on 的查詢表現良好：

explain analyze select * from tickets where state = 'open';

Index Scan using state_index on tickets  (cost=0.29..16093.57 rows=36599 width=212) (actual time=0.025..22.875 rows=37346 loops=1)
Planning time: 0.212 ms
Execution time: 25.697 ms

我正在嘗試使用條件為查詢實現相同或更好的性能，closed IS NULL以便我可以刪除該state列並依賴該closed列來獲取相同的行。closed是null針對相同的行 where state = 'open'，因此該state列是多餘的。

select * from tickets where closed IS NULL;

但是，我嘗試過的所有索引都不會像第一個查詢那樣導致單個索引掃描。以下是我嘗試過的索引以及EXPLAIN ANALYZE結果。

部分索引：

CREATE INDEX  "closed_index" ON "tickets"  ("closed") WHERE (closed IS NULL)

explain analyze select * from tickets where closed IS NULL;

Bitmap Heap Scan on tickets  (cost=604.22..38697.91 rows=36559 width=212) (actual time=12.879..48.780 rows=37348 loops=1)
 Recheck Cond: (closed IS NULL)
 Heap Blocks: exact=14757
 ->  Bitmap Index Scan on closed_index  (cost=0.00..595.09 rows=36559 width=0) (actual time=7.585..7.585 rows=37348 loops=1)
Planning time: 4.831 ms
Execution time: 52.068 ms

表達式索引：

CREATE INDEX  "closed_index" ON "tickets" ((closed IS NULL))

explain analyze select * from tickets where closed IS NULL;

Seq Scan on tickets  (cost=0.00..45228.26 rows=36559 width=212) (actual time=0.025..271.418 rows=37348 loops=1)
 Filter: (closed IS NULL)
 Rows Removed by Filter: 836578
Planning time: 7.992 ms
Execution time: 274.504 ms

部分錶達式索引：

CREATE INDEX  "closed_index" ON "tickets" ((closed IS NULL))
 WHERE (closed IS NULL);

explain analyze select * from tickets where closed IS NULL;

Bitmap Heap Scan on tickets  (cost=604.22..38697.91 rows=36559 width=212) (actual time=177.109..238.008 rows=37348 loops=1)
 Recheck Cond: (closed IS NULL)
 Heap Blocks: exact=14757
 ->  Bitmap Index Scan on "closed_index"  (cost=0.00..595.09 rows=36559 width=0) (actual time=174.598..174.598 rows=37348 loops=1)
Planning time: 23.063 ms
Execution time: 241.292 ms

更新

擴展表定義：

CREATE TABLE tickets (
 id bigserial primary key,
 state character varying,
 closed timestamp,
 created timestamp,
 updated timestamp,
 title character varying,
 size integer NOT NULL,
 comment_count integer NOT NULL
);

CREATE INDEX  "state_index" ON "tickets" ("state")
 WHERE ((state)::text = 'open'::text));

基數：

該表包含 1027616 行，其中 51533 行的狀態 = ‘open’ 且關閉為 NULL，即 5%。如上所述，我試圖刪除state列，因此我希望能夠使用closed列上的條件來獲取相同的行。

對列有條件的查詢state使用索引掃描。

explain analyze select id, title, created, closed, updated from tickets where state = 'open';

Index Scan using state_index on tickets  (cost=0.29..22901.58 rows=49356 width=72) (actual time=0.107..49.599 rows=51533 loops=1)
Planning time: 0.511 ms
Execution time: 54.366 ms

closed在切換到對列進行查詢時，我想要相同的性能（理想情況下是索引掃描） 。id我在和上嘗試了部分索引closed IS NULL：

CREATE INDEX closed_index ON tickets (id) WHERE closed IS NULL;

VACUUM ANALYZE tickets;

explain analyze select id, title, created, closed, updated from tickets where closed IS NULL;

Bitmap Heap Scan on tickets  (cost=811.96..33999.42 rows=49461 width=72) (actual time=7.868..47.080 rows=51537 loops=1)
 Recheck Cond: (closed IS NULL)
 Heap Blocks: exact=17479
 ->  Bitmap Index Scan on closed_index  (cost=0.00..799.60 rows=49461 width=0) (actual time=4.868..4.868 rows=51537 loops=1)
Planning time: 0.222 ms
Execution time: 51.028 ms

假設中心資訊：

約 15% 的行具有state = 'open'和closed IS NULL

應該意味著所有 1031584 行中相同的 15% 滿足這兩個條件（所有細節都很重要！）。這兩個條件應該執行相同 - 返回大約155k行（！）

您的查詢計劃顯示37346 個符合條件的行，~ 3.6 % 而不是 15 %。你的問題還是有問題。

在 3.6% 的情況下，索引才開始有意義。您的小行大小實際上每行佔用約 52 個字節，每頁約 155 行。對於完全隨機分佈，這將是每頁 5-6 次點擊。無論如何， Postgres 必須讀取所有頁面，順序掃描應該是最快的計劃。過濾未命中應該比以任何方式涉及索引更快。

通常，符合條件的行或多或少是聚集的，涉及的數據頁越少，涉及索引的意義就越大。但是，所有點陣圖索引掃描，我幾乎看不到任何索引掃描的情況。對於您聲稱的 15%，甚至更少（只要“更少”比“幾乎沒有”）。

對於您更新的數字（約 5% 的行匹配），我仍然希望點陣圖索引掃描而不是索引掃描。一個可能的解釋：表膨脹，有很多死元組。您提到了高寫入負載。這將導致每個數據頁的活動元組越來越少，並且有利於索引掃描（與點陣圖索引掃描相比）。您可以在 a 之後重新測試您的初始查詢VACUUM FULL ANALYZE（如果您能負擔得起表上的排他鎖！）。如果我的假設成立，那麼物理表的大小將大幅縮小，然後您將看到點陣圖索引掃描而不是索引掃描（而且速度也更快）。

• 對於對齊優化表大於原始表 - 為什麼？
• 測量 PostgreSQL 表行的大小

您可能需要更激進的autovacuum設置。看：

• PostgreSQL 上的主動自動清理

部分索引

您的“表達索引”和“部分錶達索引”沒有用。我們不需要closed IS NULL作為實際的索引表達式（它總是true在這裡）。該表達式只會增加成本，不會增加收益。

第一個，普通的部分索引是更有用的變體。但不要closed用作索引表達式（同樣，總是NULL在這裡）。相反，使用任何可能對其他查詢有用的列，最好不要更新以避免額外的成本和索引膨脹。在沒有其他有用的應用程序的情況下，主鍵列id是自然的候選者：

CREATE INDEX closed_index ON tickets (id) WHERE closed IS NULL;

或者，如果id沒有用，請考慮使用常量：

CREATE INDEX closed_index ON tickets ((1)) WHERE closed IS NULL;

這使得實際的索引列像其他被解僱的變體一樣無用 - 但它避免了所有額外的成本和依賴性。有關的：

• 從 pg_class.reltuples 獲取給定條件的計數估計

我可能會嘗試：

更新到您更新的問題 - 只有當您在有問題的行上沒有很多其他寫入時才有意義（添加的列updated讓comments_count我懷疑。）

id使用和其他相關列（少數和小）作為索引表達式創建部分索引，並通過合適的查詢利用它來獲取僅索引掃描：

CREATE INDEX closed_index ON tickets (id, title, created, updated)
WHERE closed IS NULL;

VACUUM ANALYZE tickets;   -- just to prove idx-only is possible

SELECT id, title, created, updated
    , NULL::timestamp AS closed  -- redundant, rather drop it
FROM   tickets
WHERE  closed IS NULL;

我們不需要SELECT *，closed IS NULL由WHERE子句給出。所以我們可以在快速的僅索引掃描中使用微小的部分索引- 假設你滿足先決條件（這就是我投入VACUUM更新可見性地圖的原因）。這是一種罕見的情況，其中讀取超過 ~ 5% 的所有行的查詢仍然愉快地使用索引（甚至包括整個表）。

您的設計似乎存在冗餘，應該可以進行簡化。

這從 Postgres 9.6 開始工作，引用發行說明：

• 當索引的子句引用未編入索引的列時，允許對部分索引使用僅索引掃描（Tomas Vondra，Kyotaro Horiguchi）WHERE

例如，由 定義的索引CREATE INDEX tidx_partial ON t(b) WHERE a > 0現在可以用於指定WHERE a > 0且不使用的查詢的僅索引掃描a。以前這是不允許的，因為 a 未列為索引列。

或者您問題中的資訊具有誤導性。

有關的：

• 在具有現有數據的表上創建時未使用 PostgreSQL 部分索引
• 對值為 NULL 的布爾值進行查詢時出現意外的 Seq 掃描
• 索引掃描時 Postgres 不使用索引是更好的選擇

如果您沒有看到僅索引掃描，即使在執行後立即，VACUUM也可能是高寫入負載，可見性映射永遠不會達到允許僅索引掃描的狀態。手冊。或者您的數據庫中有另一個問題VACUUM無法完成其工作。有關的：

• Postgres：“vacuum”命令不清理死元組

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