v1-0001-Leverage-existing-functions-for-relation-stats.patch

From 45467a69813cbf25c2850b254c5d2710c231a723 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Fri, 21 Feb 2025 23:31:04 -0500
Subject: [PATCH v1] Leverage existing functions for relation stats.

Rather than query pg_class once per relation in order to fetch relation
stats (reltuples, relpages, relallvisible), instead just add those
fields to the existing queries in getTables and getIndexes, and then
store their string representations in RelStatsInfo.

Additionally, we will need the string representation of the server
version number so render that one and store in the Archive struct.

No modification has been made for the attribute query, that will be
addressed in a later patch.
---
 src/bin/pg_dump/pg_backup.h    |   1 +
 src/bin/pg_dump/pg_backup_db.c |   1 +
 src/bin/pg_dump/pg_dump.c      | 113 +++++++++++++--------------------
 src/bin/pg_dump/pg_dump.h      |   9 +++
 4 files changed, 54 insertions(+), 70 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 350cf659c41..bbca3419b45 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -224,6 +224,7 @@ typedef struct Archive
 	int			verbose;
 	char	   *remoteVersionStr;	/* server's version string */
 	int			remoteVersion;	/* same in numeric form */
+	char		remoteVersionNumStr[32]; /* server version number, as string */
 	bool		isStandby;		/* is server a standby node */
 
 	int			minRemoteVersion;	/* allowable range */
diff --git a/src/bin/pg_dump/pg_backup_db.c b/src/bin/pg_dump/pg_backup_db.c
index 71c55d2466a..59c7b70d90f 100644
--- a/src/bin/pg_dump/pg_backup_db.c
+++ b/src/bin/pg_dump/pg_backup_db.c
@@ -41,6 +41,7 @@ _check_database_version(ArchiveHandle *AH)
 
 	AH->public.remoteVersionStr = pg_strdup(remoteversion_str);
 	AH->public.remoteVersion = remoteversion;
+	sprintf(AH->public.remoteVersionNumStr, "%d", remoteversion);
 	if (!AH->archiveRemoteVersion)
 		AH->archiveRemoteVersion = AH->public.remoteVersionStr;
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index afd79287177..4311ed5c65d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6814,7 +6814,8 @@ getFuncs(Archive *fout)
  *
  */
 static RelStatsInfo *
