v10-0002-Show-progress-for-index-vacuums.patch

From e5f70a1853c8be8b02144420f84763ccfcee9b13 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)" <simseih@amazon.com>
Date: Thu, 14 Apr 2022 00:47:42 +0000
Subject: [PATCH 2/2] Show progress for index vacuums

Add 2 new columns to pg_stat_progress_vacuum. The columns are
indexes_total as the total indexes to be vacuumed or cleaned and
indexes_processed as the number of indexes vacuumed or cleaned up so
far.

Author: Sami Imseih, based on suggestions by Nathan Bossart, Peter Geoghegan and Masahiko Sawada
Discussion: https://www.postgresql.org/message-id/flat/5478DFCD-2333-401A-B2F0-0D186AB09228@amazon.com
---
 doc/src/sgml/monitoring.sgml          | 25 ++++++++++++++++
 src/backend/access/heap/vacuumlazy.c  | 19 ++++++++++++
 src/backend/catalog/system_views.sql  |  3 +-
 src/backend/commands/vacuumparallel.c | 42 +++++++++++++++++++++++++++
 src/include/commands/progress.h       |  2 ++
 src/test/regress/expected/rules.out   |  4 ++-
 6 files changed, 93 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 180798a6b9..c3e60e57d9 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6396,6 +6396,31 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
        Number of dead tuples collected since the last index vacuum cycle.
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>indexes_total</structfield> <type>bigint</type>
+      </para>
+      <para>
+       The number of indexes to be processed in the 
+       <literal>vacuuming indexes</literal> 
+       or <literal>cleaning up indexes</literal> phase. It is set to
+       <literal>0</literal> when there are no indexes to process 
+       or when failsafe is triggered during the vacuum.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>indexes_completed</structfield> <type>bigint</type>
+      </para>
+      <para>
+       The number of indexes already processed in the
+       <literal>vacuuming indexes</literal>
+       or <literal>cleaning up indexes</literal> phase. It is set to
+       <literal>0</literal> when vacuum is not in any of these phases.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 788db569b2..587aa668f9 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -415,6 +415,9 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	vacrel->rel = rel;
 	vac_open_indexes(vacrel->rel, RowExclusiveLock, &vacrel->nindexes,
 					 &vacrel->indrels);
+	/* Report the number of indexes to vacuum/cleanup */
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_TOTAL, vacrel->nindexes);
+
 	if (instrument && vacrel->nindexes > 0)
 	{
 		/* Copy index names used by instrumentation (not error reporting) */
@@ -2327,6 +2330,8 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
 			vacrel->indstats[idx] =
 				lazy_vacuum_one_index(indrel, istat, vacrel->old_live_tuples,
 									  vacrel);
+			/* Report the number of indexes vacuumed */
+			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
 
 			if (lazy_check_wraparound_failsafe(vacrel))
 			{
@@ -2361,6 +2366,10 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
 		   vacrel->dead_items->num_items == vacrel->lpdead_items);
 	Assert(allindexes || vacrel->failsafe_active);
 
+	/* Report that we're done vacuuming indexes */
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_TOTAL, 0);
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, 0);
+
 	/*
 	 * Increase and report the number of index scans.
 	 *
@@ -2625,6 +2634,10 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
 		vacrel->do_index_cleanup = false;
 		vacrel->do_rel_truncate = false;
 
+		/* Report that we're no longer vacuuming indexes */
+		pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_TOTAL, 0);
+		pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, 0);
+
 		ereport(WARNING,
 				(errmsg("bypassing nonessential maintenance of table \"%s.%s.%s\" as a failsafe after %d index scans",
 						get_database_name(MyDatabaseId),
@@ -2671,6 +2684,8 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
 			vacrel->indstats[idx] =
 				lazy_cleanup_one_index(indrel, istat, reltuples,
 									   estimated_count, vacrel);
+			/* Report the number of indexes cleaned */
+			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
 		}
 	}
 	else
@@ -2680,6 +2695,10 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
 											vacrel->num_index_scans,
 											estimated_count);
 	}
