v2-0002-Move-attribute-statistics-fetching-to-a-prepared-.patch

From 5a03d2935999cac037daeaac566e7709f1824f5e Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 24 Feb 2025 04:36:16 -0500
Subject: [PATCH v2 2/3] Move attribute statistics fetching to a prepared
 statement.

Simplify the query to pg_stats by removing the joins to namespace and
pg_class, these were only needed because we wanted to get the relation
oid, but that's information that we already have.

Also, create a new prepared statement getAttributeStats for this query,
as it will be run once per relation.

Additionally, pull the server_version_num out of the export query, as we
already have that information. However, because version appeared in the
middle of the arginfo array, it has to move to either before the arginfo
loop, which would mix it in with the grain of the call (attname and
inherited would follow it) or to the last argument pair, which is what
was done.

Make the same move for the version parameter in the
pg_set_relation_stats() calls for consistency.
---
 src/bin/pg_dump/pg_backup.h |   3 +-
 src/bin/pg_dump/pg_dump.c   | 137 +++++++++++++++++-------------------
 2 files changed, 68 insertions(+), 72 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 350cf659c41..b78724671c5 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -74,9 +74,10 @@ enum _dumpPreparedQueries
 	PREPQUERY_DUMPTABLEATTACH,
 	PREPQUERY_GETCOLUMNACLS,
 	PREPQUERY_GETDOMAINCONSTRAINTS,
+	PREPQUERY_ATTRIBUTESTATS,
 };
 
-#define NUM_PREP_QUERIES (PREPQUERY_GETDOMAINCONSTRAINTS + 1)
+#define NUM_PREP_QUERIES (PREPQUERY_ATTRIBUTESTATS + 1)
 
 /* Parameters needed by ConnectDatabase; same for dump and restore */
 typedef struct _connParams
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a5e7aa73671..bbd415d5477 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -10419,10 +10419,8 @@ dumpComment(Archive *fout, const char *type,
  * param_name, param_type
  */
 static const char *att_stats_arginfo[][2] = {
-	{"relation", "regclass"},
 	{"attname", "name"},
 	{"inherited", "boolean"},
-	{"version", "integer"},
 	{"null_frac", "float4"},
 	{"avg_width", "integer"},
 	{"n_distinct", "float4"},
@@ -10438,59 +10436,6 @@ static const char *att_stats_arginfo[][2] = {
 	{"range_bounds_histogram", "text"},
 };
 
-/*
- * getAttStatsExportQuery --
- *
- * Generate a query that will fetch all attribute (e.g. pg_statistic)
- * stats for a given relation.
- */
-static void
-getAttStatsExportQuery(PQExpBuffer query, Archive *fout,
-					   const char *schemaname, const char *relname)
-{
-	resetPQExpBuffer(query);
-	appendPQExpBufferStr(query,
-						 "SELECT c.oid::regclass AS relation, "
-						 "s.attname,"
-						 "s.inherited,"
-						 "current_setting('server_version_num') AS version, "
-						 "s.null_frac,"
-						 "s.avg_width,"
-						 "s.n_distinct,"
-						 "s.most_common_vals,"
-						 "s.most_common_freqs,"
-						 "s.histogram_bounds,"
-						 "s.correlation,"
-						 "s.most_common_elems,"
-						 "s.most_common_elem_freqs,"
-						 "s.elem_count_histogram,");
-
-	if (fout->remoteVersion >= 170000)
-		appendPQExpBufferStr(query,
-							 "s.range_length_histogram,"
-							 "s.range_empty_frac,"
-							 "s.range_bounds_histogram ");
-	else
-		appendPQExpBufferStr(query,
-							 "NULL AS range_length_histogram,"
-							 "NULL AS range_empty_frac,"
-							 "NULL AS range_bounds_histogram ");
-
-	appendPQExpBufferStr(query,
-						 "FROM pg_stats s "
-						 "JOIN pg_namespace n "
-						 "ON n.nspname = s.schemaname "
-						 "JOIN pg_class c "
-						 "ON c.relname = s.tablename "
-						 "AND c.relnamespace = n.oid "
-						 "WHERE s.schemaname = ");
-	appendStringLiteralAH(query, schemaname, fout);
-	appendPQExpBufferStr(query, " AND s.tablename = ");
-	appendStringLiteralAH(query, relname, fout);
-	appendPQExpBufferStr(query, " ORDER BY s.attname, s.inherited");
-}
-
-
 /*
  * appendNamedArgument --
  *
@@ -10516,23 +10461,20 @@ appendNamedArgument(PQExpBuffer out, Archive *fout, const char *argname,
  * Append a formatted pg_restore_relation_stats statement.
  */
 static void
-appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo)
+appendRelStatsImport(PQExpBuffer out, Archive *fout,
+					 const RelStatsInfo *rsinfo, const char *qualname,
+					 const char *version)
 {
-	const char *qualname = fmtQualifiedId(rsinfo->dobj.namespace->dobj.name, rsinfo->dobj.name);
-	char		version[32];
-
-	snprintf(version, sizeof(version), "%d", fout->remoteVersion);
-
 	appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_relation_stats(\n");
 	appendNamedArgument(out, fout, "relation", qualname, "regclass");
 	appendPQExpBufferStr(out, ",\n");
-	appendNamedArgument(out, fout, "version", version, "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");
+	appendNamedArgument(out, fout, "version", version, "integer");
 	appendPQExpBufferStr(out, "\n);\n");
 }
 
@@ -10542,13 +10484,16 @@ appendRelStatsImport(PQExpBuffer out, Archive *fout, const RelStatsInfo *rsinfo)
  * Append a series of formatted pg_restore_attribute_stats statements.
  */
 static void
-appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res)
+appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res,
+					 const char *qualname, const char *version)
 {
+	const char *sep = ",\n";
+
 	for (int rownum = 0; rownum < PQntuples(res); rownum++)
 	{
-		const char *sep = "";
-
 		appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_attribute_stats(\n");
+		appendNamedArgument(out, fout, "relation", qualname, "regclass");
+
 		for (int argno = 0; argno < lengthof(att_stats_arginfo); argno++)
 		{
 			const char *argname = att_stats_arginfo[argno][0];
@@ -10564,8 +10509,9 @@ appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res)
 
 			appendPQExpBufferStr(out, sep);
 			appendNamedArgument(out, fout, argname, PQgetvalue(res, rownum, fieldno), argtype);
-			sep = ",\n";
 		}
+		appendPQExpBufferStr(out, sep);
+		appendNamedArgument(out, fout, "version", version, "integer");
 		appendPQExpBufferStr(out, "\n);\n");
 	}
 }
