v13-0003-Add-remote_analyze-to-postgres_fdw-remote-statis.patch

From c25cb9fb4c5dff0711de4827670e801783c2a124 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Sat, 17 Jan 2026 19:33:53 -0500
Subject: [PATCH v13 3/3] Add remote_analyze to postgres_fdw remote statistics
 fetching.

This is accomplished through a new option, remote_analyze, which is
available at the server level and table level. The default value is
false.

If remote_analyze is enabled, and if the first attempt to fetch remote
statistics did not fetch attribute statistics for every local table
column, then an attempt will be made to ANALYZE the remote table. If
that remote ANALYZE succeeds, then a second and final attempt will be
made to fetch remote statistics. If the statistics found are still
insufficient, then the local ANALYZE command will fall back to regular
row sampling and computing the statistics locally.
---
 doc/src/sgml/postgres-fdw.sgml                | 16 ++++
 .../postgres_fdw/expected/postgres_fdw.out    | 38 +++++++++
 contrib/postgres_fdw/option.c                 |  5 ++
 contrib/postgres_fdw/postgres_fdw.c           | 80 ++++++++++++++++++-
 contrib/postgres_fdw/sql/postgres_fdw.sql     | 34 ++++++++
 5 files changed, 171 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index f695415575d..97754053bac 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -387,6 +387,22 @@ OPTIONS (ADD password_required 'false');
      </listitem>
     </varlistentry>
 
+    <varlistentry>
+     <term><literal>remote_analyze</literal> (<type>boolean</type>)</term>
+     <listitem>
+      <para>
+       This option, which can be specified for a foreign table or a foreign
+       server, determines whether an <command>ANALYZE</command> on a foreign
+       table will attempt to <command>ANALYZE</command> the remote table if
+       the first attempt to fetch remote statistics fails, and will then
+       make a second and final attempt to fetch remote statistics. This option
+       has no meaning if the foreign table has <literal>fetch_stats</literal>
+       disabled.
+       The default is <literal>false</literal>.
+      </para>
+     </listitem>
+    </varlistentry>
+
    </variablelist>
 
   </sect3>
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 9ca3a14fa74..3c704c11d73 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12691,6 +12691,44 @@ ANALYZE analyze_ftable;
 DROP FOREIGN TABLE analyze_ftable;
 DROP TABLE analyze_table;
 -- ===================================================================
+-- test remote analyze
+-- ===================================================================
+CREATE TABLE remote_analyze_table (id int, a text, b bigint);
+INSERT INTO remote_analyze_table (SELECT x FROM generate_series(1,1000) x);
+CREATE FOREIGN TABLE remote_analyze_ftable (id int, a text, b bigint)
+       SERVER loopback
+       OPTIONS (table_name 'remote_analyze_table',
+                fetch_stats 'true',
+                remote_analyze 'true');
+-- no stats before
+SELECT s.tablename, COUNT(*) AS num_stats
+FROM pg_stats AS s
+WHERE s.schemaname = 'public'
+AND s.tablename IN ('remote_analyze_table', 'remote_analyze_ftable')
+GROUP BY s.tablename
+ORDER BY s.tablename;
+ tablename | num_stats 
+-----------+-----------
+(0 rows)
+
+ANALYZE remote_analyze_ftable;
+-- both stats after
+SELECT s.tablename, COUNT(*) AS num_stats
+FROM pg_stats AS s
+WHERE s.schemaname = 'public'
+AND s.tablename IN ('remote_analyze_table', 'remote_analyze_ftable')
+GROUP BY s.tablename
+ORDER BY s.tablename;
+       tablename       | num_stats 
+-----------------------+-----------
+ remote_analyze_ftable |         3
+ remote_analyze_table  |         3
+(2 rows)
+
+-- cleanup
+DROP FOREIGN TABLE remote_analyze_ftable;
+DROP TABLE remote_analyze_table;
+-- ===================================================================
 -- test for postgres_fdw_get_connections function with check_conn = true
 -- ===================================================================
 -- Disable debug_discard_caches in order to manage remote connections
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 5b7726800d0..2941ecbfb87 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -121,6 +121,7 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
 			strcmp(def->defname, "parallel_commit") == 0 ||
 			strcmp(def->defname, "parallel_abort") == 0 ||
 			strcmp(def->defname, "fetch_stats") == 0 ||
