Thread: vacuumdb --analyze-only (e.g., after a major upgrade) vs. partitioned tables: pg_statistic missing stats for the partitioned table itself
| 2:47 PM (0 minutes ago) |  | ||
| ||||
I just learned that vacuumdb --analyze-only doesn't update stats for the partitioned table itself, taking care only about individual partitions:
(DDL doesn't matter here)
# vacuumdb --analyze-only -U postgres test --verbose
...
INFO: analyzing "public.measurement_2023_01"
INFO: "measurement_2023_01": scanned 6370 of 6370 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
INFO: "measurement_2023_01": scanned 6370 of 6370 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows
INFO: "measurement_2023_02": scanned 6257 of 6257 pages, containing 982279 live rows and 0 dead rows; 30000 rows in sample, 982279 estimated total rows
INFO: analyzing "public.measurement_2023_03"
INFO: "measurement_2023_03": scanned 6483 of 6483 pages, containing 1017721 live rows and 0 dead rows; 30000 rows in sample, 1017721 estimated total rows
...
test=# select starelid::regclass, count(*) from pg_statistic where starelid::regclass::text ~ 'measurement' group by 1 order by 1;
starelid | count
---------------------+-------
measurement_2023_01 | 4
measurement_2023_02 | 4
measurement_2023_03 | 4
(3 rows)
While for the single-threaded SQL-level ANALYZE:
test=# analyze verbose measurement;
...
test=# select starelid::regclass, count(*) from pg_statistic where starelid::regclass::text ~ 'measurement' group by 1 order by 1;
starelid | count
---------------------+-------
measurement | 4
measurement_2023_01 | 4
measurement_2023_02 | 4
measurement_2023_03 | 4
(4 rows)
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.
INFO: analyzing "public.measurement_2023_03"
INFO: "measurement_2023_03": scanned 6483 of 6483 pages, containing 1017721 live rows and 0 dead rows; 30000 rows in sample, 1017721 estimated total rows
...
test=# select starelid::regclass, count(*) from pg_statistic where starelid::regclass::text ~ 'measurement' group by 1 order by 1;
starelid | count
---------------------+-------
measurement_2023_01 | 4
measurement_2023_02 | 4
measurement_2023_03 | 4
(3 rows)
While for the single-threaded SQL-level ANALYZE:
test=# analyze verbose measurement;
...
test=# select starelid::regclass, count(*) from pg_statistic where starelid::regclass::text ~ 'measurement' group by 1 order by 1;
starelid | count
---------------------+-------
measurement | 4
measurement_2023_01 | 4
measurement_2023_02 | 4
measurement_2023_03 | 4
(4 rows)
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)
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)
This is also discussed in https://www.postgresql.org/message-id/flat/CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA%40mail.gmail.com
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
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(
-- psql snippet
select format(
'analyze verbose %I.%I;',
relnamespace::oid::regnamespace,
oid::regclass
) as vacuum_command
from pg_class
where relkind = 'p' \gexec
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