v6-0003-Skip-index-vacuuming-dynamically.patch

From 88ca75aa6dbac1cdf193f33c7ffbc28da269742c Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Fri, 19 Mar 2021 14:51:44 -0700
Subject: [PATCH v6 3/4] Skip index vacuuming dynamically.

Author: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-By: Peter Geoghegan <pg@bowt.ie>
Discussion: https://postgr.es/m/CAD21AoAtZb4+HJT_8RoOXvu4HM-Zd4HKS3YSMCH6+-W=bDyh-w@mail.gmail.com
---
 src/backend/access/heap/vacuumlazy.c | 127 ++++++++++++++++++++++-----
 1 file changed, 107 insertions(+), 20 deletions(-)

diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 132cfcba16..ac250d0fab 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -131,6 +131,12 @@
  */
 #define PREFETCH_SIZE			((BlockNumber) 32)
 
+/*
+ * The threshold of the percentage of heap blocks having LP_DEAD line pointer
+ * above which index vacuuming goes ahead.
+ */
+#define SKIP_VACUUM_PAGES_RATIO		0.01
+
 /*
  * DSM keys for parallel vacuum.  Unlike other parallel execution code, since
  * we don't need to worry about DSM keys conflicting with plan_node_id we can
@@ -385,8 +391,10 @@ static void lazy_scan_heap(Relation onerel, VacuumParams *params,
 						   bool aggressive);
 static void lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 									 Relation *Irel, int nindexes,
-									 LVParallelState* lps,
-									 VacOptTernaryValue index_cleanup);
+									 LVParallelState *lps,
+									 VacOptTernaryValue index_cleanup,
+									 BlockNumber has_dead_items_pages,
+									 bool onecall);
 static void lazy_vacuum_heap(Relation onerel, LVRelStats *vacrelstats);
 static bool lazy_check_needs_freeze(Buffer buf, bool *hastup,
 									LVRelStats *vacrelstats);
@@ -1349,7 +1357,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 				next_fsm_block_to_vacuum;
 	PGRUsage	ru0;
 	Buffer		vmbuffer = InvalidBuffer;
-	bool		skipping_blocks;
+	bool		skipping_blocks,
+				have_vacuumed_indexes = false;
 	xl_heap_freeze_tuple *frozen;
 	StringInfoData buf;
 	const int	initprog_index[] = {
@@ -1363,7 +1372,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 
 	/* Counters of # blocks in onerel: */
 	BlockNumber empty_pages,
-				vacuumed_pages;
+				vacuumed_pages,
+				has_dead_items_pages;
 
 	pg_rusage_init(&ru0);
 
@@ -1378,7 +1388,7 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 						vacrelstats->relnamespace,
 						vacrelstats->relname)));
 
-	empty_pages = vacuumed_pages = 0;
+	empty_pages = vacuumed_pages = has_dead_items_pages = 0;
 
 	/* Initialize counters */
 	c.num_tuples = 0;
@@ -1638,9 +1648,18 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 				vmbuffer = InvalidBuffer;
 			}
 
+			/*
+			 * Definitely won't be skipping index vacuuming due to finding
+			 * very few dead items during this VACUUM operation -- that's only
+			 * something that lazy_vacuum_pruned_items() is willing to do when
+			 * it is only called once during the entire VACUUM operation.
+			 */
+			have_vacuumed_indexes = true;
+
 			/* Remove the collected garbage tuples from table and indexes */
 			lazy_vacuum_pruned_items(onerel, vacrelstats, Irel, nindexes, lps,
-									 params->index_cleanup);
+									 params->index_cleanup,
+									 has_dead_items_pages, false);
 
 			/*
 			 * Vacuum the Free Space Map to make newly-freed space visible on
@@ -1777,6 +1796,17 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 		scan_prune_page(onerel, buf, vacrelstats, vistest, frozen,
 						&c, &ps, &vms);
 
+		/*
+		 * Remember the number of pages having at least one LP_DEAD line
+		 * pointer.  This could be from this VACUUM, a previous VACUUM, or
+		 * even opportunistic pruning.  Note that this is exactly the same
+		 * thing as having items that are stored in dead_tuples space, because
+		 * scan_prune_page() doesn't count anything other than LP_DEAD items
+		 * as dead (as of PostgreSQL 14).
+		 */
+		if (ps.has_dead_items)
+			has_dead_items_pages++;
+
 		/*
 		 * Step 7 for block: Set up details for saving free space in FSM at
 		 * end of loop.  (Also performs extra single pass strategy steps in
@@ -1793,7 +1823,16 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 		if (nindexes > 0 && ps.has_dead_items &&
 			params->index_cleanup != VACOPT_TERNARY_DISABLED)
 		{
-			/* Wait until lazy_vacuum_heap() to save free space */
+			/*
+			 * Wait until lazy_vacuum_heap() to save free space.
+			 *
+			 * Note: It's not in fact 100% certain that we really will call
+			 * lazy_vacuum_heap() in INDEX_CLEANUP = ON case (which is the
+			 * common case) -- lazy_vacuum_pruned_items() might opt to skip
+			 * index vacuuming (and so must skip heap vacuuming).  This is
+			 * deemed okay, because there can't be very much free space when
+			 * this happens.
+			 */
 		}
 		else
 		{
@@ -1905,7 +1944,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 	Assert(nindexes > 0 || dead_tuples->num_tuples == 0);
 	if (dead_tuples->num_tuples > 0)
 		lazy_vacuum_pruned_items(onerel, vacrelstats, Irel, nindexes, lps,
-								 params->index_cleanup);
+								 params->index_cleanup, has_dead_items_pages,
+								 !have_vacuumed_indexes);
 
 	/*
 	 * Vacuum the remainder of the Free Space Map.  We must do this whether or
@@ -1920,8 +1960,9 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 	/*
 	 * Do post-vacuum cleanup.
 	 *
-	 * Note that post-vacuum cleanup does not take place with
-	 * INDEX_CLEANUP=OFF.
+	 * Note that post-vacuum cleanup is supposed to take place when
+	 * lazy_vacuum_pruned_items() decided to skip index vacuuming, but not
+	 * with INDEX_CLEANUP=OFF.
 	 */
 	if (nindexes > 0 && params->index_cleanup != VACOPT_TERNARY_DISABLED)
 		lazy_cleanup_all_indexes(Irel, vacrelstats, lps, nindexes);