+			strcmp(def->defname, "remote_analyze") == 0 ||
 			strcmp(def->defname, "keep_connections") == 0)
 		{
 			/* these accept only boolean values */
@@ -283,6 +284,10 @@ InitPgFdwOptions(void)
 		{"fetch_stats", ForeignServerRelationId, false},
 		{"fetch_stats", ForeignTableRelationId, false},
 
+		/* remote_analyze is available on both server and table */
+		{"remote_analyze", ForeignServerRelationId, false},
+		{"remote_analyze", ForeignTableRelationId, false},
+
 		/*
 		 * sslcert and sslkey are in fact libpq options, but we repeat them
 		 * here to allow them to appear in both foreign server context (when
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 657cfced012..c52ddcb7a69 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5388,6 +5388,36 @@ import_cleanup:
 	return ok;
 }
 
+/*
+ * Analyze a remote table.
+ */
+static bool
+analyze_remote_table(PGconn *conn, const char *remote_schemaname,
+					 const char *remote_relname)
+{
+	StringInfoData buf;
+	PGresult   *res;
+	bool		ok = true;
+
+	initStringInfo(&buf);
+
+	appendStringInfo(&buf, "ANALYZE %s",
+					 quote_qualified_identifier(remote_schemaname, remote_relname));
+
+	res = pgfdw_exec_query(conn, buf.data, NULL);
+
+	if (res == NULL ||
+		PQresultStatus(res) != PGRES_COMMAND_OK)
+	{
+		pgfdw_report(WARNING, res, conn, buf.data);
+		ok = false;
+	}
+
+	PQclear(res);
+	pfree(buf.data);
+	return ok;
+}
+
 /*
  * Attempt to fetch remote relations stats.
  *
@@ -5514,7 +5544,7 @@ fetch_remote_statistics(PGconn *conn,
 						const char *remote_relname,
 						int server_version_num, int natts,
 						const RemoteAttributeMapping * remattrmap,
-						RemoteStatsResults * remstats)
+						bool remote_analyze, RemoteStatsResults * remstats)
 {
 	char	   *column_list = NULL;
 	PGresult   *attstats = NULL;
@@ -5566,6 +5596,35 @@ fetch_remote_statistics(PGconn *conn,
 		return true;
 	}
 
+	/*
+	 * If remote_analyze is enabled, then we will try to analyze the table and
+	 * then try again.
+	 */
+	if (!remote_analyze)
+		goto fetch_remote_statistics_fail;
+
+	if (!analyze_remote_table(conn, remote_schemaname, remote_relname))
+		goto fetch_remote_statistics_fail;
+
+	PQclear(attstats);
+	attstats = fetch_attstats(conn, remote_schemaname, remote_relname,
+							  column_list);
+
+	if (attstats == NULL || PQntuples(attstats) == 0)
+		goto fetch_remote_statistics_fail;
+
+	PQclear(relstats);
+	relstats = fetch_relstats(conn, remote_schemaname, remote_relname);
+
+	if (relstats == NULL)
+		goto fetch_remote_statistics_fail;
+
+	/* Second attempt worked */
+	pfree(column_list);
+	remstats->rel = relstats;
+	remstats->att = attstats;
+	return true;
+
 fetch_remote_statistics_fail:
 	ereport(WARNING,
 			errmsg("failed to import statistics from remote table %s.%s, ",
@@ -5720,9 +5779,11 @@ postgresImportStatistics(Relation relation, List *va_cols, int elevel)
 {
 
 	ForeignTable *table;
+	ForeignServer *server;
 	UserMapping *user;
 	PGconn	   *conn;
 	ListCell   *lc;
+	bool		remote_analyze = false;
 	int			server_version_num = 0;
 	const char *schemaname = NULL;
 	const char *relname = NULL;
@@ -5737,12 +5798,25 @@ postgresImportStatistics(Relation relation, List *va_cols, int elevel)
 	RemoteStatsResults remstats = {.rel = NULL,.att = NULL};
 
 	table = GetForeignTable(RelationGetRelid(relation));
+	server = GetForeignServer(table->serverid);
 	user = GetUserMapping(GetUserId(), table->serverid);
 	conn = GetConnection(user, false, NULL);
 	server_version_num = PQserverVersion(conn);
 	schemaname = get_namespace_name(RelationGetNamespace(relation));
 	relname = RelationGetRelationName(relation);
 
+	/*
+	 * Server-level options can be overridden by table-level options, so check
+	 * server-level first.
+	 */
+	foreach(lc, server->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(lc);
+
+		if (strcmp(def->defname, "remote_analyze") == 0)
+			remote_analyze = defGetBoolean(def);
+	}
+
 	foreach(lc, table->options)
 	{
 		DefElem    *def = (DefElem *) lfirst(lc);
@@ -5751,6 +5825,8 @@ postgresImportStatistics(Relation relation, List *va_cols, int elevel)
 			remote_schemaname = defGetString(def);
 		else if (strcmp(def->defname, "table_name") == 0)
 			remote_relname = defGetString(def);
+		else if (strcmp(def->defname, "remote_analyze") == 0)
+			remote_analyze = defGetBoolean(def);
 	}
 
 	/*
@@ -5822,7 +5898,7 @@ postgresImportStatistics(Relation relation, List *va_cols, int elevel)
 
 	ok = fetch_remote_statistics(conn, remote_schemaname, remote_relname,
 								 server_version_num, natts, remattrmap,
-								 &remstats);
+								 remote_analyze, &remstats);
 
 	ReleaseConnection(conn);
 
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 204ef78b1a4..6aa3b42dd4f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4405,6 +4405,40 @@ ANALYZE analyze_ftable;
 DROP FOREIGN TABLE analyze_ftable;
 DROP TABLE analyze_table;
 
+-- ===================================================================
+-- test remote analyze
+-- ===================================================================
+CREATE TABLE remote_analyze_table (id int, a text, b bigint);
+INSERT INTO remote_analyze_table (SELECT x FROM generate_series(1,1000) x);
+
+CREATE FOREIGN TABLE remote_analyze_ftable (id int, a text, b bigint)
+       SERVER loopback
+       OPTIONS (table_name 'remote_analyze_table',
+                fetch_stats 'true',
+                remote_analyze 'true');
+
+-- no stats before
+SELECT s.tablename, COUNT(*) AS num_stats
+FROM pg_stats AS s
+WHERE s.schemaname = 'public'
+AND s.tablename IN ('remote_analyze_table', 'remote_analyze_ftable')
+GROUP BY s.tablename
+ORDER BY s.tablename;
+
+ANALYZE remote_analyze_ftable;
+
+-- both stats after
+SELECT s.tablename, COUNT(*) AS num_stats
+FROM pg_stats AS s
+WHERE s.schemaname = 'public'
+AND s.tablename IN ('remote_analyze_table', 'remote_analyze_ftable')
+GROUP BY s.tablename
+ORDER BY s.tablename;
+
+-- cleanup
+DROP FOREIGN TABLE remote_analyze_ftable;
+DROP TABLE remote_analyze_table;
+
 -- ===================================================================
 -- test for postgres_fdw_get_connections function with check_conn = true
 -- ===================================================================
-- 
2.53.0