0001-Expose-the-index-being-processed-in-the-vacuuming-in.patch

From 4bb70bc6dd3005d07b3c9b74d527e31b446b833f Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)" <simseih@amazon.com>
Date: Thu, 17 Feb 2022 06:27:10 +0000
Subject: [PATCH 1/1] Expose the index being processed in the "vacuuming
 indexes" or "cleaning up indexes" phase of a VACUUM operation.

A new view called pg_stat_progress_vacuum_index to show the indexrelid
being vacuumed or cleaned during a vacuum. The view also shows the
number of tuples removed for the index during the vacuuming indexes phase.

Author: Sami Imseih, based on suggestions by Nathan Bossart, Peter Geoghegan and Masahiko Sawada
Reviewed by: Nathan Bossart, Justin Pryzby
---
 src/backend/access/gin/ginvacuum.c    |  3 ++
 src/backend/access/gist/gistvacuum.c  |  3 ++
 src/backend/access/hash/hash.c        |  1 +
 src/backend/access/heap/vacuumlazy.c  | 15 ++++++++++
 src/backend/access/nbtree/nbtree.c    |  1 +
 src/backend/access/spgist/spgvacuum.c |  4 +++
 src/backend/catalog/system_views.sql  | 41 +++++++++++++++++++++++++++
 src/backend/commands/vacuumparallel.c | 12 ++++++++
 src/backend/utils/adt/pgstatfuncs.c   |  2 ++
 src/include/commands/progress.h       | 17 +++++++----
 src/include/utils/backend_progress.h  |  1 +
 src/test/regress/expected/rules.out   | 36 +++++++++++++++++++++++
 12 files changed, 130 insertions(+), 6 deletions(-)

diff --git a/src/backend/access/gin/ginvacuum.c b/src/backend/access/gin/ginvacuum.c
index b4fa5f6bf8..1d5d003780 100644
--- a/src/backend/access/gin/ginvacuum.c
+++ b/src/backend/access/gin/ginvacuum.c
@@ -17,8 +17,10 @@
 #include "access/gin_private.h"
 #include "access/ginxlog.h"
 #include "access/xloginsert.h"
+#include "commands/progress.h"
 #include "commands/vacuum.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "postmaster/autovacuum.h"
 #include "storage/indexfsm.h"
 #include "storage/lmgr.h"
@@ -60,6 +62,7 @@ ginVacuumItemPointers(GinVacuumState *gvs, ItemPointerData *items,
 		if (gvs->callback(items + i, gvs->callback_state))
 		{
 			gvs->result->tuples_removed += 1;
+			pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, gvs->result->tuples_removed);
 			if (!tmpitems)
 			{
 				/*
diff --git a/src/backend/access/gist/gistvacuum.c b/src/backend/access/gist/gistvacuum.c
index aac4afab8f..8a0f23388b 100644
--- a/src/backend/access/gist/gistvacuum.c
+++ b/src/backend/access/gist/gistvacuum.c
@@ -17,9 +17,11 @@
 #include "access/genam.h"
 #include "access/gist_private.h"
 #include "access/transam.h"
+#include "commands/progress.h"
 #include "commands/vacuum.h"
 #include "lib/integerset.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "storage/indexfsm.h"
 #include "storage/lmgr.h"
 #include "utils/memutils.h"
@@ -375,6 +377,7 @@ restart:
 			END_CRIT_SECTION();
 
 			vstate->stats->tuples_removed += ntodelete;
+			pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, vstate->stats->tuples_removed);
 			/* must recompute maxoff */
 			maxoff = PageGetMaxOffsetNumber(page);
 		}
diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c
index a259a301fa..23dacee52e 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -632,6 +632,7 @@ loop_top:
 	stats->estimated_count = false;
 	stats->num_index_tuples = num_index_tuples;
 	stats->tuples_removed += tuples_removed;
+	pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, stats->tuples_removed);
 	/* hashvacuumcleanup will fill in num_pages */
 
 	return stats;
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 514582972d..57dc9bc63b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -370,6 +370,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 
 	pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
 								  RelationGetRelid(rel));
+	pgstat_progress_update_param(PROGRESS_VACUUM_LEADER_PID, MyProcPid);
 
 	/*
 	 * Get OldestXmin cutoff, which is used to determine which deleted tuples
@@ -2347,6 +2348,9 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
 			Relation	indrel = vacrel->indrels[idx];
 			IndexBulkDeleteResult *istat = vacrel->indstats[idx];
 
+			/* Advertise the index being vacuumed in non-parallel vacuum */
+			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, RelationGetRelid(indrel));
+
 			vacrel->indstats[idx] =
 				lazy_vacuum_one_index(indrel, istat, vacrel->old_live_tuples,
 									  vacrel);
