In PostgreSQL, why do the clicks parent's statistics go stale even with autovacuum on, and how does PostgreSQL 18 change the way you refresh them?
answer
- the parent holds no rows
- partitions fine, parent never
- inherited = true rows
- ANALYZE ONLY is new
basics
~10 sAutovacuum never processes a partitioned parent, so its whole-hierarchy statistics exist only after a manual ANALYZE. Schedule ANALYZE on the parent; PostgreSQL 18's ANALYZE ONLY clicks refreshes them without re-analyzing every partition.
solid answer
~50 sEach monthly partition is an ordinary table, so autovacuum analyzes it and per-partition estimates stay current. The partitioned parent stores no tuples, so autovacuum never processes it, and the statistics for the hierarchy as a whole (`pg_stats` rows for `clicks` with `inherited = true`) exist only after someone runs `ANALYZE clicks`. The planner reads them when it estimates against the parent as a whole, such as a join between `clicks` and `campaigns` or a `GROUP BY` across months; without them it falls back to default guesses. In `pg_stat_user_tables` the parent's `last_autoanalyze` never advances. Fix it with a scheduled `ANALYZE clicks`. Before 18 that also re-analyzes every partition; PostgreSQL 18 adds `ANALYZE ONLY clicks`, which still samples all partitions but skips the per-partition pass. 18 also makes `VACUUM` and `ANALYZE` recurse into legacy inheritance children unless you write `ONLY`.
code
sql · 12 lines-- Has the parent ever been analyzed, and by whom?
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('clicks', 'clicks_2026_09');
-- Whole-hierarchy statistics exist only as inherited rows
SELECT attname, n_distinct
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'clicks' AND inherited;
-- PostgreSQL 18: refresh the parent's statistics only
ANALYZE ONLY clicks;go deeper
Remember that autovacuum handles the partitions but never the partitioned parent, so somebody has to run ANALYZE on the parent.
Explain which statistics belong to the parent (pg_stats rows with inherited = true), how they are gathered by sampling partitions, and how to tell from pg_stat_user_tables that they are stale.
Connect stale parent statistics to bad join and aggregate estimates, and build the schedule: after loads, after backfills, and on 18 with ANALYZE ONLY to keep it cheap.
Treat statistics maintenance as part of the partitioning design and decide where it lives: in the load pipeline, in a scheduler, or in the partition-management tool.
## What autovacuum does and does not process - Each monthly partition of `clicks` is an ordinary table. Autovacuum vacuums and analyzes it on its own thresholds, so **per-partition statistics stay fresh**. - The **partitioned parent** stores no tuples, so autovacuum never processes it. The manual is explicit that autovacuum does not run `ANALYZE` on partitioned tables and that this can cause poor plans. - The same gap exists for a legacy **inheritance** parent whose rows all arrive in its children: autovacuum only counts changes made to the parent itself. ## What statistics the parent has `ANALYZE clicks` builds statistics for the hierarchy as a whole by **sampling rows from all partitions**. They appear in `pg_stats` under `tablename = 'clicks'` with `inherited = true`. A legacy inheritance parent gets two sets, parent-only (`inherited = false`) and parent-plus-children (`inherited = true`); a partitioned parent has no rows of its own, so it only ever has the inherited set. The planner reads the inherited set when it estimates something against the parent as a whole rather than one partition: the selectivity of a join between `clicks` and `campaigns`, or how many groups a `GROUP BY campaign_id` over a quarter produces. With no statistics it falls back to built-in default estimates, which on a large clickstream can be far off and push the planner to the wrong join method. Two things make the gap worse on a monthly clickstream: - A parent analyzed right after it was created, while still empty, records **no** statistics: `ANALYZE` on a completely empty table keeps whatever it had, which is nothing. - The data drifts. Every month brings new campaigns and ads, so a distinct-count or most-common-values list gathered in January says nothing about September, even though each September partition's own statistics are current. ## Diagnosing it 1. Check analyze history: in `pg_stat_user_tables` the row for `clicks` shows `last_analyze` from manual runs, while `last_autoanalyze` never advances. 2. Check that parent-level statistics exist at all: `SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'clicks' AND inherited;` returning no rows means there are none. 3. Compare estimated and actual row counts for the join or aggregate in `EXPLAIN ANALYZE` (reading the plan is its own topic). ## Fixing it | Version | Command | What it does | |---|---|---| | 17 and earlier | `ANALYZE clicks;` | builds the parent's statistics and also re-analyzes every partition | | 18 | `ANALYZE clicks;` | the same: parent plus every partition | | 18 | `ANALYZE ONLY clicks;` | builds only the parent's statistics; it still samples rows from all partitions but skips their own per-partition pass | Run it after the initial load, after large backfills, and on a schedule, for example after each month closes. On 18, `ANALYZE ONLY` makes a frequent schedule cheaper, because autovacuum already keeps the partitions themselves current. Things that do **not** fix it: - Storage parameters on the parent: `ALTER TABLE clicks SET (autovacuum_analyze_scale_factor = ...)` fails with `cannot specify storage parameters for a partitioned table`. - Analyzing the partitions one by one: `ANALYZE clicks_2026_09` refreshes that partition only, not the parent's inherited statistics. - Waiting: autovacuum has no trigger for the parent at all. ## The PostgreSQL 18 change for inheritance PostgreSQL 18 changed `VACUUM` and `ANALYZE` to process the **inheritance children** of a named parent by default; before 18 a legacy inheritance parent was processed alone. The new `ONLY` keyword restores the old behaviour, and the same keyword gives partitioned tables their parent-only pass. A maintenance script that ran `ANALYZE` on a large legacy inheritance tree's parent takes longer after upgrading to 18 unless it adds `ONLY`. ## If pg_partman manages the table pg_partman does not analyze the parent for you by default: `run_maintenance()` takes `p_analyze` defaulting to false, the background worker's `pg_partman_bgw.analyze` defaults to `off`, and even when enabled it analyzes only sets that just gained a new child. Schedule the parent `ANALYZE` yourself.
- If the parent has no rows, why does ANALYZE ONLY clicks still read data?The parent's statistics describe the whole hierarchy, so PostgreSQL gathers them by sampling rows from all partitions. `ONLY` skips the separate per-partition pass that would also rewrite each partition's own statistics; it does not skip the sampling that the parent's statistics are built from.
- What does ANALYZE on a legacy inheritance parent do differently in PostgreSQL 18?Since 18 it also analyzes each inheritance child, not just the parent and the parent-plus-children statistics. Write `ANALYZE ONLY parent` for the pre-18 behaviour. VACUUM changed the same way.
- Does pg_partman keep the parent's statistics fresh?Not by default. `run_maintenance()` has `p_analyze` false and the background worker's `pg_partman_bgw.analyze` is `off`; when enabled it analyzes only sets that just gained a new child. A separate scheduled `ANALYZE` on the parent is still needed.
- Autovacuum analyzes the partitioned parent once enough rows change in its partitions.
- Analyzing every partition one by one also refreshes the parent's statistics.
- On PostgreSQL 17 you can run ANALYZE ONLY clicks to refresh just the parent.
- Setting autovacuum_analyze_scale_factor on the parent makes autovacuum process it.
- A partitioned parent's statistics appear in pg_stats with inherited = false.
Assumes PostgreSQL 18: ANALYZE ONLY on a partitioned table and VACUUM/ANALYZE recursing into inheritance children are both new in 18.