agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathan@postgresql.org>
Subject: [PATCH v1 1/1] pg_dump: Avoid full scans of pg_stats.
Date: Sun, 30 Aug 2026 09:21:47 -0500
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--
view thread (169+ messages) latest in thread
Message-ID: <no-message-id-1700742@localhost>
Permalink: ../no-message-id-1700742@localhost/
Also on: postgresql.org/message-id/no-message-id-1700742@localhost
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathan@postgresql.org
Subject: Re: [PATCH v1 1/1] pg_dump: Avoid full scans of pg_stats.
In-Reply-To: <no-message-id-1700742@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox