为什么在使用VACUUM ANALYZE时会改变查询计划而ANALYZE不会？

Question

我希望利用Postgres中索引扫描的能力，并在一个表上进行了实验：

CREATE TABLE dest.contexts
(
  id integer NOT NULL,
  phrase_id integer NOT NULL,
  lang character varying(5) NOT NULL,
  ranking_value double precision,
  index_min integer,
  index_max integer,
  case_sensitive boolean,
  is_enabled boolean,
  is_to_sync boolean NOT NULL DEFAULT true
);

insert into dest.contexts select * from source.contexts;

alter table dest.contexts
  add constraint pk_contexts primary key (id, phrase_id, lang);

 CREATE INDEX idx_contexts_
  ON dest.contexts
  USING btree
  (id, is_enabled, lang, phrase_id, ranking_value, index_min, index_max, case_sensitive);

这个索引包括我在下一个查询中想要使用的所有列：

explain analyze
select ranking_value, index_min, index_max, case_sensitive
from dest.contexts
where id = 456 and is_enabled

创建计划后，我立即进行检查：

Bitmap Heap Scan on contexts  (cost=4.41..31.46 rows=12 width=17) (actual time=0.045..0.045 rows=0 loops=1)
  Recheck Cond: (id = 456)
  Filter: is_enabled
  ->  Bitmap Index Scan on idx_contexts_  (cost=0.00..4.40 rows=12 width=0) (actual time=0.038..0.038 rows=0 loops=1)
        Index Cond: ((id = 456) AND (is_enabled = true))
Planning time: 0.631 ms
Execution time: 0.093 ms

很奇怪，但没关系...

几秒钟后，它会自动转换为另一个（自动清理？）

Index Scan using pk_contexts on contexts  (cost=0.28..17.93 rows=6 width=17) (actual time=0.027..0.027 rows=0 loops=1)
  Index Cond: (id = 456)
  Filter: is_enabled
Planning time: 0.185 ms
Execution time: 0.070 ms

我尝试强制使用索引扫描：

analyze dest.contexts

但是这并没有改变任何事情。然后我做了什么。

vacuum verbose analyze dest.contexts;

INFO:  vacuuming "dest.contexts"
INFO:  index "pk_contexts" now contains 4845 row versions in 21 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_contexts_" now contains 4845 row versions in 37 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "contexts": found 0 removable, 4845 nonremovable row versions in 41 out of 41 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "dest.contexts"
INFO:  "contexts": scanned 41 of 41 pages, containing 4845 live rows and 0 dead rows; 4845 rows in sample, 4845 estimated total rows

我终于得到了想要的：

Index Only Scan using idx_contexts_ on contexts  (cost=0.28..4.40 rows=6 width=17) (actual time=0.014..0.014 rows=0 loops=1)
  Index Cond: ((id = 456) AND (is_enabled = true))
  Filter: is_enabled
  Heap Fetches: 0
Planning time: 0.247 ms
Execution time: 0.052 ms

以下是问题：

1. 为什么analyze不教它使用大型“全覆盖”索引？
2. 为什么vacuum analyze要这样做？
3. 我的表是通过一次大的插入操作填充的。为什么vacuum会做任何事情呢？在我看来，那儿没有什么需要清理的。

- greatvovan

---

可能是由于 https://www.postgresql.org/docs/9.6/static/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP 引起的。 - wildplasser

Answer 1

分析模块负责收集统计信息，分析结果会影响查询计划和计划成本。

索引不包含元组的可见性信息。因此，即使选择中的所有列都在索引中，也需要读取数据页并对结果集进行过滤。如果页面中的所有元组都可见，则Postgres无需进行其他过滤操作。Postgres使用可见性映射（vm）来实现这一点。

在回收死元组时，Vacuum使用并更新可见性映射。因此，如果可能的话，vacuum会更改查询计划以使用仅索引扫描。

参考链接：https://www.postgresql.org/docs/9.6/static/sql-vacuum.html，https://www.postgresql.org/docs/9.6/static/storage-vm.html。

- samed.yildirim