Date: Sun, 30 Aug 2026 09:26:38 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>,
	Sami Imseih <samimseih@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Add starelid, attnum to pg_stats and leverage this in pg_dump
Message-ID: <apQ9nvLW_zFdarYd@nathan>
References: <abhXqx-xffsknv3B@nathan>
 <CADkLM=cuwGZU0i51vn+SUqptXB8QT8rhRRYshns+pX0KxczmAQ@mail.gmail.com>
 <abhk0oLSA7lQ62jJ@nathan>
 <abh63Ro7XBvik6MD@paquier.xyz>
 <abiJtpb6FsluRpGk@nathan>
 <abiMCYxA5JI5T57P@paquier.xyz>
 <CADkLM=dc1P231z9X1-PgFsu6P+Bft4K9eE+nT7=zeX=_7YBuXQ@mail.gmail.com>
 <ablksYjpJLwwVmm5@nathan>
 <abls1l0aNZMRu33u@nathan>
 <abmCfOljRb_CbYu0@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="H90LzND/eZZd006U"
Content-Disposition: inline
In-Reply-To: <abmCfOljRb_CbYu0@nathan>
Archived-At: <https://www.postgresql.org/message-id/apQ9nvLW_zFdarYd%40nathan>
Precedence: bulk

--H90LzND/eZZd006U
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Claude advises me that removing the redundant filter clause was a mistake.
See the attached patch.

-- 
nathan

--H90LzND/eZZd006U
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v1-0001-pg_dump-Avoid-full-scans-of-pg_stats.patch

From 42f7c17cbc530d78a3ddc94ba0193d3da3f487a1 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Sun, 30 Aug 2026 09:21:47 -0500
Subject: [PATCH v1 1/1] pg_dump: Avoid full scans of pg_stats.

Commit 4b5ba0c4ca taught the attribute statistics query to join
pg_stats on the new tableid column, and it dropped the redundant
filter clause on s.tablename at the same time, on the theory that
the clause was only compensating for the name-based lookup.  That
isn't what the clause was doing.  pg_stats is a security barrier
view, so the planner will not push a join clause down into it,
whereas the redundant clause is a restriction clause on a
leakproof operator, which it will push.  Presently, we scan all of
pg_statistic once per batch of 64 relations, which makes dumping
statistics quadratic in the number of relations.  With 8000
tables, pg_dump --statistics-only takes 27.6s instead of 1.5s, and
pg_upgrade pays that cost during downtime.

To fix, put the filter clause back, now on s.tableid, and correct
the comment that claimed the OIDs had made it unnecessary.  I've
checked that the resulting plan holds up as a generic plan, which
matters here because pg_dump prepares this query once and executes
it once per batch.

Oversight in commit 4b5ba0c4ca.

Discussion: https://postgr.es/m/CADkLM%3DcoCVy92QkVUUTLdo5eO2bMDtwMrzRn_8miAhX%2BuPaqXg%40mail.gmail.com
Backpatch-through: 19
---
 src/bin/pg_dump/pg_dump.c | 12 +++++++-----
 1 file changed, 7 insertions(+), 5 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index db14834e430..b4bb0ce7b22 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -11150,17 +11150,19 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te)
 		 * The results must be in the order of the relations supplied in the
 		 * parameters to ensure we remain in sync as we walk through the TOC.
 		 *
-		 * For versions before 19, the redundant filter clause on s.tablename
-		 * = ANY(...) seems sufficient to convince the planner to use
-		 * pg_class_relname_nsp_index, which avoids a full scan of pg_stats.
-		 * In newer versions, pg_stats returns the table OIDs, eliminating the
-		 * need for that hack.
+		 * pg_stats is a security barrier view, so the planner will not push
+		 * the join clause down into it, and we would scan all of pg_statistic
+		 * once per batch.  The redundant filter clause is a restriction
+		 * clause on a leakproof operator, which the planner is willing to
+		 * push down, and that gets us an index scan.  This may not work for
+		 * all versions.
 		 */
 		if (fout->remoteVersion >= 190000)
 			appendPQExpBufferStr(query,
 								 "FROM pg_catalog.pg_stats s "
 								 "JOIN unnest($1) WITH ORDINALITY AS u (tableid, ord) "
 								 "ON s.tableid = u.tableid "
+								 "WHERE s.tableid = ANY($1) "
 								 "ORDER BY u.ord, s.attname, s.inherited");
 		else
 			appendPQExpBufferStr(query,
-- 
2.55.0


--H90LzND/eZZd006U--