@@ -2358,6 +2362,10 @@ lazy_vacuum_all_indexes(LVRelState *vacrel)
 			 */
 			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
 
+			/* Advertise we are done vacuuming indexes in non-parallel vacuum */
+			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, 0);
+			pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, 0);
+
 			if (lazy_check_wraparound_failsafe(vacrel))
 			{
 				/* Wraparound emergency -- end current index scan */
@@ -2719,12 +2727,19 @@ lazy_cleanup_all_indexes(LVRelState *vacrel)
 			Relation	indrel = vacrel->indrels[idx];
 			IndexBulkDeleteResult *istat = vacrel->indstats[idx];
 
+			/* Advertise the index being cleaned in non-parallel vacuum*/
+			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, RelationGetRelid(indrel));
+
 			vacrel->indstats[idx] =
 				lazy_cleanup_one_index(indrel, istat, reltuples,
 									   estimated_count, vacrel);
 
 			/* See the lazy_vacuum_all_indexes comments */
 			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXES_COMPLETED, idx + 1);
+
+			/* Advertise we are done cleaning indexes in non-parallel vacuum */
+			pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, 0);
+			pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, 0);
 		}
 	}
 	else
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index c9b4964c1e..09edf49082 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -1273,6 +1273,7 @@ backtrack:
 								nupdatable);
 
 			stats->tuples_removed += nhtidsdead;
+			pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, stats->tuples_removed);
 			/* must recompute maxoff */
 			maxoff = PageGetMaxOffsetNumber(page);
 
diff --git a/src/backend/access/spgist/spgvacuum.c b/src/backend/access/spgist/spgvacuum.c
index 0049630532..db73f8ef59 100644
--- a/src/backend/access/spgist/spgvacuum.c
+++ b/src/backend/access/spgist/spgvacuum.c
@@ -21,8 +21,10 @@
 #include "access/transam.h"
 #include "access/xloginsert.h"
 #include "catalog/storage_xlog.h"
+#include "commands/progress.h"
 #include "commands/vacuum.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "storage/bufmgr.h"
 #include "storage/indexfsm.h"
 #include "storage/lmgr.h"