-getRelationStatistics(Archive *fout, DumpableObject *rel, char relkind)
+getRelationStatistics(Archive *fout, DumpableObject *rel, const char *relpages,
+					  const char *reltuples, const char *relallvisible, char relkind)
 {
 	if (!fout->dopt->dumpStatistics)
 		return NULL;
@@ -6839,6 +6840,9 @@ getRelationStatistics(Archive *fout, DumpableObject *rel, char relkind)
 		dobj->components |= DUMP_COMPONENT_STATISTICS;
 		dobj->name = pg_strdup(rel->name);
 		dobj->namespace = rel->namespace;
+		info->relpages = relpages;
+		info->reltuples = reltuples;
+		info->relallvisible = relallvisible;
 		info->relkind = relkind;
 		info->postponed_def = false;
 
@@ -6874,6 +6878,8 @@ getTables(Archive *fout, int *numTables)
 	int			i_relhasindex;
 	int			i_relhasrules;
 	int			i_relpages;
+	int			i_reltuples;
+	int			i_relallvisible;
 	int			i_toastpages;
 	int			i_owning_tab;
 	int			i_owning_col;
@@ -6921,6 +6927,7 @@ getTables(Archive *fout, int *numTables)
 	appendPQExpBufferStr(query,
 						 "SELECT c.tableoid, c.oid, c.relname, "
 						 "c.relnamespace, c.relkind, c.reltype, "
+						 "c.relpages, c.reltuples, c.relallvisible, "
 						 "c.relowner, "
 						 "c.relchecks, "
 						 "c.relhasindex, c.relhasrules, c.relpages, "
@@ -7088,6 +7095,8 @@ getTables(Archive *fout, int *numTables)
 	i_relhasindex = PQfnumber(res, "relhasindex");
 	i_relhasrules = PQfnumber(res, "relhasrules");
 	i_relpages = PQfnumber(res, "relpages");
+	i_reltuples = PQfnumber(res, "reltuples");
+	i_relallvisible = PQfnumber(res, "relallvisible");
 	i_toastpages = PQfnumber(res, "toastpages");
 	i_owning_tab = PQfnumber(res, "owning_tab");
 	i_owning_col = PQfnumber(res, "owning_col");
@@ -7151,7 +7160,10 @@ getTables(Archive *fout, int *numTables)
 		tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
 		tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
 		tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
-		tblinfo[i].relpages = atoi(PQgetvalue(res, i, i_relpages));
+		tblinfo[i].relpages_s = pg_strdup(PQgetvalue(res, i, i_relpages));
+		tblinfo[i].relpages = atoi(tblinfo[i].relpages_s);
+		tblinfo[i].reltuples_s = pg_strdup(PQgetvalue(res, i, i_reltuples));
+		tblinfo[i].relallvisible_s = pg_strdup(PQgetvalue(res, i, i_relallvisible));
 		if (PQgetisnull(res, i, i_toastpages))
 			tblinfo[i].toastpages = 0;
 		else
@@ -7233,7 +7245,9 @@ getTables(Archive *fout, int *numTables)
 
 		/* Add statistics */
 		if (tblinfo[i].interesting)
-			getRelationStatistics(fout, &tblinfo[i].dobj, tblinfo[i].relkind);
+			getRelationStatistics(fout, &tblinfo[i].dobj, tblinfo[i].relpages_s,
+								  tblinfo[i].reltuples_s, tblinfo[i].relallvisible_s,
+								  tblinfo[i].relkind);
 
 		/*
 		 * Read-lock target tables to make sure they aren't DROPPED or altered
@@ -7499,6 +7513,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 				i_oid,
 				i_indrelid,
 				i_indexname,
+				i_relpages,
+				i_reltuples,
+				i_relallvisible,
 				i_parentidx,
 				i_indexdef,
 				i_indnkeyatts,
@@ -7552,6 +7569,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 	appendPQExpBufferStr(query,
 						 "SELECT t.tableoid, t.oid, i.indrelid, "
 						 "t.relname AS indexname, "
+						 "t.relpages, t.reltuples, t.relallvisible, "
 						 "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, "
 						 "i.indkey, i.indisclustered, "
 						 "c.contype, c.conname, "
@@ -7659,6 +7677,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 	i_oid = PQfnumber(res, "oid");
 	i_indrelid = PQfnumber(res, "indrelid");
 	i_indexname = PQfnumber(res, "indexname");
+	i_relpages = PQfnumber(res, "relpages");
+	i_reltuples = PQfnumber(res, "reltuples");
+	i_relallvisible = PQfnumber(res, "relallvisible");
 	i_parentidx = PQfnumber(res, "parentidx");
 	i_indexdef = PQfnumber(res, "indexdef");
 	i_indnkeyatts = PQfnumber(res, "indnkeyatts");
@@ -7732,6 +7753,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 			AssignDumpId(&indxinfo[j].dobj);
 			indxinfo[j].dobj.dump = tbinfo->dobj.dump;
 			indxinfo[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_indexname));
+			indxinfo[j].relpages_s = pg_strdup(PQgetvalue(res, j, i_relpages));
+			indxinfo[j].reltuples_s = pg_strdup(PQgetvalue(res, j, i_reltuples));
+			indxinfo[j].relallvisible_s = pg_strdup(PQgetvalue(res, j, i_relallvisible));
 			indxinfo[j].dobj.namespace = tbinfo->dobj.namespace;
 			indxinfo[j].indextable = tbinfo;
 			indxinfo[j].indexdef = pg_strdup(PQgetvalue(res, j, i_indexdef));
@@ -7759,7 +7783,9 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 				indexkind = RELKIND_PARTITIONED_INDEX;
 
 			contype = *(PQgetvalue(res, j, i_contype));
-			relstats = getRelationStatistics(fout, &indxinfo[j].dobj, indexkind);
+			relstats = getRelationStatistics(fout, &indxinfo[j].dobj, indxinfo[j].relpages_s,
+											 indxinfo[j].reltuples_s, indxinfo[j].relallvisible_s,
+									indexkind);
 
 			if (contype == 'p' || contype == 'u' || contype == 'x')
 			{
@@ -10383,18 +10409,6 @@ dumpComment(Archive *fout, const char *type,
 						catalogId, subid, dumpId, NULL);
 }
 
-/*
- * Tabular description of the parameters to pg_restore_relation_stats()
- * param_name, param_type
- */
-static const char *rel_stats_arginfo[][2] = {
-	{"relation", "regclass"},
-	{"version", "integer"},
-	{"relpages", "integer"},
-	{"reltuples", "real"},
-	{"relallvisible", "integer"},
-};
-
 /*
  * Tabular description of the parameters to pg_restore_attribute_stats()
  * param_name, param_type
@@ -10419,30 +10433,6 @@ static const char *att_stats_arginfo[][2] = {
 	{"range_bounds_histogram", "text"},
 };
 
-/*
- * getRelStatsExportQuery --
- *
- * Generate a query that will fetch all relation (e.g. pg_class)
- * stats for a given relation.
- */
-static void
-getRelStatsExportQuery(PQExpBuffer query, Archive *fout,
-					   const char *schemaname, const char *relname)
-{
-	resetPQExpBuffer(query);
-	appendPQExpBufferStr(query,
-						 "SELECT c.oid::regclass AS relation, "
-						 "current_setting('server_version_num') AS version, "
-						 "c.relpages, c.reltuples, c.relallvisible "
-						 "FROM pg_class c "
-						 "JOIN pg_namespace n "
-						 "ON n.oid = c.relnamespace "
-						 "WHERE n.nspname = ");
-	appendStringLiteralAH(query, schemaname, fout);
-	appendPQExpBufferStr(query, " AND c.relname = ");
-	appendStringLiteralAH(query, relname, fout);
-}
-
 /*
  * getAttStatsExportQuery --
  *
@@ -10521,33 +10511,20 @@ appendNamedArgument(PQExpBuffer out, Archive *fout, const char *argname,
  * Append a formatted pg_restore_relation_stats statement.
  */
 static void
-appendRelStatsImport(PQExpBuffer out, Archive *fout, PGresult *res)
+appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo)
 {
-	const char *sep = "";
-
-	if (PQntuples(res) == 0)
-		return;
+	const char *qualname = fmtQualifiedId(rsinfo->dobj.namespace->dobj.name, rsinfo->dobj.name);
 
 	appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_relation_stats(\n");
-
-	for (int argno = 0; argno < lengthof(rel_stats_arginfo); argno++)
-	{
-		const char *argname = rel_stats_arginfo[argno][0];
-		const char *argtype = rel_stats_arginfo[argno][1];
-		int			fieldno = PQfnumber(res, argname);
-
-		if (fieldno < 0)
-			pg_fatal("relation stats export query missing field '%s'",
-					 argname);
-
-		if (PQgetisnull(res, 0, fieldno))
-			continue;
-
-		appendPQExpBufferStr(out, sep);
-		appendNamedArgument(out, fout, argname, PQgetvalue(res, 0, fieldno), argtype);
-
-		sep = ",\n";
-	}
+	appendNamedArgument(out, fout, "relation", qualname, "regclass");
+	appendPQExpBufferStr(out, ",\n");
+	appendNamedArgument(out, fout, "version", fout->remoteVersionNumStr, "integer");
+	appendPQExpBufferStr(out, ",\n");
+	appendNamedArgument(out, fout, "relpages", rsinfo->relpages, "integer");
+	appendPQExpBufferStr(out, ",\n");
+	appendNamedArgument(out, fout, "reltuples", rsinfo->reltuples, "real");
+	appendPQExpBufferStr(out, ",\n");
+	appendNamedArgument(out, fout, "relallvisible", rsinfo->relallvisible, "integer");
 	appendPQExpBufferStr(out, "\n);\n");
 }
 
@@ -10643,15 +10620,11 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 	tag = createPQExpBuffer();
 	appendPQExpBufferStr(tag, fmtId(dobj->name));
 
-	query = createPQExpBuffer();
 	out = createPQExpBuffer();
 
-	getRelStatsExportQuery(query, fout, dobj->namespace->dobj.name,
-						   dobj->name);
-	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
-	appendRelStatsImport(out, fout, res);
-	PQclear(res);
+	appendRelStatsImport(out, fout, rsinfo);
 
+	query = createPQExpBuffer();
 	getAttStatsExportQuery(query, fout, dobj->namespace->dobj.name,
 						   dobj->name);
 	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f08f5905aa3..1fd1812d348 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -329,6 +329,9 @@ typedef struct _tableInfo
 	int			owning_col;		/* attr # of column owning sequence */
 	bool		is_identity_sequence;
 	int			relpages;		/* table's size in pages (from pg_class) */
+	const char *relpages_s;		/* table's size in pages (from pg_class) */
+	const char *reltuples_s;	/* table's approx number of tuples (from pg_class) */
+	const char *relallvisible_s;	/* table's number of pages all-visible (from pg_class) */
 	int			toastpages;		/* toast table's size in pages, if any */
 
 	bool		interesting;	/* true if need to collect more data */
@@ -418,6 +421,9 @@ typedef struct _indxInfo
 	int			indnattrs;		/* total number of index attributes */
 	Oid		   *indkeys;		/* In spite of the name 'indkeys' this field
 								 * contains both key and nonkey attributes */
+	const char *relpages_s;
+	const char *reltuples_s;
+	const char *relallvisible_s;
 	bool		indisclustered;
 	bool		indisreplident;
 	bool		indnullsnotdistinct;
@@ -438,6 +444,9 @@ typedef struct _indexAttachInfo
 typedef struct _relStatsInfo
 {
 	DumpableObject dobj;
+	const char *relpages;
+	const char *reltuples;
+	const char *relallvisible;
 	char		relkind;		/* 'r', 'm', 'i', etc */
 	bool		postponed_def;	/* stats must be postponed into post-data */
 } RelStatsInfo;

base-commit: f8d7f29b3e81db59b95e4b5baaa6943178c89fd8
-- 
2.48.1