agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedanalyze-in-stages post upgrade questions
33+ messages / 9 participants
[nested] [flat]
* analyze-in-stages post upgrade questions
@ 2025-06-27 13:35 Zechman, Derek S <Derek.S.Zechman@snapon.com>
0 siblings, 2 replies; 33+ messages in thread
From: Zechman, Derek S @ 2025-06-27 13:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and performed the analyze-in-stages post upgrade. It has been noticed that some plans changed to use hash joins instead of nested loops. Further investigation found it was because the parent table of partitioned tables did not have stats. After running an ANALYZE on the parent tables we got similar plan an execution times as before.
I have two questions
1 - Why does analyze-in-stages not analyze the parent tables?
2 - What happens if we do not run analyze-in-stages post upgrade and just run an analyze?
Thanks,
Sean
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-06-27 13:41 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Zechman, Derek S <Derek.S.Zechman@snapon.com>
1 sibling, 1 reply; 33+ messages in thread
From: Ron Johnson @ 2025-06-27 13:41 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Fri, Jun 27, 2025 at 9:35 AM Zechman, Derek S <Derek.S.Zechman@snapon.com>
wrote:
>
>
> We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and
> performed the analyze-in-stages post upgrade. It has been noticed that
> some plans changed to use hash joins instead of nested loops. Further
> investigation found it was because the parent table of partitioned tables
> did not have stats. After running an ANALYZE on the parent tables we got
> similar plan an execution times as before.
>
>
>
> I have two questions
>
> 1 - Why does analyze-in-stages not analyze the parent tables?
>
> 2 – What happens if we do not run analyze-in-stages post upgrade and just
> run an analyze?
>
It takes more time, and you don't have *any* statistics on a given table
until the ANALYZE on that table completes.
How long did "vacuumdb --analyze-only --jobs=$mumble your_db" take?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-06-27 15:31 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Zechman, Derek S <Derek.S.Zechman@snapon.com>
1 sibling, 1 reply; 33+ messages in thread
From: Adrian Klaver @ 2025-06-27 15:31 UTC (permalink / raw)
To: Zechman, Derek S <Derek.S.Zechman@snapon.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 6/27/25 06:35, Zechman, Derek S wrote:
> We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and
> performed the analyze-in-stages post upgrade. It has been noticed that
> some plans changed to use hash joins instead of nested loops. Further
> investigation found it was because the parent table of partitioned
> tables did not have stats. After running an ANALYZE on the parent
> tables we got similar plan an execution times as before.
>
> I have two questions
>
> 1 - Why does analyze-in-stages not analyze the parent tables?
>
> 2 – What happens if we do not run analyze-in-stages post upgrade and
> just run an analyze?
It is spelled out in the docs:
https://www.postgresql.org/docs/current/pgupgrade.html
Emphasis added
"Using vacuumdb --all --analyze-only can efficiently generate such
statistics, and the use of --jobs can speed it up. Option
--analyze-in-stages can be used to generate **minimal statistics**
quickly. If vacuum_cost_delay is set to a non-zero value, this can be
overridden to speed up statistics generation using PGOPTIONS, e.g.,
PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ...."
and from here:
https://www.postgresql.org/docs/current/app-vacuumdb.html
"--analyze-in-stages
Only calculate statistics for use by the optimizer (no vacuum),
like --analyze-only. Run three stages of analyze; the first stage uses
the lowest possible statistics target (see default_statistics_target) to
produce usable statistics faster, and subsequent stages build the full
statistics.
This option is only useful to analyze a database that currently has
no statistics or has wholly incorrect ones, such as if it is newly
populated from a restored dump or by pg_upgrade. Be aware that running
with this option in a database with existing statistics may cause the
query optimizer choices to become transiently worse due to the low
statistics targets of the early stages.
"
>
> Thanks,
>
> Sean
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-06-27 21:39 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-06-27 21:39 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Fri, 2025-06-27 at 08:31 -0700, Adrian Klaver wrote:
> On 6/27/25 06:35, Zechman, Derek S wrote:
> > We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and
> > performed the analyze-in-stages post upgrade. It has been noticed that
> > some plans changed to use hash joins instead of nested loops. Further
> > investigation found it was because the parent table of partitioned
> > tables did not have stats. After running an ANALYZE on the parent
> > tables we got similar plan an execution times as before.
> >
> > I have two questions
> >
> > 1 - Why does analyze-in-stages not analyze the parent tables?
> >
> > 2 – What happens if we do not run analyze-in-stages post upgrade and
> > just run an analyze?
>
> It is spelled out in the docs:
>
> https://www.postgresql.org/docs/current/pgupgrade.html
>
> Emphasis added
>
> "Using vacuumdb --all --analyze-only can efficiently generate such
> statistics, and the use of --jobs can speed it up. Option
> --analyze-in-stages can be used to generate **minimal statistics**
> quickly. If vacuum_cost_delay is set to a non-zero value, this can be
> overridden to speed up statistics generation using PGOPTIONS, e.g.,
> PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ...."
>
> and from here:
>
> https://www.postgresql.org/docs/current/app-vacuumdb.html
>
> "--analyze-in-stages
>
> Only calculate statistics for use by the optimizer (no vacuum),
> like --analyze-only. Run three stages of analyze; the first stage uses
> the lowest possible statistics target (see default_statistics_target) to
> produce usable statistics faster, and subsequent stages build the full
> statistics.
>
> This option is only useful to analyze a database that currently has
> no statistics or has wholly incorrect ones, such as if it is newly
> populated from a restored dump or by pg_upgrade. Be aware that running
> with this option in a database with existing statistics may cause the
> query optimizer choices to become transiently worse due to the low
> statistics targets of the early stages.
Well, that wouldn't explain why it doesn't work on partitioned tables.
I am under the impression that it should.
Derek, can cou share the pg_stats entries for the partitioned table?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* RE: analyze-in-stages post upgrade questions
@ 2025-06-28 01:11 Zechman, Derek S <Derek.S.Zechman@snapon.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 33+ messages in thread
From: Zechman, Derek S @ 2025-06-28 01:11 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and performed the analyze-in-stages post upgrade. It has been noticed that some plans changed to use hash joins instead of nested loops. Further investigation found it was because the parent table of partitioned tables did not have stats. After running an ANALYZE on the parent tables we got similar plan an execution times as before.
I have two questions
1 - Why does analyze-in-stages not analyze the parent tables?
2 – What happens if we do not run analyze-in-stages post upgrade and just run an analyze?
“It takes more time, and you don't have any statistics on a given table until the ANALYZE on that table completes.
How long did "vacuumdb --analyze-only --jobs=$mumble your_db" take?”
Thanks – that makes sense. I understand what analyze in stages does just wish it would include parent tables.
"vacuumdb --all --analyze-only --jobs=7" took about 75 minutes where the analyze-in-stages after upgrade took 115 minutes. Neither of these activities analyzed the parent tables.
Reading more and it seems vacuumdb doesn’t analyze parent tables and a manual analyze on those is needed if we want better planner statistics.
^ permalink raw reply [nested|flat] 33+ messages in thread
* RE: analyze-in-stages post upgrade questions
@ 2025-06-28 01:23 Zechman, Derek S <Derek.S.Zechman@snapon.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Zechman, Derek S @ 2025-06-28 01:23 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> > We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and
> > performed the analyze-in-stages post upgrade. It has been noticed that
> > some plans changed to use hash joins instead of nested loops. Further
> > investigation found it was because the parent table of partitioned
> > tables did not have stats. After running an ANALYZE on the parent
> > tables we got similar plan an execution times as before.
> >
> > I have two questions
> >
> > 1 - Why does analyze-in-stages not analyze the parent tables?
> >
> > 2 – What happens if we do not run analyze-in-stages post upgrade and
> > just run an analyze?
>
> It is spelled out in the docs:
>
> https://urldefense.com/v3/__https://www.postgresql.org/docs/current/pgupgrade.html__;!!Lf_9VycLqA!jU...;
>
> Emphasis added
>
> "Using vacuumdb --all --analyze-only can efficiently generate such
> statistics, and the use of --jobs can speed it up. Option
> --analyze-in-stages can be used to generate **minimal statistics**
> quickly. If vacuum_cost_delay is set to a non-zero value, this can be
> overridden to speed up statistics generation using PGOPTIONS, e.g.,
> PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ...."
>
> and from here:
>
> https://urldefense.com/v3/__https://www.postgresql.org/docs/current/app-vacuumdb.html__;!!Lf_9VycLqA...;
>
> "--analyze-in-stages
>
> Only calculate statistics for use by the optimizer (no vacuum),
> like --analyze-only. Run three stages of analyze; the first stage uses
> the lowest possible statistics target (see default_statistics_target) to
> produce usable statistics faster, and subsequent stages build the full
> statistics.
>
> This option is only useful to analyze a database that currently has
> no statistics or has wholly incorrect ones, such as if it is newly
> populated from a restored dump or by pg_upgrade. Be aware that running
> with this option in a database with existing statistics may cause the
> query optimizer choices to become transiently worse due to the low
> statistics targets of the early stages.
Well, that wouldn't explain why it doesn't work on partitioned tables.
I am under the impression that it should.
Derek, can cou share the pg_stats entries for the partitioned table?
Yours,
Laurenz Albe
There are no entries in pg_stats for the parent table until after I manually run an analyze on it – Example below
=> select relname, reltuples, relkind from pg_class where relname ~ '^chapter_[0-9]+$' or relname='chapter' order by 1;
relname | reltuples | relkind
-------------+-----------+---------
chapter | -1 | p
chapter_1 | 4 | r
chapter_10 | 4 | r
chapter_100 | 30 | r
chapter_101 | 15 | r
chapter_102 | 15 | r
…
=> select count(*) from pg_stats where tablename='chapter';
count
-------
0
(1 row)
=> analyze chapter;
ANALYZE
=> select relname, reltuples, relkind from pg_class where relkind ='p' and relname='chapter';
relname | reltuples | relkind
---------+-----------+---------
chapter | 7589 | p
(1 row)
=> select count(*) from pg_stats where tablename='chapter';
count
-------
49
(1 row)
toy_epc_stg_1_db=>
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-06-28 05:25 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Zechman, Derek S <Derek.S.Zechman@snapon.com>
0 siblings, 2 replies; 33+ messages in thread
From: Laurenz Albe @ 2025-06-28 05:25 UTC (permalink / raw)
To: Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Sat, 2025-06-28 at 01:23 +0000, Zechman, Derek S wrote:
> > Well, that wouldn't explain why it doesn't work on partitioned tables.
> > I am under the impression that it should.
> >
> > Derek, can cou share the pg_stats entries for the partitioned table?
>
> There are no entries in pg_stats for the parent table until after I manually run an analyze on it – Example below
You are right. I looked at the code, and "vacuumdb" does not process
partitiond tables, even if --analyze-only is specified. I find that
surprising, aince the SQL command ANALYZE (without a table name) will
also collect statistics for partitioned tables.
I think that it would be a good idea to change that behavior.
In particular, it makes a lot of sense to collect statistics for
partitioned tables after a "pg_upgrade".
Attached is a patch to make "vacuumdb --analyze-only" consider
partitioned tables as well.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] v1-0001-Make-vacuumdb-Z-process-partitioned-tables.patch (3.0K, ../../f2ebeef82fddaf8a8152383f8ff62e75f20aac3c.camel@cybertec.at/2-v1-0001-Make-vacuumdb-Z-process-partitioned-tables.patch)
download | inline diff:
From 07cbd491011ff5da1243b117c111d51531293782 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Sat, 28 Jun 2025 07:20:02 +0200
Subject: [PATCH v1] 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.
---
doc/src/sgml/ref/vacuumdb.sgml | 4 ++++
src/bin/scripts/vacuumdb.c | 23 +++++++++++++++++++----
2 files changed, 23 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/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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-07 12:43 Mircea Cadariu <cadariu.mircea@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 33+ messages in thread
From: Mircea Cadariu @ 2025-07-07 12:43 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed
Hi Laurenz,
Nice focused patch.
Applied successfully on commit 62a17a92 from master.
Documentation is updated and there is an explanatory comment for the code, as well as a descriptive commit message.
To check the patch, I added the following test at the end of src/bin/scripts/t/100_vacuumdb.pl in both master (as experiment) and your patch. In master it does not pass, but with your patch applied it does.
You can consider adding it to your patch, or I could also do that.
$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');
Kind regards,
Mircea Cadariu
The new status of this patch is: Waiting on Author
^ permalink raw reply [nested|flat] 33+ messages in thread
* RE: analyze-in-stages post upgrade questions
@ 2025-07-09 11:30 Zechman, Derek S <Derek.S.Zechman@snapon.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 33+ messages in thread
From: Zechman, Derek S @ 2025-07-09 11:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> > Well, that wouldn't explain why it doesn't work on partitioned tables.
> > I am under the impression that it should.
> >
> > Derek, can cou share the pg_stats entries for the partitioned table?
>
> There are no entries in pg_stats for the parent table until after I manually run an analyze on it – Example below
You are right. I looked at the code, and "vacuumdb" does not process
partitiond tables, even if --analyze-only is specified. I find that
surprising, aince the SQL command ANALYZE (without a table name) will
also collect statistics for partitioned tables.
I think that it would be a good idea to change that behavior.
In particular, it makes a lot of sense to collect statistics for
partitioned tables after a "pg_upgrade".
Attached is a patch to make "vacuumdb --analyze-only" consider
partitioned tables as well.
Yours,
Laurenz Albe
Is there a plan to include this patch in future releases/patches of postgres?
Thanks,
(Derek) Sean
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-09 15:26 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Zechman, Derek S <Derek.S.Zechman@snapon.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-07-09 15:26 UTC (permalink / raw)
To: Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, 2025-07-09 at 11:30 +0000, Zechman, Derek S wrote:
> > > There are no entries in pg_stats for the parent table until after I manually run an analyze on it – Example below
> >
> > You are right. I looked at the code, and "vacuumdb" does not process
> > partitiond tables, even if --analyze-only is specified. I find that
> > surprising, aince the SQL command ANALYZE (without a table name) will
> > also collect statistics for partitioned tables.
> >
> > I think that it would be a good idea to change that behavior.
> > In particular, it makes a lot of sense to collect statistics for
> > partitioned tables after a "pg_upgrade".
> >
> > Attached is a patch to make "vacuumdb --analyze-only" consider
> > partitioned tables as well.
>
> Is there a plan to include this patch in future releases/patches of postgres?
I have added the patch to the current commitfest:
https://commitfest.postgresql.org/patch/5871/
So far, it has not got any peer review. So yes, I'd like to include
the patch, but I cannot make it happen by myself.
Essentially, patches get applied if
a) they get peer review and
b) a committer applies them
If you want this to happen, the best thing you could do would be
to review the patch and see if it works for you, if it does what you
need and so on:
https://wiki.postgresql.org/wiki/Reviewing_a_Patch
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-09 16:37 Mircea Cadariu <cadariu.mircea@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Mircea Cadariu @ 2025-07-09 16:37 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi Laurenz,
On 09/07/2025 16:26, Laurenz Albe wrote:
> I have added the patch to the current commitfest:
> https://commitfest.postgresql.org/patch/5871/
Just to let you know that I have added a review through the commitfest app.
You can see it here:
https://www.postgresql.org/message-id/flat/175189219162.2200286.3306593311375985296.pgcf@coridan.pos...
Kind regards,
Mircea Cadariu
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-09 17:22 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mircea Cadariu <cadariu.mircea@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-07-09 17:22 UTC (permalink / raw)
To: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, 2025-07-09 at 17:37 +0100, Mircea Cadariu wrote:
> Just to let you know that I have added a review through the commitfest app.
Thanks!
The patch is still in state "needs review".
If there is something that I should change, you should set it to
"waiting on author". If you think that the patch is ready to go
as it is, please set it to "ready for committer".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-10 16:20 Mircea Cadariu <cadariu.mircea@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Mircea Cadariu @ 2025-07-10 16:20 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi Laurenz,
Got it. 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');
Kind regards,
Mircea Cadariu
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-11 09:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mircea Cadariu <cadariu.mircea@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-07-11 09:51 UTC (permalink / raw)
To: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
[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
Attachments:
[text/x-patch] v2-0001-Make-vacuumdb-Z-process-partitioned-tables.patch (4.0K, ../../2c1f1834107045dfa8b32417771b56bab0cffc56.camel@cybertec.at/2-v2-0001-Make-vacuumdb-Z-process-partitioned-tables.patch)
download | inline diff:
From 4bf5048da6ffd250ee204fff419cf975aa7a6548 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@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.albe@cybertec.at>
Author: Mircea Cadariu <cadariu.mircea@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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-11 10:42 Mircea Cadariu <cadariu.mircea@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Mircea Cadariu @ 2025-07-11 10:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On 11/07/2025 10:51, Laurenz Albe wrote:
> Good idea; done in the attached version 2 of the patch.
Thanks! Looks good. I have set the status of the Commitfest entry to
"Ready for Committer".
Kind regards,
Mircea Cadariu
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-07-30 02:49 Fujii Masao <masao.fujii@gmail.com>
parent: Mircea Cadariu <cadariu.mircea@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2025-07-30 02:49 UTC (permalink / raw)
To: Mircea Cadariu <cadariu.mircea@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Fri, Jul 11, 2025 at 7:42 PM Mircea Cadariu <cadariu.mircea@gmail.com> wrote:
>
> On 11/07/2025 10:51, Laurenz Albe wrote:
>
> > Good idea; done in the attached version 2 of the patch.
>
> Thanks! Looks good. I have set the status of the Commitfest entry to
> "Ready for Committer".
I've started reviewing the patch since it's marked as ready for committer.
Overall, I like the change. But I have one question: should this be treated as
a bug fix that we back-patch to supported branches, or is it more of
an improvement that should only go into master?
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.
This wording seems a bit out of place in the --analyze-only section,
since it also describes the default behavior of vacuumdb without that option.
Wouldn't it make more sense to move that explanation in the --table section?
For example, we could add something like:
------------------
If no tables are specified with the --table option, vacuumdb will
clean all regular tables and materialized views in the connected
database. If --analyze-only or --analyze-in-stages is also specified,
it will analyze all regular tables, partitioned tables, and
materialized views (but not foreign tables).
------------------
+ /*
+ * 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.
+ */
This is probably true. But isn't the main reason more about aligning with
the behavior of the underlying VACUUM and ANALYZE commands? As the vacuumdb
docs says, "There is no effective difference between vacuuming and analyzing
databases via this utility and via other methods for accessing the server.",
so its default target objects should match: VACUUM skips partitioned tables
by default, while ANALYZE includes them. If that's the case, maybe the comment
should reflect that instead.
+ qr/statement:\s+ANALYZE\s+public\.parent_table/s,
+ '--analyze_only updates statistics for partitioned tables');
A plain space might be sufficient instead of \s+.
Also, I don't think the backslash before ".parent_table" is necessary.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-06 04:01 Mircea Cadariu <cadariu.mircea@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Mircea Cadariu @ 2025-08-06 04:01 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
Hi,
On 30/07/2025 12:49, Fujii Masao wrote:
> I've started reviewing the patch since it's marked as ready for committer.
Thanks!
> Overall, I like the change. But I have one question: should this be treated as
> a bug fix that we back-patch to supported branches, or is it more of
> an improvement that should only go into master?
I reckon it might make sense to back-patch it to previous versions, as
users might not upgrade always to the latest version.
> 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.
>
> This wording seems a bit out of place in the --analyze-only section,
> since it also describes the default behavior of vacuumdb without that option.
> Wouldn't it make more sense to move that explanation in the --table section?
>
> For example, we could add something like:
>
> ------------------
> If no tables are specified with the --table option, vacuumdb will
> clean all regular tables and materialized views in the connected
> database. If --analyze-only or --analyze-in-stages is also specified,
> it will analyze all regular tables, partitioned tables, and
> materialized views (but not foreign tables).
> ------------------
Yes, agreed.
> + /*
> + * 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.
> + */
>
> This is probably true. But isn't the main reason more about aligning with
> the behavior of the underlying VACUUM and ANALYZE commands? As the vacuumdb
> docs says, "There is no effective difference between vacuuming and analyzing
> databases via this utility and via other methods for accessing the server.",
> so its default target objects should match: VACUUM skips partitioned tables
> by default, while ANALYZE includes them. If that's the case, maybe the comment
> should reflect that instead.
I see what you mean. From that perspective, I wonder if we even need a
comment there at all.
> + qr/statement:\s+ANALYZE\s+public\.parent_table/s,
> + '--analyze_only updates statistics for partitioned tables');
>
> A plain space might be sufficient instead of \s+.
> Also, I don't think the backslash before ".parent_table" is necessary.
Good catch! Indeed let's simplify that to contain strictly only what's
necessary.
Kind regards,
Mircea Cadariu
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-06 14:25 Fujii Masao <masao.fujii@gmail.com>
parent: Mircea Cadariu <cadariu.mircea@gmail.com>
0 siblings, 2 replies; 33+ messages in thread
From: Fujii Masao @ 2025-08-06 14:25 UTC (permalink / raw)
To: Mircea Cadariu <cadariu.mircea@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Wed, Aug 6, 2025 at 1:01 PM Mircea Cadariu <cadariu.mircea@gmail.com> wrote:
> Overall, I like the change. But I have one question: should this be treated as
> a bug fix that we back-patch to supported branches, or is it more of
> an improvement that should only go into master?
>
> I reckon it might make sense to back-patch it to previous versions, as users might not upgrade always to the latest version.
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...
> + /*
> + * 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.
> + */
>
> This is probably true. But isn't the main reason more about aligning with
> the behavior of the underlying VACUUM and ANALYZE commands? As the vacuumdb
> docs says, "There is no effective difference between vacuuming and analyzing
> databases via this utility and via other methods for accessing the server.",
> so its default target objects should match: VACUUM skips partitioned tables
> by default, while ANALYZE includes them. If that's the case, maybe the comment
> should reflect that instead.
>
> I see what you mean. From that perspective, I wonder if we even need a comment there at all.
Or, if we keep it, though, I'd like to update it to something like
the following:
--------------------
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.
--------------------
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-06 18:14 Nathan Bossart <nathandbossart@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
1 sibling, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2025-08-06 18:14 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Wed, Aug 06, 2025 at 11:25:53PM +0900, Fujii Masao wrote:
> On Wed, Aug 6, 2025 at 1:01 PM Mircea Cadariu <cadariu.mircea@gmail.com> wrote:
>> Overall, I like the change. But I have one question: should this be treated as
>> a bug fix that we back-patch to supported branches, or is it more of
>> an improvement that should only go into master?
>>
>> I reckon it might make sense to back-patch it to previous versions, as users might not upgrade always to the latest version.
>
> 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 also am leaning towards treating this as v19 material. It's a nontrivial
behavior change, and this option is useful for major version upgrades,
which is an area that we really don't want to surprise users too much.
Furthermore, auto-analyze doesn't process partitioned tables, either, so
this introduces a bit of divergence. (I'd love to see that project picked
up again someday. Perhaps I will take a gander...)
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-06 20:52 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Fujii Masao <masao.fujii@gmail.com>
1 sibling, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-08-06 20:52 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; Mircea Cadariu <cadariu.mircea@gmail.com>; +Cc: Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Wed, 2025-08-06 at 23:25 +0900, Fujii Masao wrote:
> On Wed, Aug 6, 2025 at 1:01 PM Mircea Cadariu <cadariu.mircea@gmail.com> wrote:
> > Overall, I like the change. But I have one question: should this be treated as
> > a bug fix that we back-patch to supported branches, or is it more of
> > an improvement that should only go into master?
> >
> > I reckon it might make sense to back-patch it to previous versions, as users might not upgrade always to the latest version.
>
> 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.
> > + /*
> > + * 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.
> > + */
> >
> > This is probably true. But isn't the main reason more about aligning with
> > the behavior of the underlying VACUUM and ANALYZE commands? As the vacuumdb
> > docs says, "There is no effective difference between vacuuming and analyzing
> > databases via this utility and via other methods for accessing the server.",
> > so its default target objects should match: VACUUM skips partitioned tables
> > by default, while ANALYZE includes them. If that's the case, maybe the comment
> > should reflect that instead.
> >
> > I see what you mean. From that perspective, I wonder if we even need a comment there at all.
>
> Or, if we keep it, though, I'd like to update it to something like
> the following:
>
> --------------------
> 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.
Alternatively, my original comment could be amended with
Besides, ANALYZE (without an option) processes partitioned tables, and
"vacuumdb -Z" should behave like ANALYZE.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-18 02:36 Fujii Masao <masao.fujii@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 33+ messages in thread
From: Fujii Masao @ 2025-08-18 02:36 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Thu, Aug 7, 2025 at 3:14 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
> I also am leaning towards treating this as v19 material. It's a nontrivial
> behavior change, and this option is useful for major version upgrades,
> which is an area that we really don't want to surprise users too much.
+1
> Furthermore, auto-analyze doesn't process partitioned tables, either, so
> this introduces a bit of divergence. (I'd love to see that project picked
> up again someday. Perhaps I will take a gander...)
Sounds good!
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-18 02:38 Fujii Masao <masao.fujii@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2025-08-18 02:38 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Thu, Aug 7, 2025 at 5:52 AM Laurenz Albe <laurenz.albe@cybertec.at> 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, ../../CAHGQGwGje3mv5sOmyCJ0NroNPF8938OYCOHMJZbegTekccZZ=g@mail.gmail.com/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 <fujii@postgresql.org>
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 <Derek.S.Zechman@snapon.com>
Author: Laurenz Albe <laurenz.albe@cybertec.at>
Co-authored-by: Mircea Cadariu <cadariu.mircea@gmail.com>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
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
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-18 06:40 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-08-18 06:40 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Mon, 2025-08-18 at 11:38 +0900, Fujii Masao wrote:
> 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.
I am fine with your patch.
One suggestion:
> --- 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>
I suggest replacing "clean" with "process", since VACUUM does so much more than
clean up dead tuples.
Concerning backpatching, I voted against, but I suggest that this be backpatched
to v18. I don't feel very strongly about it though.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-19 14:40 Fujii Masao <masao.fujii@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2025-08-19 14:40 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Mon, Aug 18, 2025 at 3:40 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Mon, 2025-08-18 at 11:38 +0900, Fujii Masao wrote:
> > 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.
>
> I am fine with your patch.
Thanks for the review!
> One suggestion:
>
> > --- 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>
>
> I suggest replacing "clean" with "process", since VACUUM does so much more than
> clean up dead tuples.
I see your point. However, since the vacuumdb docs already use "clean"
in several places, I think it's better to keep using "clean" here
for consistency. Thought?
> Concerning backpatching, I voted against, but I suggest that this be backpatched
> to v18. I don't feel very strongly about it though.
As for back-patching, I failed to find a strong reason to apply this change
to v18 over the many other patches that could not be committed before
the feature freeze... Of course if there's broad support for back-patching,
we can certainly revisit it. But for now I'm thinking to commit the patch
to master.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-19 15:16 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Laurenz Albe @ 2025-08-19 15:16 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Tue, 2025-08-19 at 23:40 +0900, Fujii Masao wrote:
> > > --- 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>
> >
> > I suggest replacing "clean" with "process", since VACUUM does so much more than
> > clean up dead tuples.
>
> I see your point. However, since the vacuumdb docs already use "clean"
> in several places, I think it's better to keep using "clean" here
> for consistency. Thought?
Works for me; I didn't consider that.
> > Concerning backpatching, I voted against, but I suggest that this be backpatched
> > to v18. I don't feel very strongly about it though.
>
> As for back-patching, I failed to find a strong reason to apply this change
> to v18 over the many other patches that could not be committed before
> the feature freeze... Of course if there's broad support for back-patching,
> we can certainly revisit it. But for now I'm thinking to commit the patch
> to master.
I don't have a strong reason either - my reasoning was that the change is small
and unlikely to introduce a bug, and that it would be nice to get more accurate
statistics on partitioned tables after "pg_upgrade" a year earlier.
But I won't object if the patch is only in v19.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-20 04:31 Fujii Masao <masao.fujii@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 33+ messages in thread
From: Fujii Masao @ 2025-08-20 04:31 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Wed, Aug 20, 2025 at 12:16 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Tue, 2025-08-19 at 23:40 +0900, Fujii Masao wrote:
> > > > --- 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>
> > >
> > > I suggest replacing "clean" with "process", since VACUUM does so much more than
> > > clean up dead tuples.
> >
> > I see your point. However, since the vacuumdb docs already use "clean"
> > in several places, I think it's better to keep using "clean" here
> > for consistency. Thought?
>
> Works for me; I didn't consider that.
>
> > > Concerning backpatching, I voted against, but I suggest that this be backpatched
> > > to v18. I don't feel very strongly about it though.
> >
> > As for back-patching, I failed to find a strong reason to apply this change
> > to v18 over the many other patches that could not be committed before
> > the feature freeze... Of course if there's broad support for back-patching,
> > we can certainly revisit it. But for now I'm thinking to commit the patch
> > to master.
>
> I don't have a strong reason either - my reasoning was that the change is small
> and unlikely to introduce a bug, and that it would be nice to get more accurate
> statistics on partitioned tables after "pg_upgrade" a year earlier.
>
> But I won't object if the patch is only in v19.
OK, so for now I've pushed the patch to master. Thanks!
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-20 07:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Fujii Masao <masao.fujii@gmail.com>
1 sibling, 0 replies; 33+ messages in thread
From: Laurenz Albe @ 2025-08-20 07:14 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Wed, 2025-08-20 at 13:31 +0900, Fujii Masao wrote:
> OK, so for now I've pushed the patch to master. Thanks!
Thank you for working on that!
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2025-08-21 01:51 Justin Pryzby <pryzby@telsasoft.com>
parent: Fujii Masao <masao.fujii@gmail.com>
1 sibling, 1 reply; 33+ messages in thread
From: Justin Pryzby @ 2025-08-21 01:51 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
When analyzing a partitioned table, I think you should use ANALYZE ONLY,
or otherwise avoid processing the children twice.
Thanks for handling this. I was recently suprised to learn that
vacuumdb doesn't process parents.
--
Justin
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2026-06-04 17:14 Justin Pryzby <pryzby@telsasoft.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 33+ messages in thread
From: Justin Pryzby @ 2026-06-04 17:14 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
Checking back.
On Wed, Aug 20, 2025 at 08:51:16PM -0500, Justin Pryzby wrote:
> When analyzing a partitioned table, I think you should use ANALYZE ONLY,
> or otherwise avoid processing the children twice.
>
> Thanks for handling this. I was recently suprised to learn that
> vacuumdb doesn't process parents.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2026-07-15 15:42 Nathan Bossart <nathandbossart@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 33+ messages in thread
From: Nathan Bossart @ 2026-07-15 15:42 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
[RMT hat]
On Thu, Jun 04, 2026 at 12:14:34PM -0500, Justin Pryzby wrote:
> Checking back.
>
> On Wed, Aug 20, 2025 at 08:51:16PM -0500, Justin Pryzby wrote:
>> When analyzing a partitioned table, I think you should use ANALYZE ONLY,
>> or otherwise avoid processing the children twice.
This is listed as an open item for v19 [0]. Is it appropriate to consider
this v19 stabilization work?
[0] https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
--
nathan
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2026-07-16 02:53 Fujii Masao <masao.fujii@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Fujii Masao @ 2026-07-16 02:53 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Thu, Jul 16, 2026 at 12:42 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:
>
> [RMT hat]
>
> On Thu, Jun 04, 2026 at 12:14:34PM -0500, Justin Pryzby wrote:
> > Checking back.
> >
> > On Wed, Aug 20, 2025 at 08:51:16PM -0500, Justin Pryzby wrote:
> >> When analyzing a partitioned table, I think you should use ANALYZE ONLY,
> >> or otherwise avoid processing the children twice.
>
> This is listed as an open item for v19 [0].
Thanks for pointing this out!
> Is it appropriate to consider
> this v19 stabilization work?
Yes, probably. The attached patch updates vacuumdb to use ANALYZE ONLY
for partitioned tables when --analyze-only or --analyze-in-stages is
specified, as suggested.
Regards,
--
Fujii Masao
Attachments:
[application/octet-stream] v1-0001-vacuumdb-Use-ANALYZE-ONLY-for-partitioned-tables.patch (12.7K, ../../CAHGQGwHLMxQbsKK4JKM1pPopSgXixNkrQex8siJstDSo574bbA@mail.gmail.com/2-v1-0001-vacuumdb-Use-ANALYZE-ONLY-for-partitioned-tables.patch)
download | inline diff:
From 095da1cdc53b4a2cc6cd2c766c1acf0f5cc9f43a Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 16 Jul 2026 11:47:05 +0900
Subject: [PATCH v1] vacuumdb: Use ANALYZE ONLY for partitioned tables
When vacuumdb --analyze-only or --analyze-in-stages enumerates
tables itself, it can select both a partitioned table and its
partitions as separate work items. Previously, it generated plain
ANALYZE for the partitioned table, which recursively analyzed its
partitions as well.
This caused duplicate work when the partitions were already selected by
vacuumdb. With --analyze-in-stages, the redundant work could be
repeated at every stage.
Fix this by generating ANALYZE ONLY for automatically selected
partitioned tables. This updates inherited statistics for the
partitioned table while leaving per-partition statistics to the
separately selected partition entries.
This commit does not change the behavior for partitioned tables
specified explicitly with --table, i.e., in that case, vacuumdb continues
to generate plain ANALYZE, recursively analyzing the specified
partitioned table and its partitions.
Suggested-by: Justin Pryzby <pryzby@telsasoft.com>
Author: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/aKZ7lMYGnnIo35c0@pryzbyj2023
Backpatch-through: 19
---
src/bin/scripts/t/100_vacuumdb.pl | 8 +-
src/bin/scripts/vacuuming.c | 119 ++++++++++++++++++++----------
src/tools/pgindent/typedefs.list | 2 +
3 files changed, 87 insertions(+), 42 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 58e38971b3d..7c4e35a6717 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -362,12 +362,16 @@ $node->safe_psql('postgres',
. "INSERT INTO parent_table VALUES (1);\n");
$node->issues_sql_like(
[ 'vacuumdb', '--analyze-only', 'postgres' ],
- qr/statement: ANALYZE public.parent_table/s,
+ qr/statement: ANALYZE ONLY public.parent_table/s,
'--analyze-only updates statistics for partitioned tables');
$node->issues_sql_like(
[ 'vacuumdb', '--analyze-in-stages', 'postgres' ],
- qr/statement: ANALYZE public.parent_table/s,
+ qr/statement: ANALYZE ONLY public.parent_table/s,
'--analyze-in-stages updates statistics for partitioned tables');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--analyze-only', '-t', 'parent_table', 'postgres' ],
+ qr/statement: ANALYZE public.parent_table/s,
+ '--analyze-only with --table keeps normal ANALYZE recursion');
$node->issues_sql_unlike(
[ 'vacuumdb', '--analyze-only', 'postgres' ],
qr/statement:\ VACUUM/sx,
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 855a5754c98..f34ed37ef4f 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -23,12 +23,23 @@
#include "fe_utils/string_utils.h"
#include "vacuuming.h"
+typedef struct RetrievedObject
+{
+ char *target;
+ bool use_only;
+} RetrievedObject;
+
+typedef struct RetrievedObjects
+{
+ int num_objects;
+ RetrievedObject objects[FLEXIBLE_ARRAY_MEMBER];
+} RetrievedObjects;
static int vacuum_one_database(ConnParams *cparams,
vacuumingOptions *vacopts,
int stage,
SimpleStringList *objects,
- SimpleStringList **found_objs,
+ RetrievedObjects **found_objs,
int concurrentCons,
const char *progname);
static int vacuum_all_databases(ConnParams *cparams,
@@ -36,12 +47,13 @@ static int vacuum_all_databases(ConnParams *cparams,
SimpleStringList *objects,
int concurrentCons,
const char *progname);
-static SimpleStringList *retrieve_objects(PGconn *conn,
+static RetrievedObjects *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
SimpleStringList *objects);
-static void free_retrieved_objects(SimpleStringList *list);
+static void free_retrieved_objects(RetrievedObjects *objs);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
- vacuumingOptions *vacopts, const char *table);
+ vacuumingOptions *vacopts, const char *table,
+ bool use_only);
static void run_vacuum_command(ParallelSlot *free_slot,
vacuumingOptions *vacopts, const char *sql,
const char *table);
@@ -89,7 +101,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
- SimpleStringList *found_objs = NULL;
+ RetrievedObjects *found_objs = NULL;
for (int stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
{
@@ -127,9 +139,9 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
*
* There are two ways to specify the list of objects to process:
*
- * 1) The "found_objs" parameter is a double pointer to a fully qualified list
- * of objects to process, as returned by a previous call to
- * vacuum_one_database().
+ * 1) The "found_objs" parameter is a double pointer to a list of fully
+ * qualified objects and their command generation metadata, as returned by
+ * a previous call to vacuum_one_database().
*
* a) If both "found_objs" (the double pointer) and "*found_objs" (the
* once-dereferenced double pointer) are not NULL, this list takes
@@ -165,17 +177,16 @@ vacuum_one_database(ConnParams *cparams,
vacuumingOptions *vacopts,
int stage,
SimpleStringList *objects,
- SimpleStringList **found_objs,
+ RetrievedObjects **found_objs,
int concurrentCons,
const char *progname)
{
PQExpBufferData sql;
PGconn *conn;
- SimpleStringListCell *cell;
ParallelSlotArray *sa;
int ntups = 0;
const char *initcmd;
- SimpleStringList *retobjs = NULL;
+ RetrievedObjects *retobjs = NULL;
bool free_retobjs = false;
int ret = EXIT_SUCCESS;
const char *stage_commands[] = {
@@ -295,7 +306,7 @@ vacuum_one_database(ConnParams *cparams,
/*
* If the caller provided the results of a previous catalog query, just
* use that. Otherwise, run the catalog query ourselves and set the
- * return variable if provided. (If it is, then freeing the string list
+ * return variable if provided. (If it is, then freeing the results
* becomes the caller's responsibility.)
*/
if (found_objs && *found_objs)
@@ -309,12 +320,7 @@ vacuum_one_database(ConnParams *cparams,
free_retobjs = true;
}
- /*
- * Count the number of objects in the catalog query result. If there are
- * none, we are done.
- */
- for (cell = retobjs->head; cell; cell = cell->next)
- ntups++;
+ ntups = retobjs->num_objects;
if (ntups == 0)
{
@@ -361,10 +367,10 @@ vacuum_one_database(ConnParams *cparams,
initPQExpBuffer(&sql);
- cell = retobjs->head;
- do
+ for (int i = 0; i < ntups; i++)
{
- const char *tabname = cell->val;
+ RetrievedObject *object = &retobjs->objects[i];
+ const char *tabname = object->target;
ParallelSlot *free_slot;
if (CancelRequested)
@@ -381,7 +387,7 @@ vacuum_one_database(ConnParams *cparams,
}
prepare_vacuum_command(free_slot->connection, &sql,
- vacopts, tabname);
+ vacopts, tabname, object->use_only);
/*
* Execute the vacuum. All errors are handled in processQueryResult
@@ -390,8 +396,7 @@ vacuum_one_database(ConnParams *cparams,
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
run_vacuum_command(free_slot, vacopts, sql.data, tabname);
- cell = cell->next;
- } while (cell != NULL);
+ }
if (!ParallelSlotsWaitCompletion(sa))
{
@@ -456,10 +461,10 @@ vacuum_all_databases(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
- SimpleStringList **found_objs = NULL;
+ RetrievedObjects **found_objs = NULL;
if (vacopts->missing_stats_only)
- found_objs = palloc0(numdbs * sizeof(SimpleStringList *));
+ found_objs = palloc0(numdbs * sizeof(RetrievedObjects *));
/*
* When analyzing all databases in stages, we analyze them all in the
@@ -526,7 +531,7 @@ vacuum_all_databases(ConnParams *cparams,
* generated qualified identifiers and to filter for the tables provided via
* --table. If a listed table does not exist, the catalog query will fail.
*/
-static SimpleStringList *
+static RetrievedObjects *
retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
SimpleStringList *objects)
{
@@ -534,8 +539,9 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
PQExpBufferData catalog_query;
PGresult *res;
SimpleStringListCell *cell;
- SimpleStringList *found_objs = palloc0_object(SimpleStringList);
+ RetrievedObjects *found_objs;
bool objects_listed = false;
+ int ntups;
initPQExpBuffer(&catalog_query);
for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
@@ -588,7 +594,7 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
if (objects_listed)
appendPQExpBufferStr(&catalog_query, "\n)\n");
- appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
+ appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname, c.relkind");
if (objects_listed)
appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
@@ -791,18 +797,39 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* Build qualified identifiers for each table, including the column list
* if given.
*/
+ ntups = PQntuples(res);
+ found_objs = palloc0(add_size(offsetof(RetrievedObjects, objects),
+ mul_size(sizeof(RetrievedObject), ntups)));
+ found_objs->num_objects = ntups;
+
initPQExpBuffer(&buf);
- for (int i = 0; i < PQntuples(res); i++)
+ for (int i = 0; i < found_objs->num_objects; i++)
{
+ RetrievedObject *object;
+ bool use_only;
+
+ /*
+ * For automatically enumerated partitioned tables, use ONLY to
+ * collect inherited statistics without recursively updating
+ * per-partition statistics. Partitions selected by the current
+ * filters are processed as separate targets.
+ */
+ use_only = ((vacopts->mode == MODE_ANALYZE ||
+ vacopts->mode == MODE_ANALYZE_IN_STAGES) &&
+ (vacopts->objfilter & OBJFILTER_TABLE) == 0 &&
+ PQgetvalue(res, i, 2)[0] == RELKIND_PARTITIONED_TABLE);
+
appendPQExpBufferStr(&buf,
fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
PQgetvalue(res, i, 0),
PQclientEncoding(conn)));
- if (objects_listed && !PQgetisnull(res, i, 2))
- appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
+ if (objects_listed && !PQgetisnull(res, i, 3))
+ appendPQExpBufferStr(&buf, PQgetvalue(res, i, 3));
- simple_string_list_append(found_objs, buf.data);
+ object = &found_objs->objects[i];
+ object->target = pg_strdup(buf.data);
+ object->use_only = use_only;
resetPQExpBuffer(&buf);
}
termPQExpBuffer(&buf);
@@ -818,12 +845,14 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* although retrieve_objects() will never return that.
*/
static void
-free_retrieved_objects(SimpleStringList *list)
+free_retrieved_objects(RetrievedObjects *objs)
{
- if (list)
+ if (objs)
{
- simple_string_list_destroy(list);
- pg_free(list);
+ for (int i = 0; i < objs->num_objects; i++)
+ pg_free(objs->objects[i].target);
+
+ pg_free(objs);
}
}
@@ -831,18 +860,25 @@ free_retrieved_objects(SimpleStringList *list)
* Construct a vacuum/analyze command to run based on the given
* options, in the given string buffer, which may contain previous garbage.
*
- * The table name used must be already properly quoted. The command generated
- * depends on the server version involved and it is semicolon-terminated.
+ * The table reference used must be already properly quoted. It is usually a
+ * table name, but may also include a column list. The command generated
+ * depends on the server version involved and it is semicolon-terminated. If
+ * use_only is set, the command targets the table with ANALYZE ONLY.
*/
static void
prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
- vacuumingOptions *vacopts, const char *table)
+ vacuumingOptions *vacopts, const char *table,
+ bool use_only)
{
int serverVersion = PQserverVersion(conn);
const char *paren = " (";
const char *comma = ", ";
const char *sep = paren;
+ Assert(!use_only ||
+ vacopts->mode == MODE_ANALYZE ||
+ vacopts->mode == MODE_ANALYZE_IN_STAGES);
+
resetPQExpBuffer(sql);
if (vacopts->mode == MODE_ANALYZE ||
@@ -880,6 +916,9 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
if (vacopts->verbose)
appendPQExpBufferStr(sql, " VERBOSE");
}
+
+ if (use_only)
+ appendPQExpBufferStr(sql, " ONLY");
}
else
{
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 56c1f997f88..801e692d008 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2702,6 +2702,8 @@ ResultState
ResultType
RetainDeadTuplesData
RetainDeadTuplesPhase
+RetrievedObject
+RetrievedObjects
ReturnSetInfo
ReturnStmt
ReturningClause
--
2.55.0
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2026-08-06 14:30 Daniel Gustafsson <daniel@yesql.se>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: Daniel Gustafsson @ 2026-08-06 14:30 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; Justin Pryzby <pryzby@telsasoft.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
> On 16 Jul 2026, at 04:53, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Thu, Jul 16, 2026 at 12:42 AM Nathan Bossart
> <nathandbossart@gmail.com> wrote:
>>
>> [RMT hat]
>>
>> On Thu, Jun 04, 2026 at 12:14:34PM -0500, Justin Pryzby wrote:
>>> Checking back.
>>>
>>> On Wed, Aug 20, 2025 at 08:51:16PM -0500, Justin Pryzby wrote:
>>>> When analyzing a partitioned table, I think you should use ANALYZE ONLY,
>>>> or otherwise avoid processing the children twice.
>>
>> This is listed as an open item for v19 [0].
>
> Thanks for pointing this out!
>
>> Is it appropriate to consider
>> this v19 stabilization work?
>
> Yes, probably. The attached patch updates vacuumdb to use ANALYZE ONLY
> for partitioned tables when --analyze-only or --analyze-in-stages is
> specified, as suggested.
I had a look at this open item today and the proposal and patch looks good to
me. Just one small comment: don't we need a check for the applicable server
version (as per the attached v2) before issuing ANALYZE ONLY since it's not
supported in all versions?
--
Daniel Gustafsson
Attachments:
[application/octet-stream] v2-0001-vacuumdb-Use-ANALYZE-ONLY-for-partitioned-tables.patch (12.8K, ../../87BC0949-448C-4B84-8B47-7D044F87B9C9@yesql.se/2-v2-0001-vacuumdb-Use-ANALYZE-ONLY-for-partitioned-tables.patch)
download | inline diff:
From 6e1e44bf98ab4b2f397a52cecc5ae990cb4ac9ab Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 16 Jul 2026 11:47:05 +0900
Subject: [PATCH v2] vacuumdb: Use ANALYZE ONLY for partitioned tables
When vacuumdb --analyze-only or --analyze-in-stages enumerates
tables itself, it can select both a partitioned table and its
partitions as separate work items. Previously, it generated plain
ANALYZE for the partitioned table, which recursively analyzed its
partitions as well.
This caused duplicate work when the partitions were already selected by
vacuumdb. With --analyze-in-stages, the redundant work could be
repeated at every stage.
Fix this by generating ANALYZE ONLY for automatically selected
partitioned tables. This updates inherited statistics for the
partitioned table while leaving per-partition statistics to the
separately selected partition entries.
This commit does not change the behavior for partitioned tables
specified explicitly with --table, i.e., in that case, vacuumdb continues
to generate plain ANALYZE, recursively analyzing the specified
partitioned table and its partitions.
Suggested-by: Justin Pryzby <pryzby@telsasoft.com>
Author: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/aKZ7lMYGnnIo35c0@pryzbyj2023
Backpatch-through: 19
---
src/bin/scripts/t/100_vacuumdb.pl | 8 +-
src/bin/scripts/vacuuming.c | 123 ++++++++++++++++++++----------
src/tools/pgindent/typedefs.list | 2 +
3 files changed, 91 insertions(+), 42 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 58e38971b3d..7c4e35a6717 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -362,12 +362,16 @@ $node->safe_psql('postgres',
. "INSERT INTO parent_table VALUES (1);\n");
$node->issues_sql_like(
[ 'vacuumdb', '--analyze-only', 'postgres' ],
- qr/statement: ANALYZE public.parent_table/s,
+ qr/statement: ANALYZE ONLY public.parent_table/s,
'--analyze-only updates statistics for partitioned tables');
$node->issues_sql_like(
[ 'vacuumdb', '--analyze-in-stages', 'postgres' ],
- qr/statement: ANALYZE public.parent_table/s,
+ qr/statement: ANALYZE ONLY public.parent_table/s,
'--analyze-in-stages updates statistics for partitioned tables');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--analyze-only', '-t', 'parent_table', 'postgres' ],
+ qr/statement: ANALYZE public.parent_table/s,
+ '--analyze-only with --table keeps normal ANALYZE recursion');
$node->issues_sql_unlike(
[ 'vacuumdb', '--analyze-only', 'postgres' ],
qr/statement:\ VACUUM/sx,
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 855a5754c98..eb25452135e 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -23,12 +23,23 @@
#include "fe_utils/string_utils.h"
#include "vacuuming.h"
+typedef struct RetrievedObject
+{
+ char *target;
+ bool use_only;
+} RetrievedObject;
+
+typedef struct RetrievedObjects
+{
+ int num_objects;
+ RetrievedObject objects[FLEXIBLE_ARRAY_MEMBER];
+} RetrievedObjects;
static int vacuum_one_database(ConnParams *cparams,
vacuumingOptions *vacopts,
int stage,
SimpleStringList *objects,
- SimpleStringList **found_objs,
+ RetrievedObjects **found_objs,
int concurrentCons,
const char *progname);
static int vacuum_all_databases(ConnParams *cparams,
@@ -36,12 +47,13 @@ static int vacuum_all_databases(ConnParams *cparams,
SimpleStringList *objects,
int concurrentCons,
const char *progname);
-static SimpleStringList *retrieve_objects(PGconn *conn,
+static RetrievedObjects *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
SimpleStringList *objects);
-static void free_retrieved_objects(SimpleStringList *list);
+static void free_retrieved_objects(RetrievedObjects *objs);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
- vacuumingOptions *vacopts, const char *table);
+ vacuumingOptions *vacopts, const char *table,
+ bool use_only);
static void run_vacuum_command(ParallelSlot *free_slot,
vacuumingOptions *vacopts, const char *sql,
const char *table);
@@ -89,7 +101,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
- SimpleStringList *found_objs = NULL;
+ RetrievedObjects *found_objs = NULL;
for (int stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
{
@@ -127,9 +139,9 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
*
* There are two ways to specify the list of objects to process:
*
- * 1) The "found_objs" parameter is a double pointer to a fully qualified list
- * of objects to process, as returned by a previous call to
- * vacuum_one_database().
+ * 1) The "found_objs" parameter is a double pointer to a list of fully
+ * qualified objects and their command generation metadata, as returned by
+ * a previous call to vacuum_one_database().
*
* a) If both "found_objs" (the double pointer) and "*found_objs" (the
* once-dereferenced double pointer) are not NULL, this list takes
@@ -165,17 +177,16 @@ vacuum_one_database(ConnParams *cparams,
vacuumingOptions *vacopts,
int stage,
SimpleStringList *objects,
- SimpleStringList **found_objs,
+ RetrievedObjects **found_objs,
int concurrentCons,
const char *progname)
{
PQExpBufferData sql;
PGconn *conn;
- SimpleStringListCell *cell;
ParallelSlotArray *sa;
int ntups = 0;
const char *initcmd;
- SimpleStringList *retobjs = NULL;
+ RetrievedObjects *retobjs = NULL;
bool free_retobjs = false;
int ret = EXIT_SUCCESS;
const char *stage_commands[] = {
@@ -295,7 +306,7 @@ vacuum_one_database(ConnParams *cparams,
/*
* If the caller provided the results of a previous catalog query, just
* use that. Otherwise, run the catalog query ourselves and set the
- * return variable if provided. (If it is, then freeing the string list
+ * return variable if provided. (If it is, then freeing the results
* becomes the caller's responsibility.)
*/
if (found_objs && *found_objs)
@@ -309,12 +320,7 @@ vacuum_one_database(ConnParams *cparams,
free_retobjs = true;
}
- /*
- * Count the number of objects in the catalog query result. If there are
- * none, we are done.
- */
- for (cell = retobjs->head; cell; cell = cell->next)
- ntups++;
+ ntups = retobjs->num_objects;
if (ntups == 0)
{
@@ -361,10 +367,10 @@ vacuum_one_database(ConnParams *cparams,
initPQExpBuffer(&sql);
- cell = retobjs->head;
- do
+ for (int i = 0; i < ntups; i++)
{
- const char *tabname = cell->val;
+ RetrievedObject *object = &retobjs->objects[i];
+ const char *tabname = object->target;
ParallelSlot *free_slot;
if (CancelRequested)
@@ -381,7 +387,7 @@ vacuum_one_database(ConnParams *cparams,
}
prepare_vacuum_command(free_slot->connection, &sql,
- vacopts, tabname);
+ vacopts, tabname, object->use_only);
/*
* Execute the vacuum. All errors are handled in processQueryResult
@@ -390,8 +396,7 @@ vacuum_one_database(ConnParams *cparams,
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
run_vacuum_command(free_slot, vacopts, sql.data, tabname);
- cell = cell->next;
- } while (cell != NULL);
+ }
if (!ParallelSlotsWaitCompletion(sa))
{
@@ -456,10 +461,10 @@ vacuum_all_databases(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
- SimpleStringList **found_objs = NULL;
+ RetrievedObjects **found_objs = NULL;
if (vacopts->missing_stats_only)
- found_objs = palloc0(numdbs * sizeof(SimpleStringList *));
+ found_objs = palloc0(numdbs * sizeof(RetrievedObjects *));
/*
* When analyzing all databases in stages, we analyze them all in the
@@ -526,7 +531,7 @@ vacuum_all_databases(ConnParams *cparams,
* generated qualified identifiers and to filter for the tables provided via
* --table. If a listed table does not exist, the catalog query will fail.
*/
-static SimpleStringList *
+static RetrievedObjects *
retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
SimpleStringList *objects)
{
@@ -534,8 +539,9 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
PQExpBufferData catalog_query;
PGresult *res;
SimpleStringListCell *cell;
- SimpleStringList *found_objs = palloc0_object(SimpleStringList);
+ RetrievedObjects *found_objs;
bool objects_listed = false;
+ int ntups;
initPQExpBuffer(&catalog_query);
for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
@@ -588,7 +594,7 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
if (objects_listed)
appendPQExpBufferStr(&catalog_query, "\n)\n");
- appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
+ appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname, c.relkind");
if (objects_listed)
appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
@@ -791,18 +797,39 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* Build qualified identifiers for each table, including the column list
* if given.
*/
+ ntups = PQntuples(res);
+ found_objs = palloc0(add_size(offsetof(RetrievedObjects, objects),
+ mul_size(sizeof(RetrievedObject), ntups)));
+ found_objs->num_objects = ntups;
+
initPQExpBuffer(&buf);
- for (int i = 0; i < PQntuples(res); i++)
+ for (int i = 0; i < found_objs->num_objects; i++)
{
+ RetrievedObject *object;
+ bool use_only;
+
+ /*
+ * For automatically enumerated partitioned tables, use ONLY to
+ * collect inherited statistics without recursively updating
+ * per-partition statistics. Partitions selected by the current
+ * filters are processed as separate targets.
+ */
+ use_only = ((vacopts->mode == MODE_ANALYZE ||
+ vacopts->mode == MODE_ANALYZE_IN_STAGES) &&
+ (vacopts->objfilter & OBJFILTER_TABLE) == 0 &&
+ PQgetvalue(res, i, 2)[0] == RELKIND_PARTITIONED_TABLE);
+
appendPQExpBufferStr(&buf,
fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
PQgetvalue(res, i, 0),
PQclientEncoding(conn)));
- if (objects_listed && !PQgetisnull(res, i, 2))
- appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
+ if (objects_listed && !PQgetisnull(res, i, 3))
+ appendPQExpBufferStr(&buf, PQgetvalue(res, i, 3));
- simple_string_list_append(found_objs, buf.data);
+ object = &found_objs->objects[i];
+ object->target = pg_strdup(buf.data);
+ object->use_only = use_only;
resetPQExpBuffer(&buf);
}
termPQExpBuffer(&buf);
@@ -818,12 +845,14 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* although retrieve_objects() will never return that.
*/
static void
-free_retrieved_objects(SimpleStringList *list)
+free_retrieved_objects(RetrievedObjects *objs)
{
- if (list)
+ if (objs)
{
- simple_string_list_destroy(list);
- pg_free(list);
+ for (int i = 0; i < objs->num_objects; i++)
+ pg_free(objs->objects[i].target);
+
+ pg_free(objs);
}
}
@@ -831,18 +860,25 @@ free_retrieved_objects(SimpleStringList *list)
* Construct a vacuum/analyze command to run based on the given
* options, in the given string buffer, which may contain previous garbage.
*
- * The table name used must be already properly quoted. The command generated
- * depends on the server version involved and it is semicolon-terminated.
+ * The table reference used must be already properly quoted. It is usually a
+ * table name, but may also include a column list. The command generated
+ * depends on the server version involved and it is semicolon-terminated. If
+ * use_only is set, the command targets the table with ANALYZE ONLY.
*/
static void
prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
- vacuumingOptions *vacopts, const char *table)
+ vacuumingOptions *vacopts, const char *table,
+ bool use_only)
{
int serverVersion = PQserverVersion(conn);
const char *paren = " (";
const char *comma = ", ";
const char *sep = paren;
+ Assert(!use_only ||
+ vacopts->mode == MODE_ANALYZE ||
+ vacopts->mode == MODE_ANALYZE_IN_STAGES);
+
resetPQExpBuffer(sql);
if (vacopts->mode == MODE_ANALYZE ||
@@ -880,6 +916,13 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
if (vacopts->verbose)
appendPQExpBufferStr(sql, " VERBOSE");
}
+
+ /* ANALYZE ONLY is supported since v18 */
+ if (serverVersion >= 180000)
+ {
+ if (use_only)
+ appendPQExpBufferStr(sql, " ONLY");
+ }
}
else
{
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 85d989f395d..7bab17fdc93 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2702,6 +2702,8 @@ ResultState
ResultType
RetainDeadTuplesData
RetainDeadTuplesPhase
+RetrievedObject
+RetrievedObjects
ReturnSetInfo
ReturnStmt
ReturningClause
--
2.39.3 (Apple Git-146)
=
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: analyze-in-stages post upgrade questions
@ 2026-08-13 05:31 Fujii Masao <masao.fujii@gmail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 33+ messages in thread
From: Fujii Masao @ 2026-08-13 05:31 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Nathan Bossart <nathandbossart@gmail.com>; Justin Pryzby <pryzby@telsasoft.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Mircea Cadariu <cadariu.mircea@gmail.com>; Zechman, Derek S <Derek.S.Zechman@snapon.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-hackers@lists.postgresql.org
On Fri, Aug 7, 2026 at 3:31 PM Daniel Gustafsson <daniel@yesql.se> wrote:
>
> > On 7 Aug 2026, at 06:30, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> > Attached is an updated v3 patch. Barring any objections, I'll commit it.
>
> No objection, v3 LGTM.
I've pushed the patch. Thanks!
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 33+ messages in thread
end of thread, other threads:[~2026-08-13 05:31 UTC | newest]
Thread overview: 33+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-27 13:35 analyze-in-stages post upgrade questions Zechman, Derek S <Derek.S.Zechman@snapon.com>
2025-06-27 13:41 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-06-28 01:11 ` Zechman, Derek S <Derek.S.Zechman@snapon.com>
2025-06-27 15:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-06-27 21:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-06-28 01:23 ` Zechman, Derek S <Derek.S.Zechman@snapon.com>
2025-06-28 05:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-07 12:43 ` Mircea Cadariu <cadariu.mircea@gmail.com>
2025-07-09 11:30 ` Zechman, Derek S <Derek.S.Zechman@snapon.com>
2025-07-09 15:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-09 16:37 ` Mircea Cadariu <cadariu.mircea@gmail.com>
2025-07-09 17:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-10 16:20 ` Mircea Cadariu <cadariu.mircea@gmail.com>
2025-07-11 09:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-11 10:42 ` Mircea Cadariu <cadariu.mircea@gmail.com>
2025-07-30 02:49 ` Fujii Masao <masao.fujii@gmail.com>
2025-08-06 04:01 ` Mircea Cadariu <cadariu.mircea@gmail.com>
2025-08-06 14:25 ` Fujii Masao <masao.fujii@gmail.com>
2025-08-06 18:14 ` Nathan Bossart <nathandbossart@gmail.com>
2025-08-18 02:36 ` Fujii Masao <masao.fujii@gmail.com>
2025-08-06 20:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-08-18 02:38 ` Fujii Masao <masao.fujii@gmail.com>
2025-08-18 06:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-08-19 14:40 ` Fujii Masao <masao.fujii@gmail.com>
2025-08-19 15:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-08-20 04:31 ` Fujii Masao <masao.fujii@gmail.com>
2025-08-20 07:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-08-21 01:51 ` Justin Pryzby <pryzby@telsasoft.com>
2026-06-04 17:14 ` Justin Pryzby <pryzby@telsasoft.com>
2026-07-15 15:42 ` Nathan Bossart <nathandbossart@gmail.com>
2026-07-16 02:53 ` Fujii Masao <masao.fujii@gmail.com>
2026-08-06 14:30 ` Daniel Gustafsson <daniel@yesql.se>
2026-08-13 05:31 ` Fujii Masao <masao.fujii@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox