如何让vacuum跳过无谓的index scan
背景
这两天我在整理参数的时候，发现了vacuum_cleanup_index_scale_factor这个之前一直淡出视野的参数，大概看了一下官网注释，和索引扫描有关。并且在前几天的release中，https://www.postgresql.org/docs/current/release-13-3.html，摒弃了这个参数，大意就是和autovacuum_vacuum_insert_threshold这个参数水火不容，从v14之后就不再有这个参数了。v13中，添加了autovacuum_vacuum_insert_threshold这个参数，为了防止大量insert操作后，导致的 “冻结” 炸弹，因为以前纯insert操作是不会触发vacuum的，只会触发analyze。
Disable the
vacuum_cleanup_index_scale_factorparameter and storage option (Peter Geoghegan)The notion of tracking “stale” index statistics proved to interact badly with the
autovacuum_vacuum_insert_thresholdparameter, resulting in unnecessary full-index scans and consequent degradation of autovacuum performance. The latter mechanism seems superior, so remove the stale-statistics logic. The control parameter for that,vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13, it remains present to avoid breaking existing configuration files, but it no longer does anything.
我们知道，在对表执行vacuum的时候，假如表上面有索引，也需要对所有的索引挨个进行扫描，大致过程如下：
Heap scan phase：Scan the table from the top and collect garbage tuples in memory.
Index vacuum phase：Vacuum both indexes one by one.
Heap vacuum phase：Vacuum the heap (table).
Index cleanup phase：Cleanup both indexes one by one.
Heap truncation phase：Truncate empty pages at the end of the table.
在第一个堆扫描阶段，可以配合vm文件跳过一些没有包含死元组的页面，而对于索引的vacuum和cleanup阶段，需要一个完整的索引扫描（取决于具体的索引访问方式），因为我们知道索引是不存有可见性信息的，这也是为啥老是强调Index-only-scan的原因。部分引自Masahiko Sawada的Blog：https://www.2ndquadrant.com/en/blog/parallelism-comes-to-vacuum/
比如Btree索引，就需要一个完整的索引扫描以清除垃圾，再做索引清理。可想而知，对于大表，清理是很慢的，所以v13里引入了并行索引扫描（不过vacuum full是不能并行的）。而vacuum_cleanup_index_scale_factor这个参数是从v11引入的一个GUC参数，这是个啥玩意？
认知
Specifies the fraction of the total number of heap tuples counted in the previous statistics collection that can be inserted without incurring an index scan at the VACUUM cleanup stage. This setting currently applies to B-tree indexes only.
If no tuples were deleted from the heap, B-tree indexes are still scanned at the VACUUM cleanup stage when at least one of the following conditions is met: the index statistics are stale, or the index contains deleted pages that can be recycled during cleanup. Index statistics are considered to be stale if the number of newly inserted tuples exceeds the vacuum_cleanup_index_scale_factor fraction of the total number of heap tuples detected by the previous statistics collection. The total number of heap tuples is stored in the index meta-page. Note that the meta-page does not include this data until VACUUM finds no dead tuples, so B-tree index scan at the cleanup stage can only be skipped if the second and subsequent VACUUM cycles detect no dead tuples.
The value can range from 0 to 10000000000. When vacuum_cleanup_index_scale_factor is set to 0, index scans are never skipped during VACUUM cleanup. The default value is 0.1.
该参数指定了有多大比例的元组，可以在vacuum cleaup阶段直接插入而不会触发索引扫描，这些比例的元组基于前一次统计信息采集到的元组数量。该参数目前仅仅适用于B-tree索引。
如果没有行被删除，在碰到下面的情形之一的话，也会触发vacuum清理阶段对B-tree索引的扫描：
1、索引的统计信息是陈旧的
2、索引包含了已经被删除并且可以在清理阶段被回收的页面
如果新插入的元组数量超过了之前统计信息所收集到的元组总数所对应的vacuum_cleanup_index_scale_factor参数指定的比例，则索引统计信息被认为是过时的。
另外，总行数会存储在索引的meta-page中。注意：直到vacuum发现没有死元组时，meta-page才会包含此数据，因此只有在第二次及之后的vacuum周期没有发现死元组，vacuum清理阶段才会跳过对B-tree的索引扫描。
这个值的大小是 0 到 10000000000。如果把vacuum_cleanup_index_scale_factor设为0，那么在vacuum的清理阶段，都不会跳过对索引的扫描。默认值是0.1
简单翻译一下，可以得知该参数，仅仅适用于Btree索引，毕竟Btree打天下嘛。还有一个关键信息，索引的meta-page中会存储表的总行数，last_cleanup_num_tuples可以看到是1000000行，注意需要vacuum才会更新：
postgres=# create table mytest(id int primary key);
CREATE TABLE
postgres=# insert into mytest values(generate_series(1,1000000));
INSERT 0 1000000
postgres=# analyze mytest;
ANALYZE
postgres=# select * from bt_metap('mytest_pkey');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+-------------+-------------------------+---------------
340322 | 4 | 412 | 2 | 412 | 2 | 0 | -1 | t
(1 row)
postgres=# vacuum verbose mytest;
INFO: vacuuming "public.mytest"
INFO: index "mytest_pkey" now contains 1000000 row versions in 2745 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "mytest": found 0 removable, 1000000 nonremovable row versions in 4425 out of 4425 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 11016
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.12 s, system: 0.00 s, elapsed: 0.12 s.
VACUUM
postgres=# select * from bt_metap('mytest_pkey');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+-------------+-------------------------+---------------
340322 | 4 | 412 | 2 | 412 | 2 | 0 | 1000000 | t
(1 row)
另外，可以得知，索引假如被认为是过时的，也会在vacuum清理阶段触发对索引的扫描，也就是
如果新插入的元组数量超过了之前统计信息所收集到的元组总数所对应的vacuum_cleanup_index_scale_factor参数指定的比例，则索引统计信息被认为是过时的。
所以，公式就是：(insert_tuples - previous_total_tuples) / previous_total_tuples > vacuum_cleanup_index_scale_factor时，vacuum cleanup阶段才需要去扫描索引，更新index stats信息(包括meta page计数器信息)
至此大概明了了，vacuum_cleanup_index_scale_factor这个参数，可以加速vacuum，引用一下德哥原话：
因此，对于大量insert，没有update、delete操作的表的vacuum，或者常规静态表的vacuum会快很多，因为不需要scan index了。
实践
第二个索引vacuum_cleanup_index_scale_factor设为了100000，所以对应到这个公式(insert_tuples - previous_total_tuples) / previous_total_tuples > vacuum_cleanup_index_scale_factor，因为是新建的表，所以previous_total_tuples为0，那么我新插入的行数只要超过了100000行，就会跳过对Btree索引的扫描，验证一下：
postgres=# create table mytest(id int);
CREATE TABLE
postgres=# create table mytest2(id int);
CREATE TABLE
postgres=# create index myidx1 on mytest(id) with (vacuum_cleanup_index_scale_factor=0);
CREATE INDEX
postgres=# create index myidx2 on mytest2(id) with (vacuum_cleanup_index_scale_factor=100000);
CREATE INDEX
postgres=# insert into mytest values(generate_series(1,10000000));
INSERT 0 10000000
postgres=# insert into mytest2 values(generate_series(1,10000000));
INSERT 0 10000000
vacuum看一下时间对比，可以看到，对于mytest2表，效果十分明显，vacuum的速度比mytest的速度整缩小了接近6倍之多！
postgres=# vacuum verbose mytest;
INFO: vacuuming "public.mytest"
INFO: index "myidx1" now contains 10000000 row versions in 27421 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.03 s, system: 0.05 s, elapsed: 0.20 s.
INFO: "mytest": found 0 removable, 178 nonremovable row versions in 1 out of 44248 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 11035
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.04 s, system: 0.05 s, elapsed: 0.20 s.
VACUUM
Time: 292.031 ms
postgres=# vacuum verbose mytest2;
INFO: vacuuming "public.mytest2"
INFO: "mytest2": found 0 removable, 178 nonremovable row versions in 1 out of 44248 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 11035
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
Time: 46.758 ms
注意观察verbose的详细信息，对于mytest表，中间多了这么一坨，这一坨也正是对应了skip index scan：
INFO: index "myidx1" now contains 10000000 row versions in 27421 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.03 s, system: 0.05 s, elapsed: 0.20 s.
所以明白了，该参数的作用，对于大量insert，没有update、delete操作的表的vacuum，或者常规静态表的vacuum会快很多，因为不需要scan index了。
另外，还有一点值得注意
总行数会存储在索引的meta-page中。注意：直到vacuum发现没有死元组时，meta-page才会包含此数据，因此只有在第二次及之后的vacuum周期没有发现死元组，vacuum清理阶段才会跳过对B-tree的索引扫描。
第一次vacuum，去做了清理dead tuples的动作，第二次vacuum，才更新了meta-page的数据
postgres=# create table mytest3(id int primary key);
CREATE TABLE
postgres=# insert into mytest3 values(generate_series(1,10000));
INSERT 0 10000
postgres=# delete from mytest3 where id = 1;
DELETE 1
postgres=# vacuum verbose mytest3;
INFO: vacuuming "public.mytest3"
INFO: scanned index "mytest3_pkey" to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: "mytest3": removed 1 row versions in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: index "mytest3_pkey" now contains 9999 row versions in 30 pages
DETAIL: 1 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "mytest3": found 1 removable, 9999 nonremovable row versions in 45 out of 45 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 11045
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# select * from bt_metap('mytest3_pkey');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+-------------+-------------------------+---------------
340322 | 4 | 3 | 1 | 3 | 1 | 0 | 0 | t
(1 row)
postgres=# vacuum verbose mytest3;
INFO: vacuuming "public.mytest3"
INFO: index "mytest3_pkey" now contains 9999 row versions in 30 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "mytest3": found 0 removable, 56 nonremovable row versions in 1 out of 45 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 11046
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# select * from bt_metap('mytest3_pkey');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples | allequalimage
--------+---------+------+-------+----------+-----------+-------------+-------------------------+---------------
340322 | 4 | 3 | 1 | 3 | 1 | 0 | 9833 | t
(1 row)
而且第二次vacuum跳过了这么一段，也就是对索引的扫描。
INFO: scanned index "mytest3_pkey" to remove 1 row versions
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO: "mytest3": removed 1 row versions in 1 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
小结
一阵操作之后，vacuum_cleanup_index_scale_factor这个参数的好处如下：
加速含有大量insert，没有update、delete操作的表的vacuum
加速静态表的vacuum
但是这个参数看似很美好，殊不知彼之蜜糖汝之砒霜，在前几天发布的release中，有一个commit移除了这个参数，并且在v14中也再也看不到了
Peter Geoghegan pushed:
Don't consider newly inserted tuples in nbtree VACUUM. Remove the entire idea of "stale stats" within nbtree VACUUM (stop caring about stats involving the number of inserted tuples). Also remove the vacuum_cleanup_index_scale_factor GUC/param on the master branch (though just disable them on postgres 13). The vacuum_cleanup_index_scale_factor/stats interface made the nbtree AM partially responsible for deciding when pg_class.reltuples stats needed to be updated. This seems contrary to the spirit of the index AM API, though -- it is not actually necessary for an index AM's bulk delete and cleanup callbacks to provide accurate stats when it happens to be inconvenient. The core code owns that. (Index AMs have the authority to perform or not perform certain kinds of deferred cleanup based on their own considerations, such as page deletion and recycling, but that has little to do with pg_class.reltuples/num_index_tuples.) This issue was fairly harmless until the introduction of the autovacuum_vacuum_insert_threshold feature by commit b07642db, which had an undesirable interaction with the vacuum_cleanup_index_scale_factor mechanism: it made insert-driven autovacuums perform full index scans, even though there is no real benefit to doing so. This has been tied to a regression with an append-only insert benchmark [1]. Also have remaining cases that perform a full scan of an index during a cleanup-only nbtree VACUUM indicate that the final tuple count is only an estimate. This prevents vacuumlazy.c from setting the index's pg_class.reltuples in those cases (it will now only update pg_class when vacuumlazy.c had TIDs for nbtree to bulk delete). This arguably fixes an oversight in deduplication-related bugfix commit 48e12913. [1] https://smalldatum.blogspot.com/2021/01/insert-benchmark-postgres-is-still.html Author: Peter Geoghegan pg@bowt.ie Reviewed-By: Masahiko Sawada sawada.mshk@gmail.com Discussion: https://postgr.es/m/CAD21AoA4WHthN5uU6+WScZ7+J_RcEjmcuH94qcoUPuB42ShXzg@mail.gmail.com Backpatch: 13-, where autovacuum_vacuum_insert_threshold was added. https://git.postgresql.org/pg/commitdiff/9f3665fbfc34b963933e51778c7feaa8134ac885
大意就是会降低autovacuum的性能，而且和v13引入的autovacuum_vacuum_insert_threshold的机制相冲突，让原来insert触发的autovacuum使用了full index scan。另外，在追加插入的benchmark测试中，可能还会有附加的性能影响。
所以，可以简单总结一下这个参数：
可以加速含有大量insert，没有update、delete操作的表的vacuum
可以加速静态表的vacuum
对于v13以下的版本，可以调整这个参数，进行调优，因为还没有引入autovacuum_vacuum_insert_threshold
v13以后，就不要调整了，或者干脆关闭，设为0，和autovacuum_vacuum_insert_threshold冲突，社区作者也更认可autovacuum_vacuum_insert_threshold这个机制，减缓因为insert操作导致的“冻结”炸弹带来的负面影响
在append-only insert的benchmark测试中，关闭该参数
不过，抛开可能的风险，光从这个机制也可以看出来，PostgreSQL的commiters一直在对vacuum进行不断的优化，包括并行vacuum、vacuum_cleanup_index_scale_factor、vm可见性文件等等，让PostgreSQL一直被诟病的vacuum不断得到优化，让广大使用者可以接受PostgreSQL的Heap引擎和比较独特的MVCC处理方式，等到zheap出来之时，这些老毛病相信就可以彻底抛掷脑后了~
最后
这次事情过后（再次感谢CCTV、“神医”和占元）——参照前一篇文章，我决定创建一个关于PostgreSQL学徒公众号的交流群，美其名曰——PG学徒纠错探讨群，因为公众号没有留言功能，要么就公众号迁移，要么嵌入小程序，我太懒了，又懒得折腾。
对于那些饱受文字摧残想逼逼几句的，欢迎入群，闲聊扯淡，吃枣药丸😀
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
