Skip full index scan during cleanup of B-tree indexes when possible Vacuum of index consists from two stages: multiple (zero of more) ambulkdelete calls and one amvacuumcleanup call. When workload on particular table is append-only, then autovacuum isn't intended to touch this table. However, user may run vacuum manually in order to fill visibility map and get benefits of index-only scans. Then ambulkdelete wouldn't be called for indexes of such table (because no heap tuples were deleted), only amvacuumcleanup would be called In this case, amvacuumcleanup would perform full index scan for two objectives: put recyclable pages into free space map and update index statistics.
This patch allows btvacuumclanup to skip full index scan when two conditions are satisfied: no pages are going to be put into free space map and index statistics isn't stalled. In order to check first condition, we store oldest btpo_xact in the meta-page. When it's precedes RecentGlobalXmin, then there are some recyclable pages. In order to check second condition we store number of heap tuples observed during previous full index scan by cleanup. If fraction of newly inserted tuples is less than vacuum_cleanup_index_scale_factor, then statistics isn't considered to be stalled. vacuum_cleanup_index_scale_factor can be defined as both reloption and GUC (default). This patch bumps B-tree meta-page version. Upgrade of meta-page is performed "on the fly": during VACUUM meta-page is rewritten with new version. No special handling in pg_upgrade is required. Author: Masahiko Sawada, Alexander Korotkov Review by: Peter Geoghegan, Kyotaro Horiguchi, Alexander Korotkov, Yura Sokolov Discussion: https://www.postgresql.org/message-id/flat/CAD21AoAX+d2oD_nrd9O2YkpzHaFr=uqegr9s1rkc3o4enc5...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/857f9c36cda520030381bd8c2af20adf0ce0e1d4 Modified Files -------------- contrib/amcheck/verify_nbtree.c | 8 +- contrib/pageinspect/Makefile | 3 +- contrib/pageinspect/btreefuncs.c | 4 +- contrib/pageinspect/expected/btree.out | 16 +-- contrib/pageinspect/pageinspect--1.6--1.7.sql | 26 +++++ contrib/pageinspect/pageinspect.control | 2 +- contrib/pgstattuple/expected/pgstattuple.out | 10 +- doc/src/sgml/config.sgml | 25 +++++ doc/src/sgml/pageinspect.sgml | 16 +-- doc/src/sgml/ref/create_index.sgml | 15 +++ src/backend/access/common/reloptions.c | 13 ++- src/backend/access/nbtree/nbtinsert.c | 12 +++ src/backend/access/nbtree/nbtpage.c | 150 ++++++++++++++++++++++++-- src/backend/access/nbtree/nbtree.c | 118 ++++++++++++++++++-- src/backend/access/nbtree/nbtxlog.c | 6 +- src/backend/utils/init/globals.c | 2 + src/backend/utils/misc/guc.c | 10 ++ src/include/access/nbtree.h | 11 +- src/include/access/nbtxlog.h | 4 + src/include/miscadmin.h | 2 + src/include/utils/rel.h | 2 + src/test/regress/expected/btree_index.out | 29 +++++ src/test/regress/sql/btree_index.sql | 19 ++++ 23 files changed, 458 insertions(+), 45 deletions(-)