On Wed, 20 Aug 2025 13:30:12 +0900 Yugo Nagata <nag...@sraoss.co.jp> wrote:
> On Wed, 20 Aug 2025 12:49:14 +0900 > Fujii Masao <masao.fu...@gmail.com> wrote: > > > On Wed, Aug 20, 2025 at 10:42 AM Yugo Nagata <nag...@sraoss.co.jp> wrote: > > > > > > Hi, > > > > > > I found that "vacuumdb --missing-stats-only" always performs ANALYZE > > > on tables with a virtual generated column, since such columns currently > > > never have statistics. This seems like an obvious waste, so I've attached > > > a patch to fix it, ensuring that virtual generated columns are not > > > regarded as missing statistics. > > > > Thanks for the report and patch! This seems to be an oversight from > > the commit that added virtual generated columns. > > > > For the patch, shouldn't we also add a regression test for > > --missing-stats-only > > with generated columns, to prevent this issue from happening again? > > Thank you for reviewing the patch and your suggestion. > > I agree that we should add a test, since the behavior may change in the future > when statistics begin to be collected for virtual generated columns, and the > test > will serve as a reminder when this behavior changes. > > I've attached a updated patch including the test. The patch conflicted with the latest commit, so I rebased it. Regards, Yugo Nagata -- Yugo Nagata <nag...@sraoss.co.jp>
>From 9355bca773f306c8012cb40c52a5ef769d142e7e Mon Sep 17 00:00:00 2001 From: Yugo Nagata <nag...@sraoss.co.jp> Date: Wed, 20 Aug 2025 10:41:49 +0900 Subject: [PATCH v3] Avoid treating virtual generated columns as missing statistics in vacuumdb --missing-stats-only Previously, vacuumdb --missing-stats-only always ran ANALYZE on tables with virtual generated columns, since such columns never have statistics. This was unnecessary, so virtual generated columns are no longer regarded as missing statistics. Author: Yugo Nagata <nag...@sraoss.co.jp> Reviewed-by: Fujii Masao <masao.fu...@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/20250820104226.8ba51e43164cd590b863ce41%40sraoss.co.jp --- src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++++++ src/bin/scripts/vacuumdb.c | 3 +++ 2 files changed, 15 insertions(+) diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index 240f0fdd3e5..c9ed7463008 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -351,4 +351,16 @@ $node->issues_sql_like( qr/statement: ANALYZE public.parent_table/s, '--analyze-only updates statistics for partitioned tables'); +$node->safe_psql('postgres', + 'ALTER TABLE regression_vacuumdb_test ADD COLUMN c int GENERATED ALWAYS AS (a+b)' +); +$node->issues_sql_unlike( + [ + 'vacuumdb', '--analyze-only', + '--missing-stats-only', '-t', + 'regression_vacuumdb_parted', 'postgres' + ], + qr/statement:\ ANALYZE/sx, + '--missing-stats-only with virtual generated column'); + done_testing(); diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 22093e50aa5..5a8ec959b06 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -14,6 +14,7 @@ #include <limits.h> +#include "catalog/pg_attribute_d.h" #include "catalog/pg_class_d.h" #include "common.h" #include "common/connect.h" @@ -973,6 +974,8 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, " 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" + " AND a.attgenerated OPERATOR(pg_catalog.<>) " + CppAsString2(ATTRIBUTE_GENERATED_VIRTUAL) "\n" " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" -- 2.43.0
