v1-0002-Improve-log_autovacuum_min_duration-output.patch

From 9bd19c1e324c0a796091dce988831b1165f815e8 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Sun, 21 Nov 2021 14:47:11 -0800
Subject: [PATCH v1 2/2] Improve log_autovacuum_min_duration output.

Report on visibility map pages skipped by VACUUM, without regard for
whether the pages were all-frozen or just all-visible.

Also report when and how relfrozenxid is advanced by VACUUM, including
non-aggressive VACUUM.  Apart from being useful on its own, this might
enable future work that teaches non-aggressive VACUUM to be more
concerned about advancing relfrozenxid sooner rather than later.
---
 src/include/commands/vacuum.h        |  2 ++
 src/backend/access/heap/vacuumlazy.c | 41 ++++++++++++++++++++++------
 src/backend/commands/analyze.c       |  3 ++
 src/backend/commands/vacuum.c        |  9 ++++++
 4 files changed, 47 insertions(+), 8 deletions(-)

diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 4cfd52eaf..bc625463e 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -263,6 +263,8 @@ extern void vac_update_relstats(Relation relation,
 								bool hasindex,
 								TransactionId frozenxid,
 								MultiXactId minmulti,
+								bool *frozenxid_updated,
+								bool *minmulti_updated,
 								bool in_outer_xact);
 extern void vacuum_set_xid_limits(Relation rel,
 								  int freeze_min_age, int freeze_table_age,
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 809f59c73..2d23f35a4 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -498,6 +498,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	double		read_rate,
 				write_rate;
 	bool		aggressive;
+	bool		frozenxid_updated,
+				minmulti_updated;
 	BlockNumber orig_rel_pages;
 	char	  **indnames = NULL;
 	TransactionId xidFullScanLimit;
@@ -703,9 +705,11 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 	{
 		/* Cannot advance relfrozenxid/relminmxid -- just update pg_class */
 		Assert(!aggressive);
+		frozenxid_updated = minmulti_updated = false;
 		vac_update_relstats(rel, new_rel_pages, new_live_tuples,
 							new_rel_allvisible, vacrel->nindexes > 0,
-							InvalidTransactionId, InvalidMultiXactId, false);
+							InvalidTransactionId, InvalidMultiXactId,
+							NULL, NULL, false);
 	}
 	else
 	{
@@ -714,7 +718,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 			   orig_rel_pages);
 		vac_update_relstats(rel, new_rel_pages, new_live_tuples,
 							new_rel_allvisible, vacrel->nindexes > 0,
-							FreezeLimit, MultiXactCutoff, false);
+							FreezeLimit, MultiXactCutoff,
+							&frozenxid_updated, &minmulti_updated, false);
 	}
 
 	/*
@@ -744,6 +749,7 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 		{
 			StringInfoData buf;
 			char	   *msgfmt;
+			int32		   diff;
 
 			TimestampDifference(starttime, endtime, &secs, &usecs);
 
@@ -790,16 +796,35 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 							 vacrel->relnamespace,
 							 vacrel->relname,
 							 vacrel->num_index_scans);
-			appendStringInfo(&buf, _("pages: %u removed, %u remain, %u skipped frozen\n"),
+			appendStringInfo(&buf, _("pages: %u removed, %u remain, %u skipped using visibility map (%.2f%% of total)\n"),
 							 vacrel->pages_removed,
 							 vacrel->rel_pages,
-							 vacrel->frozenskipped_pages);
+							 orig_rel_pages - vacrel->scanned_pages,
+							 orig_rel_pages > 0 ?
+							 100.0 * (orig_rel_pages - vacrel->scanned_pages) / orig_rel_pages : 0);
 			appendStringInfo(&buf,
-							 _("tuples: %lld removed, %lld remain, %lld are dead but not yet removable, oldest xmin: %u\n"),
+							 _("tuples: %lld removed, %lld remain, %lld are dead but not yet removable\n"),
 							 (long long) vacrel->tuples_deleted,
 							 (long long) vacrel->new_rel_tuples,
-							 (long long) vacrel->new_dead_tuples,
-							 OldestXmin);
+							 (long long) vacrel->new_dead_tuples);
+			diff = (int32) (ReadNextTransactionId() - OldestXmin);
+			appendStringInfo(&buf,
+							 _("removal cutoff: oldest xmin was %u, which is now %d xact IDs behind\n"),
+							 OldestXmin, diff);
+			if (frozenxid_updated)
+			{
+				diff = (int32) (FreezeLimit - vacrel->relfrozenxid);
+				appendStringInfo(&buf,
+								 _("relfrozenxid: advanced by %d xact IDs, new value: %u\n"),
+								 diff, FreezeLimit);
+			}
+			if (minmulti_updated)
+			{
+				diff = (int32) (MultiXactCutoff - vacrel->relminmxid);
+				appendStringInfo(&buf,
+								 _("relminmxid: advanced by %d multixact IDs, new value: %u\n"),
+								 diff, MultiXactCutoff);
+			}
 			if (orig_rel_pages > 0)
 			{
 				if (vacrel->do_index_vacuuming)
@@ -4011,7 +4036,7 @@ update_index_statistics(LVRelState *vacrel)
 							false,
 							InvalidTransactionId,
 							InvalidMultiXactId,
-							false);
+							NULL, NULL, false);
 	}
 }
 
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 4928702ae..719bf556a 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -650,6 +650,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
 							hasindex,
 							InvalidTransactionId,
 							InvalidMultiXactId,
+							NULL, NULL,
 							in_outer_xact);
 
 		/* Same for indexes */
