Date: Wed, 23 Apr 2025 09:33:17 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Christoph Berg <myon@debian.org>
Cc: Nathan Bossart <nathan@postgresql.org>,
	Corey Huinker <corey.huinker@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
Message-ID: <aAj6LaoBqrSshnPd@nathan>
References: <E1tueZC-0039rk-2P@gemulon.postgresql.org>
 <aAfxfKC82B9NvJDj@msg.df7cb.de>
 <aAjyvW5_fRGNr7yF@msg.df7cb.de>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="1IeyT3aJTime+xBw"
Content-Disposition: inline
In-Reply-To: <aAjyvW5_fRGNr7yF@msg.df7cb.de>
Archived-At: <https://www.postgresql.org/message-id/aAj6LaoBqrSshnPd%40nathan>
Precedence: bulk


--1IeyT3aJTime+xBw
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Apr 23, 2025 at 04:01:33PM +0200, Christoph Berg wrote:
> If I create a table in a PG13-or-earlier cluster, never ANALYZE it,
> and then pg_upgrade to 18 and run vacuumdb --analyze-only
> --missing-stats-only, the table will not get analyzed. The only table
> visited there is pg_largeobject.

I suspect this is due to commit 3d351d9, which started using -1 for
reltuples before the first vacuum/analyze.  Before that, we set it to 0,
which could also mean the table is empty.  --missing-stats-only checks for
reltuples != 0.

My first reaction is that we should just remove the reltuples != 0 check.
That means vacuumdb might analyze some empty tables, but that doesn't seem
too terrible.

-- 
nathan

--1IeyT3aJTime+xBw
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="fix_missing_stats_only.patch"

diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 22067faaf7d..79b1096eb08 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -954,7 +954,6 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
 		appendPQExpBufferStr(&catalog_query,
 							 " EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
 							 " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
-							 " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
 							 " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
 							 " AND NOT a.attisdropped\n"
 							 " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
@@ -967,7 +966,6 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
 		appendPQExpBufferStr(&catalog_query,
 							 " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
 							 " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
-							 " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
 							 " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
 							 " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
 							 " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
@@ -979,7 +977,6 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
 							 " JOIN pg_catalog.pg_index i"
 							 " ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n"
 							 " WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n"
-							 " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
 							 " AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]"
 							 " OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n"
 							 " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
@@ -994,7 +991,6 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
 		appendPQExpBufferStr(&catalog_query,
 							 " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
 							 " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
-							 " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
 							 " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
 							 " AND NOT a.attisdropped\n"
 							 " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
@@ -1011,7 +1007,6 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
 		appendPQExpBufferStr(&catalog_query,
 							 " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
 							 " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
-							 " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
 							 " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
 							 " AND c.relhassubclass\n"
 							 " AND NOT p.inherited\n"

--1IeyT3aJTime+xBw--