@@ -160,6 +162,7 @@ vacuumLeafPage(spgBulkDeleteState *bds, Relation index, Buffer buffer,
 				bds->stats->tuples_removed += 1;
 				deletable[i] = true;
 				nDeletable++;
+				pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, bds->stats->tuples_removed);
 			}
 			else
 			{
@@ -430,6 +433,7 @@ vacuumLeafRoot(spgBulkDeleteState *bds, Relation index, Buffer buffer)
 				bds->stats->tuples_removed += 1;
 				toDelete[xlrec.nDelete] = i;
 				xlrec.nDelete++;
+				pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, bds->stats->tuples_removed);
 			}
 			else
 			{
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index eaa0508c0b..5bd6953996 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1123,6 +1123,8 @@ CREATE VIEW pg_stat_progress_vacuum AS
                       WHEN 4 THEN 'cleaning up indexes'
                       WHEN 5 THEN 'truncating heap'
                       WHEN 6 THEN 'performing final cleanup'
+                      WHEN 7 THEN 'vacuuming indexes'
+                      WHEN 8 THEN 'cleaning up indexes'
                       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,
@@ -1131,6 +1133,45 @@ CREATE VIEW pg_stat_progress_vacuum AS
     FROM pg_stat_get_progress_info('VACUUM') AS S
         LEFT JOIN pg_database D ON S.datid = D.oid;
 
+CREATE VIEW pg_stat_progress_vacuum_index AS
+    SELECT
+        S.pid,
+        S.datid,
+        S.datname,
+        S.indexrelid,
+        S.leader_pid,
+        CASE S.phase WHEN 2 THEN 'vacuuming indexes'
+					 WHEN 4 THEN 'cleaning up indexes'
+					 WHEN 7 THEN 'vacuuming indexes'
+                     WHEN 8 THEN 'cleaning up indexes'
+                     END AS phase,
+        S.tuples_removed
+    FROM (
+        SELECT
+            S.pid AS pid,
+            S.datid AS datid,
+            D.datname AS datname,
+            S.param10 AS indexrelid,
+            S.param12 AS leader_pid,
+            S.param1 AS phase,
+            S.param11 AS tuples_removed
+        FROM pg_stat_get_progress_info('VACUUM') AS S
+            LEFT JOIN pg_database D ON S.datid = D.oid
+        WHERE S.param1 IN (2, 4, 7, 8) AND S.param10 > 0
+        UNION ALL
+        SELECT
+            S.pid AS pid,
+            S.datid AS datid,
+            D.datname AS datname,
+            S.param10 AS indexrelid,
+            S.param12 AS leader_pid,
+            S.param1 AS phase,
+            S.param11 AS tuples_removed
+        FROM pg_stat_get_progress_info('VACUUM_PARALLEL') AS S
+            LEFT JOIN pg_database D ON S.datid = D.oid
+        ) AS S
+    WHERE S.phase IN (2, 4, 7, 8) AND S.indexrelid > 0;
+
 CREATE VIEW pg_stat_progress_cluster AS
     SELECT
         S.pid AS pid,
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 9b465e12cc..0c6eac8bb9 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -845,14 +845,19 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
 	pvs->indname = pstrdup(RelationGetRelationName(indrel));
 	pvs->status = indstats->status;
 
+	/* Advertise the index we are cleaning or vacuuming */
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, RelationGetRelid(indrel));
+
 	switch (indstats->status)
 	{
 		case PARALLEL_INDVAC_STATUS_NEED_BULKDELETE:
 			istat_res = vac_bulkdel_one_index(&ivinfo, istat, pvs->dead_items);
+			pgstat_progress_update_param(PROGRESS_VACUUM_PHASE, PROGRESS_VACUUM_PHASE_VACUUM_INDEX_PARALLEL);
 			vacuum_worker_update(pvs->shared->leader_pid);
 			break;
 		case PARALLEL_INDVAC_STATUS_NEED_CLEANUP:
 			istat_res = vac_cleanup_one_index(&ivinfo, istat);
+			pgstat_progress_update_param(PROGRESS_VACUUM_PHASE, PROGRESS_VACUUM_PHASE_INDEX_CLEANUP_PARALLEL);
 			vacuum_worker_update(pvs->shared->leader_pid);
 			break;
 		default:
@@ -888,6 +893,10 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
 	 */
 	indstats->status = PARALLEL_INDVAC_STATUS_COMPLETED;
 
+	/* Advertise we are no longer vacuuming/cleaning an index */
+	pgstat_progress_update_param(PROGRESS_VACUUM_INDEXRELID, 0);
+	pgstat_progress_update_param(PROGRESS_VACUUM_TUPLES_REMOVED, 0);
+
 	/* Reset error traceback information */
 	pvs->status = PARALLEL_INDVAC_STATUS_COMPLETED;
 	pfree(pvs->indname);
@@ -972,6 +981,8 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
 	 * workers.
 	 */
 	rel = table_open(shared->relid, ShareUpdateExclusiveLock);
+	pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM_PARALLEL, RelationGetRelid(rel));
+	pgstat_progress_update_param(PROGRESS_VACUUM_LEADER_PID, shared->leader_pid);
 
 	/*
 	 * Open all indexes. indrels are sorted in order by OID, which should be
@@ -1041,6 +1052,7 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
 	error_context_stack = errcallback.previous;
 
 	vac_close_indexes(nindexes, indrels, RowExclusiveLock);
+	pgstat_progress_end_command();
 	table_close(rel, ShareUpdateExclusiveLock);
 	FreeAccessStrategy(pvs.bstrategy);
 }
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index e9b41da6b9..4813fae40e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -493,6 +493,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 		cmdtype = PROGRESS_COMMAND_VACUUM;
 		callback = vacuum_progress_cb;
 	}
+	else if (pg_strcasecmp(cmd, "VACUUM_PARALLEL") == 0)
+		cmdtype = PROGRESS_COMMAND_VACUUM_PARALLEL;
 	else if (pg_strcasecmp(cmd, "ANALYZE") == 0)
 		cmdtype = PROGRESS_COMMAND_ANALYZE;
 	else if (pg_strcasecmp(cmd, "CLUSTER") == 0)
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index e4f3cd9133..c20466cc5f 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -27,14 +27,19 @@
 #define PROGRESS_VACUUM_NUM_DEAD_TUPLES			6
 #define PROGRESS_VACUUM_TOTAL_INDEXES			7
 #define PROGRESS_VACUUM_INDEXES_COMPLETED		8
+#define PROGRESS_VACUUM_INDEXRELID				9
+#define PROGRESS_VACUUM_TUPLES_REMOVED			10
+#define PROGRESS_VACUUM_LEADER_PID				11
 
 /* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
-#define PROGRESS_VACUUM_PHASE_SCAN_HEAP			1
-#define PROGRESS_VACUUM_PHASE_VACUUM_INDEX		2
-#define PROGRESS_VACUUM_PHASE_VACUUM_HEAP		3
-#define PROGRESS_VACUUM_PHASE_INDEX_CLEANUP		4
-#define PROGRESS_VACUUM_PHASE_TRUNCATE			5
-#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP		6
+#define PROGRESS_VACUUM_PHASE_SCAN_HEAP					1
+#define PROGRESS_VACUUM_PHASE_VACUUM_INDEX				2
+#define PROGRESS_VACUUM_PHASE_VACUUM_HEAP				3
+#define PROGRESS_VACUUM_PHASE_INDEX_CLEANUP				4
+#define PROGRESS_VACUUM_PHASE_TRUNCATE					5
+#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP				6
+#define PROGRESS_VACUUM_PHASE_VACUUM_INDEX_PARALLEL		7
+#define PROGRESS_VACUUM_PHASE_INDEX_CLEANUP_PARALLEL	8
 
 /* Progress parameters for analyze */
 #define PROGRESS_ANALYZE_PHASE						0
