On Tue, May 20, 2025 at 10:32:39AM -0700, Hari Krishna Sunder wrote: > Ah ya, forgot that reltuples are not always accurate. This sounds > reasonable to me.
Cool. Here is what I have staged for commit, which I am planning to do shortly. -- nathan
>From e68770a6089500e6b4d02bcb3009ec12da392d5f Mon Sep 17 00:00:00 2001 From: Nathan Bossart <nat...@postgresql.org> Date: Wed, 21 May 2025 10:53:29 -0500 Subject: [PATCH v3 1/1] pg_dump: Adjust reltuples from 0 to -1 for dumps on older versions. Before v14, a reltuples value of 0 was ambiguous: it could either mean the relation is empty, or it could mean that it hadn't yet been vacuumed or analyzed. (Commit 3d351d916b taught v14 and newer to use -1 for the latter case.) This ambiguity can cause the planner to choose inefficient plans after restoring to v18 or newer. To fix, let's just dump reltuples as -1 in that case. This will cause some truly empty tables to be seen as not-yet-processed, but that seems unlikely to cause too much trouble in practice. Commit 9879105024 fixed a similar problem for vacuumdb by removing the check for reltuples != 0. Presumably we could reinstate that check now, but I've chosen to leave it in place in case reltuples isn't accurate. As before, processing some empty tables seems pretty innocuous. Author: Hari Krishna Sunder <hari.db...@gmail.com> Discussion: https://postgr.es/m/CAAeiqZ0o2p4SX5_xPcuAbbsmXjg6MJLNuPYSLUjC%3DWh-VeW64A%40mail.gmail.com --- src/bin/pg_dump/pg_dump.c | 15 ++++++++++++++- 1 file changed, 14 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c73e73a87d1..23c8c25e33d 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -10929,7 +10929,20 @@ dumpRelationStats_dumper(Archive *fout, const void *userArg, const TocEntry *te) appendStringLiteralAH(out, rsinfo->dobj.name, fout); appendPQExpBufferStr(out, ",\n"); appendPQExpBuffer(out, "\t'relpages', '%d'::integer,\n", rsinfo->relpages); - appendPQExpBuffer(out, "\t'reltuples', '%s'::real,\n", rsinfo->reltuples); + + /* + * Before v14, a reltuples value of 0 was ambiguous: it could either mean + * the relation is empty, or it could mean that it hadn't yet been + * vacuumed or analyzed. (Newer versions use -1 for the latter case.) + * This ambiguity can cause the planner to choose inefficient plans after + * restoring to v18 or newer. To deal with this, let's just set reltuples + * to -1 in that case. + */ + if (fout->remoteVersion < 140000 && strcmp("0", rsinfo->reltuples) == 0) + appendPQExpBufferStr(out, "\t'reltuples', '-1'::real,\n"); + else + appendPQExpBuffer(out, "\t'reltuples', '%s'::real,\n", rsinfo->reltuples); + appendPQExpBuffer(out, "\t'relallvisible', '%d'::integer", rsinfo->relallvisible); -- 2.39.5 (Apple Git-154)