@@ -666,6 +667,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
 								false,
 								InvalidTransactionId,
 								InvalidMultiXactId,
+								NULL, NULL,
 								in_outer_xact);
 		}
 	}
@@ -678,6 +680,7 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
 		vac_update_relstats(onerel, -1, totalrows,
 							0, hasindex, InvalidTransactionId,
 							InvalidMultiXactId,
+							NULL, NULL,
 							in_outer_xact);
 	}
 
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 5c4bc15b4..8bd4bd12c 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1308,6 +1308,7 @@ vac_update_relstats(Relation relation,
 					BlockNumber num_all_visible_pages,
 					bool hasindex, TransactionId frozenxid,
 					MultiXactId minmulti,
+					bool *frozenxid_updated, bool *minmulti_updated,
 					bool in_outer_xact)
 {
 	Oid			relid = RelationGetRelid(relation);
@@ -1383,22 +1384,30 @@ vac_update_relstats(Relation relation,
 	 * This should match vac_update_datfrozenxid() concerning what we consider
 	 * to be "in the future".
 	 */
+	if (frozenxid_updated)
+		*frozenxid_updated = false;
 	if (TransactionIdIsNormal(frozenxid) &&
 		pgcform->relfrozenxid != frozenxid &&
 		(TransactionIdPrecedes(pgcform->relfrozenxid, frozenxid) ||
 		 TransactionIdPrecedes(ReadNextTransactionId(),
 							   pgcform->relfrozenxid)))
 	{
+		if (frozenxid_updated)
+			*frozenxid_updated = true;
 		pgcform->relfrozenxid = frozenxid;
 		dirty = true;
 	}
 
 	/* Similarly for relminmxid */
+	if (minmulti_updated)
+		*minmulti_updated = false;
 	if (MultiXactIdIsValid(minmulti) &&
 		pgcform->relminmxid != minmulti &&
 		(MultiXactIdPrecedes(pgcform->relminmxid, minmulti) ||
 		 MultiXactIdPrecedes(ReadNextMultiXactId(), pgcform->relminmxid)))
 	{
+		if (minmulti_updated)
+			*minmulti_updated = true;
 		pgcform->relminmxid = minmulti;
 		dirty = true;
 	}
-- 
2.30.2