Various improvements of skipping index scan during vacuum technics

- Change vacuum_cleanup_index_scale_factor GUC to PGC_USERSET.
  vacuum_cleanup_index_scale_factor GUC was defined as PGC_SIGHUP.  But this
  GUC affects not only autovacuum.  So it might be useful to change it from user
  session in order to influence manually runned VACUUM.
- Add missing tab-complete support for vacuum_cleanup_index_scale_factor
  reloption.
- Fix condition for B-tree index cleanup.
  Zero value of vacuum_cleanup_index_scale_factor means that user wants B-tree
  index cleanup to be never skipped.
- Documentation and comment improvements

Authors: Justin Pryzby, Alexander Korotkov, Liudmila Mantrova
Reviewed by: all authors and Robert Haas
Discussion: https://www.postgresql.org/message-id/flat/20180502023025.GD7631%40telsasoft.com

4 files changed, 16 insertions, 13 deletions

diff --git a/src/backend/access/nbtree/nbtpage.c b/src/backend/access/nbtree/nbtpage.c
index 3bcc56e9d27..22b4a7578f3 100644
--- a/src/backend/access/nbtree/nbtpage.c
+++ b/src/backend/access/nbtree/nbtpage.c
@@ -189,7 +189,7 @@ _bt_update_meta_cleanup_info(Relation rel, TransactionId oldestBtpoXact,
 	if (metad->btm_version < BTREE_VERSION)
 		_bt_upgrademetapage(metapg);
 
-	/* update cleanup-related infromation */
+	/* update cleanup-related information */
 	metad->btm_oldest_btpo_xact = oldestBtpoXact;
 	metad->btm_last_cleanup_num_heap_tuples = numHeapTuples;
 	MarkBufferDirty(metabuf);
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index e5dce00876e..27a3032e42a 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -818,10 +818,11 @@ _bt_vacuum_needs_cleanup(IndexVacuumInfo *info)
 		float8		cleanup_scale_factor;
 
 		/*
-		 * If table receives large enough amount of insertions and no cleanup
-		 * was performed, then index might appear to have stalled statistics.
-		 * In order to evade that, we perform cleanup when table receives
-		 * vacuum_cleanup_index_scale_factor fractions of insertions.
+		 * If table receives enough insertions and no cleanup was performed,
+		 * then index would appear have stale statistics.  If scale factor
+		 * is set, we avoid that by performing cleanup if the number of
+		 * inserted tuples exceeds vacuum_cleanup_index_scale_factor fraction
+		 * of original tuples count.
 		 */
 		relopts = (StdRdOptions *) info->index->rd_options;
 		cleanup_scale_factor = (relopts &&
@@ -829,7 +830,7 @@ _bt_vacuum_needs_cleanup(IndexVacuumInfo *info)
 			? relopts->vacuum_cleanup_index_scale_factor
 			: vacuum_cleanup_index_scale_factor;
 
-		if (cleanup_scale_factor < 0 ||
+		if (cleanup_scale_factor <= 0 ||
 			metad->btm_last_cleanup_num_heap_tuples < 0 ||
 			info->num_heap_tuples > (1.0 + cleanup_scale_factor) *
 			metad->btm_last_cleanup_num_heap_tuples)
@@ -870,8 +871,8 @@ btbulkdelete(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
 					 &oldestBtpoXact);
 
 		/*
-		 * Update cleanup-related information in metapage. These information
-		 * is used only for cleanup but keeping up them to date can avoid
+		 * Update cleanup-related information in metapage. This information
+		 * is used only for cleanup but keeping them up to date can avoid
 		 * unnecessary cleanup even after bulkdelete.
 		 */
 		_bt_update_meta_cleanup_info(info->index, oldestBtpoXact,
@@ -899,8 +900,8 @@ btvacuumcleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *stats)
 	 * If btbulkdelete was called, we need not do anything, just return the
 	 * stats from the latest btbulkdelete call.  If it wasn't called, we might
 	 * still need to do a pass over the index, to recycle any newly-recyclable
-	 * pages and to obtain index statistics.  _bt_vacuum_needs_cleanup checks
-	 * is there are newly-recyclable or stalled index statistics.
+	 * pages or to obtain index statistics.  _bt_vacuum_needs_cleanup
+	 * determines if either are needed.
 	 *
 	 * Since we aren't going to actually delete any leaf items, there's no
 	 * need to go through all the vacuum-cycle-ID pushups.
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index f496ad62815..7cd2d2d80ef 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3238,7 +3238,7 @@ static struct config_real ConfigureNamesReal[] =
 	},
 
 	{
-		{"vacuum_cleanup_index_scale_factor", PGC_SIGHUP, AUTOVACUUM,
+		{"vacuum_cleanup_index_scale_factor", PGC_USERSET, AUTOVACUUM,
 			gettext_noop("Number of tuple inserts prior to index cleanup as a fraction of reltuples."),
 			NULL
 		},
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b431efc9832..7bb47eadc6c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1855,13 +1855,15 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH_CONST("(");
 	/* ALTER INDEX <foo> SET|RESET ( */
 	else if (Matches5("ALTER", "INDEX", MatchAny, "RESET", "("))
-		COMPLETE_WITH_LIST7("fillfactor", "recheck_on_update",
+		COMPLETE_WITH_LIST8("fillfactor", "recheck_on_update",
+							"vacuum_cleanup_index_scale_factor", /* BTREE */
 							"fastupdate", "gin_pending_list_limit", /* GIN */
 							"buffering",	/* GiST */
 							"pages_per_range", "autosummarize"	/* BRIN */
 			);
 	else if (Matches5("ALTER", "INDEX", MatchAny, "SET", "("))
-		COMPLETE_WITH_LIST7("fillfactor =", "recheck_on_update =",
+		COMPLETE_WITH_LIST8("fillfactor =", "recheck_on_update =",
+							"vacuum_cleanup_index_scale_factor =", /* BTREE */
 							"fastupdate =", "gin_pending_list_limit =", /* GIN */
 							"buffering =",	/* GiST */
 							"pages_per_range =", "autosummarize ="	/* BRIN */