Claude advises me that removing the redundant filter clause was a mistake. See the attached patch.
-- nathan
>From 42f7c17cbc530d78a3ddc94ba0193d3da3f487a1 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> 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