diff --git a/src/include/utils/backend_progress.h b/src/include/utils/backend_progress.h
index 47bf8029b0..4651e45c40 100644
--- a/src/include/utils/backend_progress.h
+++ b/src/include/utils/backend_progress.h
@@ -23,6 +23,7 @@ typedef enum ProgressCommandType
 {
 	PROGRESS_COMMAND_INVALID,
 	PROGRESS_COMMAND_VACUUM,
+	PROGRESS_COMMAND_VACUUM_PARALLEL,
 	PROGRESS_COMMAND_ANALYZE,
 	PROGRESS_COMMAND_CLUSTER,
 	PROGRESS_COMMAND_CREATE_INDEX,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index dc27b8614e..c230c6a441 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1995,6 +1995,8 @@ pg_stat_progress_vacuum| SELECT s.pid,
             WHEN 4 THEN 'cleaning up indexes'::text
             WHEN 5 THEN 'truncating heap'::text
             WHEN 6 THEN 'performing final cleanup'::text
+            WHEN 7 THEN 'vacuuming indexes'::text
+            WHEN 8 THEN 'cleaning up indexes'::text
             ELSE NULL::text
         END AS phase,
     s.param2 AS heap_blks_total,
@@ -2007,6 +2009,40 @@ pg_stat_progress_vacuum| SELECT s.pid,
     s.param9 AS indexes_processed
    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_progress_vacuum_index| SELECT s.pid,
+    s.datid,
+    s.datname,
+    s.indexrelid,
+    s.leader_pid,
+        CASE s.phase
+            WHEN 2 THEN 'vacuuming indexes'::text
+            WHEN 4 THEN 'cleaning up indexes'::text
+            WHEN 7 THEN 'vacuuming indexes'::text
+            WHEN 8 THEN 'cleaning up indexes'::text
+            ELSE NULL::text
+        END AS phase,
+    s.tuples_removed
+   FROM ( SELECT s_1.pid,
+            s_1.datid,
+            d.datname,
+            s_1.param10 AS indexrelid,
+            s_1.param12 AS leader_pid,
+            s_1.param1 AS phase,
+            s_1.param11 AS tuples_removed
+           FROM (pg_stat_get_progress_info('VACUUM'::text) s_1(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_1.datid = d.oid)))
+          WHERE ((s_1.param1 = ANY (ARRAY[(2)::bigint, (4)::bigint, (7)::bigint, (8)::bigint])) AND (s_1.param10 > 0))
+        UNION ALL
+         SELECT s_1.pid,
+            s_1.datid,
+            d.datname,
+            s_1.param10 AS indexrelid,
+            s_1.param12 AS leader_pid,
+            s_1.param1 AS phase,
+            s_1.param11 AS tuples_removed
+           FROM (pg_stat_get_progress_info('VACUUM_PARALLEL'::text) s_1(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_1.datid = d.oid)))) s
+  WHERE ((s.phase = ANY (ARRAY[(2)::bigint, (4)::bigint, (7)::bigint, (8)::bigint])) AND (s.indexrelid > 0));
 pg_stat_replication| SELECT s.pid,
     s.usesysid,
     u.rolname AS usename,
-- 
2.32.0