在具有現有數據的表上創建時未使用 PostgreSQL 部分索引

在 PostgreSQL 9.3 中，我試圖在一個很少使用的（佔總記錄的 0.00001%）布爾列上創建一個有效的索引。為此，我在 SO 上發現了這篇文章：https ://stackoverflow.com/a/12026593/808921

我正在嘗試使用 Erwin Brandstetter 推薦的 PostgreSQL 的“部分索引”功能。我已經有一個包含幾百萬條記錄的表，我想將索引添加到該表中，如下所示：

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision) 
WHERE deprovision = 0;

（絕大多數記錄都會有deprovision = 1）

問題是，當我嘗試將此索引與最簡單的查詢一起使用時，PostgreSQL 的行為就好像它不存在一樣：

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

真正奇怪的是，我發現如果這個索引是在表中有數據之前創建的，那麼它確實可以正常工作。不相信我？以下是一些證明這一點的 SQL Fiddle 條目：

插入後創建的部分索引（索引不起作用）

在插入之前創建的部分索引（索引正常工作）

在這兩個中，只需展開“查看執行計劃”連結即可查看我在說什麼。

所以，我的問題是 - 為了讓 PostgreSQL 在創建索引之前開始在其中包含數據的表上使用部分索引，我必須做些什麼？

順便說一句，我也是 SQL Fiddle 的開發人員，這個問題與我正在為此進行的新開發工作有關。

**ANALYZE**添加索引後執行。並確保該列deprovision 具有統計資訊。如何驗證？

基本統計pg_class：

SELECT relname, relkind, reltuples, relpages
FROM   pg_class
WHERE  oid = 'schema_defs'::regclass;

pg_stats( )中每列的數據直方圖pg_statistics：

SELECT attname, inherited, n_distinct
    , array_to_string(most_common_vals, E'\n') AS most_common_vals
FROM   pg_stats
WHERE  tablename = 'schema_defs'
AND    attname = 'deprovision';

手冊：

PostgreSQL 查詢計劃器依靠有關表內容的統計資訊來生成良好的查詢計劃。這些統計資訊由ANALYZE命令收集，該命令可以自行呼叫，也可以作為VACUUM. 擁有相當準確的統計數據很重要，否則計劃選擇不當可能會降低數據庫性能。

如果啟用了 autovacuum 守護程序，它將ANALYZE 在表的內容髮生充分變化時自動發出命令。但是，管理員可能更喜歡依賴手動調度的 ANALYZE操作，特別是如果知道表上的更新活動不會影響“有趣”列的統計資訊時。守護程序ANALYZE嚴格根據插入或更新的行數進行調度；它不知道這是否會導致有意義的統計變化。

在您的情況下，僅分析一列就可以完成這項工作：

ANALYZE table_name (deprovision);

在使用它時，在 column 上有索引是沒有意義的deprovision。給定謂詞WHERE deprovision = 0，它不攜帶附加資訊。您不妨使用常量表達式：

CREATE INDEX schema_defs_deprovision ON schema_defs ((true)) 
WHERE deprovision = 0;

只是一個概念證明。這將不再有用。在這種特殊情況下，您根本不需要索引列，但您必須提供至少一個列或表達式。因此，請使用主鍵（因為它不會更改並且無論如何都會被索引，因此您不會引入更多限制/成本成本）或任何其他可能對查詢有用的小列（<= 8 字節）。

CREATE INDEX schema_defs_deprovision ON schema_defs (id) 
WHERE deprovision = 0;

sqlfiddle.com

展示小提琴具有誤導性。

在插入之前創建的部分索引（索引正常工作）

您的展示表只有 4 行。Postgres不應該使用索引。類似的問題，正好相反。Postgres 在創建表後沒有立即統計數據 - 直到第一次執行ANALYZE. 然後它知道只有 4 行並且不會再觸及索引。

那麼為什麼它在您的第二個展示中正常工作？手冊：

出於效率原因，reltuples並且relpages不會即時更新，因此它們通常包含一些過時的值。它們由VACUUM、ANALYZE和一些 DDL 命令**（例如 CREATE INDEX**.

大膽強調我的。如果您在插入行後pg_class創建索引，則會更新其中的那些基本統計資訊。但只有那些，而不是詳細的統計數據pg_statistic：

中的條目pg_statistic由ANALYZEandVACUUM ANALYZE 命令更新，並且即使在新更新時也始終是近似的。

為了使 Postgres 使用部分索引（特別是它的原始形式，它對其他任何東西都沒有用處），您還需要數據直方圖來pg_statistic通知查詢計劃器，這deprovision = 0實際上是一種罕見的情況，因此使用索引是值得的。

Autovacuum會處理這個問題。它會自動安排VACUUM時間ANALYZE。ANALYZE但是在寫入表和下一次執行之間有一個時間範圍（取決於設置和負載） 。如果您在創建表或更改表後立即執行查詢，則這些最新更改不會反映在統計資訊中。沒關係，如果這不會以相關方式改變統計數據。如果確實如此，例如在創建大INSERT表後或創建表後立即執行，ANALYZE請手動執行以獲得正確的查詢計劃。

請注意，臨時表根本不被 autovacuum 覆蓋。如果需要，您總是需要ANALYZE手動執行它們：

• 在 9.1 下是否仍建議使用正常 VACUUM ANALYZE？

我不知道您如何配置 autovacuum 以及是否/何時ANALYZE手動執行。但我過去注意到 sqlfiddle 可能由於缺少/過時的統計資訊而產生誤導。

我會很感興趣如何ANALYZE在 sqlfiddle 的幕後處理。最好不要做任何特別的事情，但歡迎提供一些資訊。每個可用的 RDBMS 版本可能有一個基本網頁？

展示

我創建了一個SQL Fiddle來展示各種統計數據的CREATE INDEX影響ANALYZE。

對我來說，效果（至少）在第一次執行時就顯示出來了。在以後的執行中可能無法重現，您必須創建一個新模式並再次執行。

首先，我們看不到基本統計數據pg_class：

relname      reltuples  relpages
schema_defs  0          0

deprovisionin也沒有任何條目pg_statistics（沒有結果）。

Postgres 不知道表中有什麼，並且預設使用索引 - 這是一個糟糕的選擇！

之後CREATE INDEX，我們看到了基本的統計數據，但在 中仍然沒有數據直方圖pg_statistics。

在ANALYZE我們看到兩者之後。

有了適當的統計數據，Postgres 現在使用順序掃描（不錯的選擇，即使有索引 - 對於這麼少的行，它會更昂貴）。

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