MIME-Version: 1.0
References: <CO1PR04MB8281387B9AD9DE30976966BBC045A@CO1PR04MB8281.namprd04.prod.outlook.com>
 <6add2a9a-7cf2-4d1b-8f3e-2e26a7ebe883@aklaver.com> <4d8122febd3007143504e4b6034b4253f7000761.camel@cybertec.at>
 <PH0PR04MB82943D67CD179002714AA503C044A@PH0PR04MB8294.namprd04.prod.outlook.com>
 <f2ebeef82fddaf8a8152383f8ff62e75f20aac3c.camel@cybertec.at>
 <PH0PR04MB8294E796D6297D4E3BE4EFB6C049A@PH0PR04MB8294.namprd04.prod.outlook.com>
 <08d943a83590308cbb9be594d80b4e19ca80e08e.camel@cybertec.at>
 <2505eef1-b6aa-4518-baff-861a2e6a507b@gmail.com> <b65126bf381d4b41f561afe691000b77ac7b8cc2.camel@cybertec.at>
 <a018a729-9b18-444f-baed-be0d6d8ecb80@gmail.com> <2c1f1834107045dfa8b32417771b56bab0cffc56.camel@cybertec.at>
 <2cce9851-327a-4b1a-ab8e-531c2f92532b@gmail.com>
In-Reply-To: <2cce9851-327a-4b1a-ab8e-531c2f92532b@gmail.com>
From: Fujii Masao <masao.fujii@gmail.com>
Date: Wed, 30 Jul 2025 11:49:56 +0900
Message-ID: <CAHGQGwEvwpydO7hjnB51Q2zgS0ydv31X4HCFLNzMeqLr=poS8w@mail.gmail.com>
Subject: Re: analyze-in-stages post upgrade questions
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
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHGQGwEvwpydO7hjnB51Q2zgS0ydv31X4HCFLNzMeqLr%3DpoS8w%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 11, 2025 at 7:42=E2=80=AFPM Mircea Cadariu <cadariu.mircea@gmai=
l.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 partiti=
ons
+        will be considered, unless a partitioned table is explicitly speci=
fied
+        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 optio=
n.
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 analyzin=
g
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 comm=
ent
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,

--=20
Fujii Masao