From: Nathan Bossart <nathan@postgresql.org>
Subject: [PATCH v2 2/2] vacuumdb: Don't skip empty relations in --missing-stats-only.
Date: Wed, 23 Apr 2025 10:37:42 -0500
Presently, --missing-stats-only skips relations with reltuples set
to 0 because empty relations don't get optimizer statistics.
However, 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 Postgres
14 and newer to use -1 for the latter case.) This ambiguity can
cause --missing-stats-only to inadvertently skip relations that
need optimizer statistics.
To fix, simply remove the check for reltuples != 0. This will
cause --missing-stats-only to analyze some empty tables, but that
doesn't seem too terrible a trade-off.
Reported-by: Christoph Berg <myon@debian.org>
Reviewed-by: Christoph Berg <myon@debian.org>
Discussion: https://postgr.es/m/aAjyvW5_fRGNr7yF%40msg.df7cb.de
---
src/bin/scripts/vacuumdb.c | 5 -----
1 file changed, 5 deletions(-)
inline patch 1: src/bin/scripts/vacuumdb.c (+0/-5 lines) download inline-1.patchdiff --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"
--
2.39.5 (Apple Git-154)
--Yk+v15XI0xqDWTQr--
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: nathan@postgresql.org
Subject: Re: [PATCH v2 2/2] vacuumdb: Don't skip empty relations in --missing-stats-only.
In-Reply-To: <no-message-id-1527@localhost>
* 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