This means that if, after running pg_upgrade, we use vacuumdb to update stats faster, some stats may be missing, potentially leading to suboptimal performance.
Additionally, it doesn't help that pg_stat_all_tables doesn't show counters/timestamps for partitioned table, even after SQL-level ANALYZE:
test=# select relname, analyze_count, autoanalyze_count, last_analyze, last_autoanalyze from pg_stat_user_tables where relname ~ 'measurement';
relname | analyze_count | autoanalyze_count | last_analyze | last_autoanalyze
---------------------+---------------+-------------------+-------------------------------+------------------
measurement_2023_01 | 2 | 0 | 2024-10-24 21:25:47.979958+00 |
measurement_2023_02 | 2 | 0 | 2024-10-24 21:25:48.070355+00 |
measurement_2023_03 | 2 | 0 | 2024-10-24 21:25:48.154613+00 |
(3 rows)
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?
Nik