+
+	/* Report that we're done cleaning indexes */
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_TOTAL, 0);
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, 0);
 }
 
 /*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0fc614e32c..bbd2d6352b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1155,7 +1155,8 @@ CREATE VIEW pg_stat_progress_vacuum AS
                       END AS phase,
         S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,
         S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,
-        S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples
+        S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples,
+        S.param8 AS indexes_total, S.param9 AS indexes_completed
     FROM pg_stat_get_progress_info('VACUUM') AS S
         LEFT JOIN pg_database D ON S.datid = D.oid;
 
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index bbf3b69c57..238c49d7d1 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -30,6 +30,7 @@
 #include "access/table.h"
 #include "access/xact.h"
 #include "catalog/index.h"
+#include "commands/progress.h"
 #include "commands/vacuum.h"
 #include "optimizer/paths.h"
 #include "pgstat.h"
@@ -213,6 +214,7 @@ static void parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation
 static bool parallel_vacuum_index_is_parallel_safe(Relation indrel, int num_index_scans,
 												   bool vacuum);
 static void parallel_vacuum_error_callback(void *arg);
+static void parallel_vacuum_progress_callback(void *arg);
 
 /*
  * Try to enter parallel mode and create a parallel context.  Then initialize
@@ -288,6 +290,10 @@ parallel_vacuum_init(Relation rel, Relation *indrels, int nindexes,
 	shm_toc_estimate_chunk(&pcxt->estimator, est_dead_items_len);
 	shm_toc_estimate_keys(&pcxt->estimator, 1);
 
+	/* Setup the Parallel Progress Callback */
+	pvs->pcxt->parallel_progress_callback = parallel_vacuum_progress_callback;
+	pvs->pcxt->parallel_progress_callback_arg = pvs;
+
 	/*
 	 * Estimate space for BufferUsage and WalUsage --
 	 * PARALLEL_VACUUM_KEY_BUFFER_USAGE and PARALLEL_VACUUM_KEY_WAL_USAGE.
@@ -856,6 +862,18 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
 				 RelationGetRelationName(indrel));
 	}
 
+	/*
+	 * If we are the leader, update index vacuum progress.
+	 *
+	 * For convenience, rely on the parallel_progress_callback
+	 * which calculates and reports the number of indexes
+	 * vacuumed/cleaned up in parallel vacuum mode.
+	 * */
+	if (!IsParallelWorker())
+	{
+		pvs->pcxt->parallel_progress_callback(pvs->pcxt->parallel_progress_callback_arg);
+	}
+
 	/*
 	 * Copy the index bulk-deletion result returned from ambulkdelete and
 	 * amvacuumcleanup to the DSM segment if it's the first cycle because they
@@ -1071,3 +1089,27 @@ parallel_vacuum_error_callback(void *arg)
 			return;
 	}
 }
+
+/*
+ * Callback to report index vacuum progress.
+ * Vacuum Progress should only be reported
+ * to the leader process.
+ */
+static void
+parallel_vacuum_progress_callback(void *arg)
+{
+	ParallelVacuumState *pvs = (ParallelVacuumState *)arg;
+	int indexes_completed = 0;
+
+	Assert(!IsParallelWorker());
+
+	for (int i = 0; i < pvs->nindexes; i++)
+	{
+		PVIndStats *indstats = &(pvs->indstats[i]);
+
+		if (indstats->status == PARALLEL_INDVAC_STATUS_COMPLETED)
+			indexes_completed++;
+	}
+
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, indexes_completed);
+}
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index a28938caf4..0d1724ee75 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -25,6 +25,8 @@
 #define PROGRESS_VACUUM_NUM_INDEX_VACUUMS		4
 #define PROGRESS_VACUUM_MAX_DEAD_TUPLES			5
 #define PROGRESS_VACUUM_NUM_DEAD_TUPLES			6
+#define PROGRESS_VACUUM_INDEXES_TOTAL			7
+#define PROGRESS_VACUUM_INDEXES_COMPLETED		8
 
 /* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
 #define PROGRESS_VACUUM_PHASE_SCAN_HEAP			1
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 21effe8315..53ff7151c8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2008,7 +2008,9 @@ pg_stat_progress_vacuum| SELECT s.pid,
     s.param4 AS heap_blks_vacuumed,
     s.param5 AS index_vacuum_count,
     s.param6 AS max_dead_tuples,
-    s.param7 AS num_dead_tuples
+    s.param7 AS num_dead_tuples,
+    s.param8 AS indexes_total,
+    s.param9 AS indexes_completed
    FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
      LEFT JOIN pg_database d ON ((s.datid = d.oid)));
 pg_stat_recovery_prefetch| SELECT s.stats_reset,
-- 
2.32.0