未使用但影響查詢的索引

我有一個 PostgreSQL 9.3 表，其中包含一些數字和一些附加數據：

CREATE TABLE mytable (
   myid BIGINT,
   somedata BYTEA
)

該表目前大約有 10M 條記錄，佔用 1GB 磁碟空間。myid不是連續的。

我想計算 100000 個連續數字的每個塊中有多少行：

SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;

這將返回大約 3500 行。

我注意到某個索引的存在顯著加快了這個查詢，即使查詢計劃根本沒有提到它。沒有索引的查詢計劃：

db=> EXPLAIN (ANALYZE TRUE, VERBOSE TRUE) SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;
                                                              QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=1636639.92..1709958.65 rows=496942 width=8) (actual time=6783.763..8888.841 rows=3460 loops=1)
  Output: ((myid / 100000)), count(*)
  ->  Sort  (cost=1636639.92..1659008.91 rows=8947594 width=8) (actual time=6783.752..8005.831 rows=8947557 loops=1)
        Output: ((myid / 100000))
        Sort Key: ((mytable.myid / 100000))
        Sort Method: external merge  Disk: 157440kB
        ->  Seq Scan on public.mytable  (cost=0.00..236506.92 rows=8947594 width=8) (actual time=0.020..1674.838 rows=8947557 loops=1)
              Output: (myid / 100000)
Total runtime: 8914.780 ms
(9 rows)

指數：

db=> CREATE INDEX myindex ON mytable ((myid/100000));
db=> VACUUM ANALYZE;

新的查詢計劃：

db=> EXPLAIN (ANALYZE TRUE, VERBOSE TRUE) SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;
                                                           QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=281242.99..281285.97 rows=3439 width=8) (actual time=3190.189..3190.800 rows=3460 loops=1)
  Output: ((myid / 100000)), count(*)
  ->  Seq Scan on public.mytable  (cost=0.00..236505.56 rows=8947485 width=8) (actual time=0.026..1659.571 rows=8947557 loops=1)
        Output: (myid / 100000)
Total runtime: 3190.975 ms
(5 rows)

因此，查詢計劃和執行時差異很大（幾乎是三倍），但都沒有提到索引。這種行為在我的開發機器上完全可以重現：我經歷了幾個循環刪除索引、多次測試查詢、重新創建索引、再次測試多次查詢。這裡發生了什麼事？

**VACUUM ANALYZE**在您的範例中有所不同。另外，正如@jjanes 提供的那樣，功能索引的附加統計資訊。根據文件：

pg_statistic還儲存有關索引表達式值的統計數據。這些被描述為好像它們是實際的數據列；特別是starelid引用索引。但是，不會為普通的非表達式索引列創建條目，因為它與基礎表列的條目是多餘的。

但是，創建索引本身並不會導致 Postgres 收集統計資訊。嘗試：

CREATE INDEX myindex ON mytable ((myid/100000));
SELECT * FROM pg_statistic WHERE starelid = 'myindex'::regclass;

在您執行您的第一個ANALYZE（或VACUUM ANALYZE，或 autovacuum 守護程序啟動）之前不返回任何內容。

ANALYZE mytable;
SELECT * FROM pg_statistic WHERE starelid = 'myindex'::regclass;

現在您將看到添加的統計資訊。

由於無論如何都必須讀取整個表，因此 Postgres 將使用順序掃描，除非它期望計算 的計算myid/100000成本足夠高以進行切換，但事實並非如此。

如果索引遠小於表，那麼您唯一的其他機會將是僅索引掃描- 並且滿足僅索引掃描的先決條件。Postgres Wiki和手冊中的詳細資訊。

只要不使用該功能指數，增加統計數據的附帶好處是適度的。如果表是只讀的，成本會很低——但話又說回來，我們可能會立即看到僅索引掃描。

也許您還可以通過為mytable.myid. 那隻會產生很小的成本。更多的：

• PostgreSQL + 查詢規劃器 + 實體數量

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