@@ -10613,6 +10559,8 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 	DumpableObject *dobj = (DumpableObject *) &rsinfo->dobj;
 	DumpId	   *deps = NULL;
 	int			ndeps = 0;
+	const char *qualname;
+	char		version[32];
 
 	/* nothing to do if we are not dumping statistics */
 	if (!fout->dopt->dumpStatistics)
@@ -10625,18 +10573,64 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 		ndeps = dobj->nDeps;
 	}
 
+	qualname = pg_strdup(fmtQualifiedId(rsinfo->dobj.namespace->dobj.name, rsinfo->dobj.name));
+	snprintf(version, sizeof(version), "%d", fout->remoteVersion);
+
 	tag = createPQExpBuffer();
 	appendPQExpBufferStr(tag, fmtId(dobj->name));
 
 	out = createPQExpBuffer();
 
-	appendRelStatsImport(out, fout, rsinfo);
+	appendRelStatsImport(out, fout, rsinfo, qualname, version);
 
 	query = createPQExpBuffer();
-	getAttStatsExportQuery(query, fout, dobj->namespace->dobj.name,
-						   dobj->name);
+	if (!fout->is_prepared[PREPQUERY_ATTRIBUTESTATS])
+	{
+		appendPQExpBufferStr(query,
+							 "PREPARE getAttributeStats(pg_catalog.name, pg_catalog.name) AS\n"
+							"SELECT "
+							"s.attname, "
+							"s.inherited, "
+							"s.null_frac, "
+							"s.avg_width, "
+							"s.n_distinct, "
+							"s.most_common_vals, "
+							"s.most_common_freqs, "
+							"s.histogram_bounds, "
+							"s.correlation, "
+							"s.most_common_elems, "
+							"s.most_common_elem_freqs, "
+							"s.elem_count_histogram, ");
+
+		if (fout->remoteVersion >= 170000)
+			appendPQExpBufferStr(query,
+								"s.range_length_histogram,"
+								"s.range_empty_frac,"
+								"s.range_bounds_histogram ");
+		else
+			appendPQExpBufferStr(query,
+								"NULL AS range_length_histogram,"
+								"NULL AS range_empty_frac,"
+								"NULL AS range_bounds_histogram ");
+
+		appendPQExpBufferStr(query,
+							"FROM pg_stats s "
+							"WHERE s.schemaname = $1 "
+							"AND s.tablename = $2 "
+							"ORDER BY s.attname, s.inherited");
+
+		ExecuteSqlStatement(fout, query->data);
+
+		fout->is_prepared[PREPQUERY_ATTRIBUTESTATS] = true;
+	}
+
+	printfPQExpBuffer(query, "EXECUTE getAttributeStats(");
+	appendStringLiteralAH(query, dobj->namespace->dobj.name, fout);
+	appendPQExpBufferStr(query, ", ");
+	appendStringLiteralAH(query, dobj->name, fout);
+	appendPQExpBufferStr(query, "); ");
 	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
-	appendAttStatsImport(out, fout, res);
+	appendAttStatsImport(out, fout, res, qualname, version);
 	PQclear(res);
 
 	ArchiveEntry(fout, nilCatalogId, createDumpId(),
@@ -10652,6 +10646,7 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
 	destroyPQExpBuffer(query);
 	destroyPQExpBuffer(out);
 	destroyPQExpBuffer(tag);
+	pg_free((void *) qualname);
 }
 
 /*
-- 
2.48.1