pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb --missing-stats-only and pg_upgrade from PG13
6+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2025-04-30 9879105024 vacuumdb: Don't skip empty relations in --missing-stats-only mode.
* vacuumdb --missing-stats-only and pg_upgrade from PG13
@ 2025-04-23 14:01 Christoph Berg <myon@debian.org>
2025-04-23 14:33 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Berg @ 2025-04-23 14:01 UTC (permalink / raw)
To: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Re: To Nathan Bossart
> > Update guidance for running vacuumdb after pg_upgrade.
> >
> > Now that pg_upgrade can carry over most optimizer statistics, we
> > should recommend using vacuumdb's new --missing-stats-only option
> > to only analyze relations that are missing statistics.
>
> I've been looking at vacuumdb --missing-stats-only because Debian's
> pg_upgradecluster is using that now.
The reason I was looking closely yesterday is because Debian's
regression tests were tripping over it, but I only figured out the
problem today:
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.
Upgrades from 14..17 are fine.
Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
2025-04-23 14:01 vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
@ 2025-04-23 14:33 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:38 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2025-04-23 14:33 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
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) download
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"
* Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
2025-04-23 14:01 vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 14:33 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
@ 2025-04-23 14:38 ` Christoph Berg <myon@debian.org>
2025-04-23 15:43 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Berg @ 2025-04-23 14:38 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
Re: Nathan Bossart
> 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.
Or some tables that aren't empty but were never analyzed when they
should have been. Sounds like a good thing.
Thanks!
Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
2025-04-23 14:01 vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 14:33 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:38 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
@ 2025-04-23 15:43 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-24 13:25 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2025-04-23 15:43 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
Here is what I have staged for commit. I'll aim to commit these patches
sometime next week to give time for additional feedback.
--
nathan
Attachments:
v2-0001-Further-adjust-guidance-for-running-vacuumdb-afte.patch (text/plain, 0B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
2025-04-23 14:01 vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 14:33 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:38 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 15:43 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
@ 2025-04-24 13:25 ` Christoph Berg <myon@debian.org>
2025-04-30 19:22 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Berg @ 2025-04-24 13:25 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
Re: Nathan Bossart
> Here is what I have staged for commit. I'll aim to commit these patches
> sometime next week to give time for additional feedback.
I confirm my PG13 test table gets analyzed now with the patch.
Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb --missing-stats-only and pg_upgrade from PG13
2025-04-23 14:01 vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 14:33 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:38 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 15:43 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Nathan Bossart <nathandbossart@gmail.com>
2025-04-24 13:25 ` Re: vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
@ 2025-04-30 19:22 ` Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Nathan Bossart @ 2025-04-30 19:22 UTC (permalink / raw)
To: Christoph Berg <myon@debian.org>; +Cc: Nathan Bossart <nathan@postgresql.org>; Corey Huinker <corey.huinker@gmail.com>; pgsql-hackers@lists.postgresql.org
On Thu, Apr 24, 2025 at 03:25:55PM +0200, Christoph Berg wrote:
> Re: Nathan Bossart
>> Here is what I have staged for commit. I'll aim to commit these patches
>> sometime next week to give time for additional feedback.
>
> I confirm my PG13 test table gets analyzed now with the patch.
Committed.
--
nathan
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-04-30 19:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-23 14:01 vacuumdb --missing-stats-only and pg_upgrade from PG13 Christoph Berg <myon@debian.org>
2025-04-23 14:33 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-23 14:38 ` Christoph Berg <myon@debian.org>
2025-04-23 15:43 ` Nathan Bossart <nathandbossart@gmail.com>
2025-04-24 13:25 ` Christoph Berg <myon@debian.org>
2025-04-30 19:22 ` Nathan Bossart <nathandbossart@gmail.com>
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