Re: vacuumdb --analyze-only (e.g., after a major upgrade) vs. partitioned tables: pg_statistic missing stats for the partitioned table itself

On Thu, Oct 24, 2024 at 02:48:42PM -0700, Nikolay Samokhvalov wrote:
> [ACg8ocIyQq]
> Nikolay Samokhvalov <nik(at)postgres(dot)ai> 2:47 PM (0 minutes ago)
> [cleardot]
> to pglsql-hackers [cleardot]
> [cleardot]
> I just learned that vacuumdb --analyze-only doesn't update stats for the
> partitioned table itself, taking care only about individual partitions:

Yes, this is covered in the ANALYZE manual page:

https://www.postgresql.org/docs/current/sql-analyze.html

For partitioned tables, ANALYZE gathers statistics by sampling
rows from all partitions; in addition, it will recurse into
each partition and update its statistics. Each leaf partition
is analyzed only once, even with multi-level partitioning. No
statistics are collected for only the parent table (without data
from its partitions), because with partitioning it's guaranteed
to be empty.

It is discussed here:

https://www.postgresql.org/message-id/flat/CAB%2B%3D1TULKjDSBHxqSQVQstxcHshGzQUnHfp45GSESAu2qm0VKg%40mail.gmail.com#586bc5deef05c35ac16100dee99f6e9e

> I propose considering 3 fixes:
>
> 1) vacuumdb --analyze / --analyze-only to update stats for the partitioned
> table, so people using pg_upgrade are not in trouble
> 2) present the ANALYZE metadata for partitioned tables in pg_stat_all_tables
> 3) for old versions, either backpatch with fix (1) OR just add to the docs (and
> maybe to the final words pg_upgrade prints), suggesting something like this in
> addition to vacuumdb analyze-only:
>
> -- psql snippet
> select format(
>   'analyze verbose %I.%I;',
>   relnamespace::oid::regnamespace,
>   oid::regclass
> ) as vacuum_command
> from pg_class
> where relkind = 'p' \gexec
>
> Additionally, I do like the idea of ANALYZE ONLY from the -general discussion
> above (though, there might be confusion with logic of --analyze and
> --analyze-only in vacuumdb).
>
> Does it make sense?

I certainly would like to see this improved.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"