From: Nathan Bossart <nathandbossart@gmail.com>
To: Christoph Berg <myon@debian.org>
Cc: Nathan Bossart <nathan@postgresql.org>
Cc: Corey Huinker <corey.huinker@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
Date: Wed, 23 Apr 2025 09:33:17 -0500
Message-ID: <aAj6LaoBqrSshnPd@nathan> (raw)
In-Reply-To: <aAjyvW5_fRGNr7yF@msg.df7cb.de>
References: <E1tueZC-0039rk-2P@gemulon.postgresql.org>
<aAfxfKC82B9NvJDj@msg.df7cb.de>
<aAjyvW5_fRGNr7yF@msg.df7cb.de>
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
Attachments:fix_missing_stats_only.patch (text/plain, 2.6K) downloaddiff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.cindex 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"
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-committers@postgresql.org
Cc: nathandbossart@gmail.com, myon@debian.org, nathan@postgresql.org, corey.huinker@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
In-Reply-To: <aAj6LaoBqrSshnPd@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox