Analyzing ANALYZE

analyze_part_table.md

Timeline:

• Oct 3: Learning about this issue
• Tweet: https://x.com/andatki/status/1841915460062769187
• Reviewing commit: https://github.com/postgres/postgres/commit/62ddf7ee9a399e0b9624412fc482ed7365e38958
• Summarizing thoughts below

Goal: Understand WHY to ANALYZE ONLY on a root partition table.

The current version (17) documentation seems to be wrong. It says only the root table is analyzed, but in the commit message David Rowley describes the partitions of the root table are also analyzed when ANALYZE runs on the root.

Postgres 17 docs

If you are using manual VACUUM or ANALYZE commands, don't forget that you need to run them on each child table individually. A command like: ANALYZE measurement;

Postgres (devel) docs

After the commit, we see the docs now describe using ONLY to get the behavior that was (incorrectly) documented before.

Manual VACUUM and ANALYZE commands will automatically process all inheritance child tables. If this is undesirable, you can use the ONLY keyword. A command like: ANALYZE ONLY measurement;

Question

My question is: why ANALYZE ONLY on the root partition table at all? The interesting statistics like samples and counts etc. are on the partitions of the root.

For example, even doing a count of rows mixed with non-partitioned and partitioned tables, the partitioned tables require special counting logic.

Need to do some experiments to understand what stats we need on the root table, and why to ANALYZE it all.

With that said, since Autovacuum is handling running ANALYZE for us, evaluating thresholds and triggering a VACUUM (ANALYZE) part_table for each partition as needed, for whatever reason there is to ANALYZE only the root, being able to isolate to the root by adding ONLY makes sense, especially since that's what seems to have been previously documented although inaccurately.

Todo:

• Do some experiments, when TBD :)

Used tables from pgsql-hackers list / David Rowley examples:

DROP TABLE IF EXISTS only_parted, only_parted1;

CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);

INSERT INTO only_parted VALUES (1, 'a');

-- Testing on PG 16 so don't have ONLY yet
--ANALYZE ONLY only_parted;
ANALYZE only_parted;

-- Trying to understand David's response on query planning,
-- Are there visible stats, a system view, related to the partition root itself, not from partitions?

-- These are row level stats, and are sensible in both tables
-- Same for pg_stat_user_tables, pg_stat_all_tables
SELECT *
  FROM pg_stat_user_tables
  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
  ORDER BY relname;

-- Nothing partition related here
SELECT *
FROM pg_stats
WHERE tablename = 'only_parted';

SHOW enable_partition_pruning;
 enable_partition_pruning
--------------------------
 on
(1 row)

Even though no statistics are directly collected on the root table, the planner uses the partition metadata to decide which partitions are relevant for the query.

17 docs:
https://www.postgresql.org/docs/current/sql-analyze.html
For partitioned tables,...

No statistics are collected for only the parent table (without data from its partitions), because with partitioning it's guaranteed to be empty.

Thoughts: I still don't fully understand why a manual ANALYZE/ANALYZE ONLY on a partition root is needed.

It sounds like it's needed for partition pruning to work for query planning, and perhaps not done automatically by Autovacuum which is running on partitions of a root, but not rolling up to the root.

Whatever ANALYZE does for a partition root, I'm not sure if there's a system view that makes the work it performed visible in a similar way to row level stats we'd see for partitions from pg_stat, pg_stat_user_tables, pg_stat_all_tables.

-- attrs "a" and "b"
INSERT INTO only_parted VALUES (1, 'b');
INSERT INTO only_parted VALUES (1, 'c');
INSERT INTO only_parted VALUES (1, null);  -- 25% of rows null, null_frac

ANALYZE only_parted; -- propagates to only_parted1 partition

-- Stats for only attr "b", but both part root=only_parted, and partition=only_parted1
SELECT *
FROM pg_stats
WHERE tablename IN ('only_parted', 'only_parted1')
AND attname = 'b';
 schemaname |  tablename   | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+--------------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
 public     | only_parted  | b       | t         |      0.25 |         2 |      -0.75 |                  |                   | {a,b,c}          |           1 |                   |                        |
 public     | only_parted1 | b       | f         |      0.25 |         2 |      -0.75 |                  |                   | {a,b,c}          |           1 |                   |                        |
(2 rows)

Observations:

• only_parted1 (the partition) has null_frac populated, and this same info is available on the root only_parted
• Same as above, partition and root for n_distinct and others

Thanks for the share of this below post Michael Christofides:

Daniel Westermann points out https://www.dbi-services.com/blog/postgresql-partitioned-tables-and-optimizer-statistics/:

• Re: prior to new behavior in 18 and ANALYZE ONLY, since ANALYZE propagates "If your partitions are huge, this can take quite some time and there is not much you can do about it. "
• ANALYZE ONLY "avoids redundant work as statistics on the partitions will be collected automatically anyway."
• Daniel writes: "If you want to have statistics on the partitioned table as well, you have to do that manually:"

My overall summary:

• Prior to ANALYZE ONLY / Postgres 18, stats were not possible to be collected on ONLY the partition root. But partitions have stats based on normal Autovacuum running/normal criteria, where partitioned tables aren't different from non-partitioned tables. When VACUUM runs it includes the ANALYZE argument. Partitions stats don't seem to be propagated up automatically.
• Why have stats at all on the root? One reason Michael Christofides mentioned was without those stats, query planning could produce bad planner estimates. David Rowley showed how certain predicates in in the query planning stage can use stats.

My next question then is why not always propagate stats "up" from partitions to the partition root? I don't know. My guess is it could possibly be load intensive, and possibly redundant, running multiple instances if multiple partitions are being analyzed at the same time. May be easier to leave up to the user.

How big is the scope of the problem where partition root tables don't have stats by default? It seems like it must be lower priority issue, since it seems like Postgres has never automatically collected stats on the partition root by default, yet features like partition pruning work to my knowledge without explicitly running a manual ANALYZE on the partition root table.