Skip to content

Instantly share code, notes, and snippets.

@andyatkinson

andyatkinson/analyze_part_table.md

Created October 3, 2024 20:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andyatkinson/1ea2d371d1977630d8653b5fd39c14e2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andyatkinson/1ea2d371d1977630d8653b5fd39c14e2 to your computer and use it in GitHub Desktop.
Download ZIP
Analyzing ANALYZE
Raw
analyze_part_table.md

Timeline:

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 :)
@andyatkinson
Copy link
Author

andyatkinson commented Oct 4, 2024 

-- 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment