[moving to pgsql-hackers]
On Thu, 2025-07-10 at 17:20 +0100, Mircea Cadariu wrote:
> I have only one suggestion for the patch. Consider adding a
> corresponding test in src/bin/scripts/t/100_vacuumdb.pl.
>
> Proposal (I used this to check the patch):
>
> $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:\s+ANALYZE\s+public\.parent_table/s,
> '--analyze_only updates statistics for partitioned tables');
Good idea; done in the attached version 2 of the patch.
Yours,
Laurenz Albe
From 4bf5048da6ffd250ee204fff419cf975aa7a6548 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Fri, 11 Jul 2025 11:46:55 +0200
Subject: [PATCH v2] Make vacuumdb -Z process partitioned tables
Autoanalyze won't process partitioned tables, but a manual ANALYZE
of the whole database does. So it was surprising that partitioned
tables were not processed by "vacuumdb --analyze-only".
In addition, "vacuumdb --analyze-only" is what you run to collect
missing statistics after a "pg_upgrade", and it makes a lot of sense
to collect statistics for partitioned tables at that point.
However, running VACUUM on partitioned tables adds no benefit over
vacuuming the partitions, and VACUUM is more expensive than ANALYZE,
so we won't treat partitioned tables unless --analyze-only is given.
Otherwise, we'd end up vacuuming the partitions twice, which would
be a waste of resources.
Author: Laurenz Albe <laurenz.a...@cybertec.at>
Author: Mircea Cadariu <cadariu.mir...@gmail.com>
Discussion: https://postgr.es/m/CO1PR04MB8281387B9AD9DE30976966BBC045A%40CO1PR04MB8281.namprd04.prod.outlook.com
---
doc/src/sgml/ref/vacuumdb.sgml | 4 ++++
src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++++++
src/bin/scripts/vacuumdb.c | 23 +++++++++++++++++++----
3 files changed, 35 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index b0680a61814..6a4c8d51b7f 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -440,6 +440,10 @@ PostgreSQL documentation
<listitem>
<para>
Only calculate statistics for use by the optimizer (no vacuum).
+ If that option is specified, <command>vacuumdb</command> will also
+ process partitioned tables. Without that option, only the partitions
+ will be considered, unless a partitioned table is explicitly specified
+ with the <option>--table</option> option.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index ff56a13b46b..9ab42c39a94 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -340,4 +340,16 @@ $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:\s+ANALYZE\s+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..d7ca8300f43 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -911,10 +911,25 @@ 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");
+ /*
+ * VACUUMing partitioned tables would be unreasonably expensive, since
+ * that entails processing the partitions twice (once as part of the
+ * partitioned table, once as tables in their own right) for no
+ * benefit. But if we only ANALYZE, collecting statistics for
+ * partitioned tables is worth the effort.
+ */
+ 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.0