@@ -1936,8 +1977,12 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 	/*
 	 * Update index statistics.
 	 *
-	 * Note that updating the statistics does not take place with
-	 * INDEX_CLEANUP=OFF.
+	 * Note that updating the statistics takes places when
+	 * lazy_vacuum_pruned_items() decided to skip index vacuuming, but not
+	 * with INDEX_CLEANUP=OFF.
+	 *
+	 * (In practice most index AMs won't have accurate statistics from
+	 * cleanup, but the index AM API allows them to, so we must check.)
 	 */
 	if (nindexes > 0 && params->index_cleanup != VACOPT_TERNARY_DISABLED)
 		update_index_statistics(Irel, vacrelstats->indstats, nindexes);
@@ -1985,12 +2030,14 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
 /*
  * Remove the collected garbage tuples from the table and its indexes.
  *
- * We may be required to skip index vacuuming by INDEX_CLEANUP reloption.
+ * We may be able to skip index vacuuming (we may even be required to do so by
+ * reloption)
  */
 static void
 lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 						 Relation *Irel, int nindexes, LVParallelState *lps,
-						 VacOptTernaryValue index_cleanup)
+						 VacOptTernaryValue index_cleanup,
+						 BlockNumber has_dead_items_pages, bool onecall)
 {
 	bool		skipping;
 
@@ -1998,12 +2045,44 @@ lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 	Assert(nindexes > 0);
 	Assert(!IsParallelWorker());
 
-	/* Check whether or not to do index vacuum and heap vacuum */
+	/* Skip index and heap vacuuming if INDEX_CLEANUP=OFF */
 	if (index_cleanup == VACOPT_TERNARY_DISABLED)
 		skipping = true;
-	else
+
+	/*
+	 * Don't skip index and heap vacuuming if it's not only called once during
+	 * the entire vacuum operation.
+	 */
+	else if (!onecall)
 		skipping = false;
 
+	/*
+	 * We do both index and heap vacuuming if more than SKIP_VACUUM_PAGES_RATIO
+	 * of all heap pages have at least one LP_DEAD line pointer.  This is
+	 * normally a case where dead tuples on the heap are highly concentrated
+	 * in relatively few heap blocks, where the index's enhanced deletion
+	 * mechanism that is clever about heap block dead tuple concentrations
+	 * including btree's bottom-up index deletion works well.  Also, since we
+	 * can clean only a few heap blocks, it would be a less negative impact in
+	 * terms of visibility map update.
+	 */
+	else
+	{
+		BlockNumber rel_pages_threshold;
+
+		Assert(onecall);
+		Assert(vacrelstats->num_index_scans == 0);
+		Assert(index_cleanup == VACOPT_TERNARY_ENABLED);
+
+		rel_pages_threshold =
+			(double) vacrelstats->rel_pages * SKIP_VACUUM_PAGES_RATIO;
+
+		if (has_dead_items_pages < rel_pages_threshold)
+			skipping = true;
+		else
+			skipping = false;
+	}
+
 	if (!skipping)
 	{
 		/* Okay, we're going to do index vacuuming */
@@ -2024,10 +2103,18 @@ lazy_vacuum_pruned_items(Relation onerel, LVRelStats *vacrelstats,
 		 * the similar "nindexes == 0" specific ereport() at the end of
 		 * lazy_scan_heap().
 		 */
-		ereport(elevel,
-				(errmsg("\"%s\": INDEX_CLEANUP off forced VACUUM to not totally remove %d pruned items",
-						vacrelstats->relname,
-						vacrelstats->dead_tuples->num_tuples)));
+		if (index_cleanup == VACOPT_TERNARY_DISABLED)
+			ereport(elevel,
+					(errmsg("\"%s\": INDEX_CLEANUP off forced VACUUM to not totally remove %d pruned items in %u pages",
+							vacrelstats->relname,
+							vacrelstats->dead_tuples->num_tuples,
+							has_dead_items_pages)));
+		else
+			ereport(elevel,
+					(errmsg("\"%s\": opted to not totally remove %d pruned items in %u pages",
+							vacrelstats->relname,
+							vacrelstats->dead_tuples->num_tuples,
+							has_dead_items_pages)));
 	}
 
 	/*
-- 
2.27.0