public inbox for [email protected]
help / color / mirror / Atom feedFrom: Fujii Masao <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Mircea Cadariu <[email protected]>
Cc: Zechman, Derek S <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: analyze-in-stages post upgrade questions
Date: Mon, 18 Aug 2025 11:38:23 +0900
Message-ID: <CAHGQGwGje3mv5sOmyCJ0NroNPF8938OYCOHMJZbegTekccZZ=g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.namprd04.prod.outlook.com>
<[email protected]>
<[email protected]>
<PH0PR04MB82943D67CD179002714AA503C044A@PH0PR04MB8294.namprd04.prod.outlook.com>
<[email protected]>
<PH0PR04MB8294E796D6297D4E3BE4EFB6C049A@PH0PR04MB8294.namprd04.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAHGQGwEvwpydO7hjnB51Q2zgS0ydv31X4HCFLNzMeqLr=poS8w@mail.gmail.com>
<[email protected]>
<CAHGQGwEj8PCk1OVveJ_F1sj_COrV95N-mfu_tLUn4gdcAM24kA@mail.gmail.com>
<[email protected]>
On Thu, Aug 7, 2025 at 5:52 AM Laurenz Albe <[email protected]> wrote:
> > I understand your point. But on second thought, since the patch changes
> > behavior, I'm leaning toward treating it as an improvement, so it should
> > only go to master...
>
> I agree that this behavior change should not be backpatched.
> That is not a bugfix.
+1
> > --------------------
> > vacuumdb should generally follow the behavior of the underlying
> > VACUUM and ANALYZE commands. If analyze_only is true, process
> > regular tables, materialized views, and partitioned tables, just like
> > ANALYZE (with no specific target tables) does. Otherwise, process
> > only regular tables and materialized views, since VACUUM skips
> > partitioned tables when no target tables are specified.
> > --------------------
>
> I am fine with that suggestion.
Thanks! So I've updated the patch based on my earlier comments.
Unless there are objections, I'll commit the attached version to master only.
Regards,
--
Fujii Masao
Attachments:
[application/octet-stream] v3-0001-vacuumdb-Make-vacuumdb-analyze-only-process-parti.patch (4.8K, 2-v3-0001-vacuumdb-Make-vacuumdb-analyze-only-process-parti.patch)
download | inline diff:
From 5c969379514aed6de612165f39fa709740a56933 Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email protected]>
Date: Mon, 18 Aug 2025 10:41:15 +0900
Subject: [PATCH v3] vacuumdb: Make vacuumdb --analyze-only process partitioned
tables.
vacuumdb should follow the behavior of the underlying VACUUM and ANALYZE
commands. When --analyze-only is used, it ought to analyze regular tables,
materialized views, and partitioned tables, just as ANALYZE (with no explicit
target tables) does. Otherwise, it should only process regular tables and
materialized views, since VACUUM skips partitioned tables when no targets
are given.
Previously, vacuumdb --analyze-only skipped partitioned tables. This was
inconsistent, and also inconvenient after pg_upgrade, where --analyze-only
is typically used to gather missing statistics.
This commit fixes the behavior so that vacuumdb --analyze-only also processes
partitioned tables. As a result, both vacuumdb --analyze-only and
ANALYZE (with no explicit targets) now analyze regular tables,
partitioned tables, and materialized views, but not foreign tables.
Because this is a nontrivial behavior change, it is applied only to master.
Reported-by: Zechman, Derek S <[email protected]>
Author: Laurenz Albe <[email protected]>
Co-authored-by: Mircea Cadariu <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Discussion: https://postgr.es/m/CO1PR04MB8281387B9AD9DE30976966BBC045A%40CO1PR04MB8281.namprd04.prod.outlook.com
---
doc/src/sgml/ref/vacuumdb.sgml | 9 +++++++++
src/bin/scripts/t/100_vacuumdb.pl | 11 +++++++++++
src/bin/scripts/vacuumdb.c | 24 ++++++++++++++++++++----
3 files changed, 40 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index c7d9dca17b8..53147480515 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -397,6 +397,15 @@ PostgreSQL documentation
Multiple tables can be vacuumed by writing multiple
<option>-t</option> switches.
</para>
+ <para>
+ If no tables are specified with the <option>--table</option> option,
+ <application>vacuumdb</application> will clean all regular tables
+ and materialized views in the connected database.
+ If <option>--analyze-only</option> or
+ <option>--analyze-in-stages</option> is also specified,
+ it will analyze all regular tables, partitioned tables,
+ and materialized views (but not foreign tables).
+ </para>
<tip>
<para>
If you specify columns, you probably have to escape the parentheses
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index ff56a13b46b..240f0fdd3e5 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -340,4 +340,15 @@ $node->issues_sql_unlike(
qr/statement:\ ANALYZE/sx,
'--missing-stats-only with no missing partition stats');
+$node->safe_psql('postgres',
+ "CREATE TABLE parent_table (a INT) PARTITION BY LIST (a);\n"
+ . "CREATE TABLE child_table PARTITION OF parent_table FOR VALUES IN (1);\n"
+ . "INSERT INTO parent_table VALUES (1);\n");
+$node->issues_sql_like(
+ [
+ 'vacuumdb', '--analyze-only', 'postgres'
+ ],
+ qr/statement: ANALYZE public.parent_table/s,
+ '--analyze-only updates statistics for partitioned tables');
+
done_testing();
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 79b1096eb08..22093e50aa5 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -911,10 +911,26 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
*/
if ((objfilter & OBJFILTER_TABLE) == 0)
{
- appendPQExpBufferStr(&catalog_query,
- " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
- CppAsString2(RELKIND_RELATION) ", "
- CppAsString2(RELKIND_MATVIEW) "])\n");
+ /*
+ * vacuumdb should generally follow the behavior of the underlying
+ * VACUUM and ANALYZE commands. If analyze_only is true, process
+ * regular tables, materialized views, and partitioned tables, just
+ * like ANALYZE (with no specific target tables) does. Otherwise,
+ * process only regular tables and materialized views, since VACUUM
+ * skips partitioned tables when no target tables are specified.
+ */
+ if (vacopts->analyze_only)
+ appendPQExpBufferStr(&catalog_query,
+ " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
+ CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_MATVIEW) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) "])\n");
+ else
+ appendPQExpBufferStr(&catalog_query,
+ " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
+ CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_MATVIEW) "])\n");
+
}
/*
--
2.50.1
view thread (19+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: analyze-in-stages post upgrade questions
In-Reply-To: <CAHGQGwGje3mv5sOmyCJ0NroNPF8938OYCOHMJZbegTekccZZ=g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox