Statistics on Partitioned Tables #

Every partition of a monthly-partitioned events table has fresh statistics; autovacuum analyzes them on schedule. Yet a query joining events to accounts and grouping by account_id estimates 200 groups for a result with 1.4 million, and chooses a sort-based plan that runs ten times slower than the hash plan it should have picked. The missing statistics are not on any partition. They are on the parent.

Partition pruning is covered in partition pruning analysis; this page covers the estimates that pruning does not fix.

The Planner Condition #

A partitioned table has two layers of statistics:

Autovacuum never analyzes a partitioned parent. The parent holds no rows itself, so its change counters never trigger autoanalyze. Unless someone runs ANALYZE on the parent explicitly, inherited statistics are missing — or were collected once, at creation or after a migration, and never refreshed.

With no inherited statistics, estimates above the Append fall back to defaults. The default number of distinct values is 200, which is why grouped queries over partitioned tables so often estimate exactly 200 groups.

Two layers of statistics, two layers of the plan A stack of plan nodes. At the top, a HashAggregate grouping by account_id and a join with accounts use the parent's inherited statistics. Below them the Append combines partitions. At the bottom, each partition scan uses that partition's own statistics, which autovacuum keeps current. HashAggregate / GROUP BY account_id uses parent (inherited) stats Hash Join with accounts join selectivity: parent stats Append sums partition row estimates Seq Scan on events_2026_09 … per-partition stats (autovacuum) autovacuum maintains only the bottom layer

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE)
SELECT e.account_id, count(*)
FROM events e
WHERE e.occurred_at >= '2026-07-01'
GROUP BY e.account_id;
GroupAggregate  (cost=… rows=200 width=16) (actual time=58204.1..71840.6 rows=1402210 loops=1)
  Group Key: e.account_id
  ->  Sort  (actual time=58204.0..66102.4 rows=184022110 loops=1)
        Sort Key: e.account_id
        Sort Method: external merge  Disk: 3204410kB
        ->  Append  (actual rows=184022110 loops=1)
              ->  Seq Scan on events_2026_07 e_1  (rows=61204110) (actual rows=60804220 loops=1)
              ->  Seq Scan on events_2026_08 e_2  (rows=62004220) (actual rows=62210440 loops=1)
              ->  Seq Scan on events_2026_09 e_3  (rows=61040102) (actual rows=61007450 loops=1)
Execution Time: 72104.8 ms
-- partition scan estimates are accurate: per-partition statistics are current
-- rows=200 groups: the default n_distinct, because the parent has no inherited stats

Confirm the missing statistics:

SELECT inherited, n_distinct FROM pg_stats
WHERE tablename = 'events' AND attname = 'account_id';
-- (0 rows)

After ANALYZE events;:

HashAggregate  (cost=… rows=1380402 width=16) (actual time=31204.8..32810.4 rows=1402210 loops=1)
  Group Key: e.account_id
  Planned Partitions: 16  Batches: 17  Memory Usage: 262193kB  Disk Usage: 612408kB
  ->  Append  (actual rows=184022110 loops=1)
Execution Time: 33102.6 ms
Accurate leaves, wrong top Three items are annotated. Partition scan estimates close to actual rows show per-partition statistics are fine. A group estimate of exactly 200 shows the default distinct count at the parent level. An empty pg_stats result for the parent confirms inherited statistics were never collected. Seq Scan on events_2026_08 (rows=62004220) partition stats are current GroupAggregate (rows=200) (actual rows=1402210) default n_distinct = 200 pg_stats … tablename='events' → 0 rows parent never analyzed exactly 200 groups over a partitioned table is a telltale default

Step-by-Step Resolution #

  1. Check for inherited statistics on each partitioned table.

    SELECT c.relname,
       (SELECT count(*) FROM pg_stats s WHERE s.tablename = c.relname AND s.inherited) AS inherited_cols,
       (SELECT max(last_analyze) FROM pg_stat_all_tables t WHERE t.relid = c.oid) AS last_analyze
FROM pg_class c WHERE c.relkind = 'p';

  2. Analyze the parent. ANALYZE on a partitioned table samples rows across all partitions to build inherited statistics, and also analyzes each partition:

    ANALYZE events;

    From PostgreSQL 18, ANALYZE ONLY events; collects the parent’s inherited statistics without re-analyzing every partition, which is far cheaper when partitions are already current.

  3. Schedule it. Because autovacuum will not, run parent analysis from cron or a scheduler after significant data changes — typically after each partition fills or after bulk loads, alongside the steps in stale statistics after bulk loads.

  4. Override inherited distinct counts where sampling underestimates them:

    ALTER TABLE events ALTER COLUMN account_id SET (n_distinct_inherited = -0.008);
ANALYZE events;

  5. Raise the statistics target on the parent’s columns if skew spans partitions; the setting applies to the parent’s sample separately from each partition’s.

  6. Re-check grouped and joined queries that sit above the Append.

Who keeps which statistics current A timeline across a month. Partition creation starts with no statistics. Autovacuum analyzes the active partition repeatedly as it fills. At month end the partition stops changing. A scheduled job analyzes the parent to refresh inherited statistics, the only step autovacuum never performs. partition created no stats yet autoanalyze runs partition stats refreshed month closes partition stable scheduled ANALYZE parent inherited stats refreshed the last step is manual — autovacuum never analyzes a partitioned parent

Before and After #

-- BEFORE: no inherited statistics on events
GroupAggregate (rows=200) → Sort (external merge, 3 GB) → Append          Execution Time: 72104.8 ms

-- AFTER: ANALYZE events (scheduled weekly)
HashAggregate (rows=1380402, Planned Partitions: 16) → Append              Execution Time: 33102.6 ms

Partitionwise planning changes which statistics matter #

When enable_partitionwise_aggregate or enable_partitionwise_join is on and the grouping or join key includes the partition key, the planner can push the aggregate or join below the Append and plan it per partition. Those per-partition nodes use per-partition statistics, which autovacuum maintains — so partitionwise plans are much less sensitive to missing parent statistics. That is one reason the same query can behave well in one database and badly in another: the difference is a setting, not the data. The trade-offs of those settings are in partitionwise join and aggregate plans. For grouping keys that are not the partition key — like account_id on time-partitioned events — partitionwise aggregation can only produce partial aggregates per partition, and the final grouping estimate still depends on the parent.

Declarative partitioning and legacy inheritance behave the same way here: in both, the parent’s inherited statistics are separate from the children’s and are not maintained by autovacuum.

Choosing a schedule for parent analysis #

How often to analyze the parent depends on how quickly the whole-table distribution changes, not on how quickly any one partition changes. For time-partitioned data where each new month looks much like the last, the inherited n_distinct and MCV list drift slowly, and a weekly job is usually enough. For tenant- or region-partitioned data where a large customer can arrive overnight, the combined distribution can shift in a day, and analysis should follow significant loads. A practical trigger is the sum of n_mod_since_analyze across partitions since the parent’s last_analyze: when it exceeds roughly 10% of the parent’s estimated rows, re-analyze.

The cost of the job scales with the sample size, which is governed by the parent’s statistics target, not with the number of partitions — the sample is drawn across all of them. On versions before PostgreSQL 18, the partitions are also re-analyzed, which is where most of the time goes on large hierarchies; on 18 and later, ANALYZE ONLY avoids that.

Common Pitfalls #

Assuming autovacuum covers the parent. It never analyzes partitioned tables. Diagnostic signal: last_analyze null for the parent while partitions are current. Fix: scheduled ANALYZE parent.

Analyzing the parent too often on huge tables. Before PostgreSQL 18 it also re-analyzes every partition. Diagnostic signal: long maintenance jobs. Fix: ANALYZE ONLY on 18+, or a less frequent schedule on older versions.

Stale inherited statistics after adding many partitions. The parent’s distribution drifts as data grows. Diagnostic signal: estimates good last quarter, wrong now. Fix: re-analyze after structural or volume changes.

Setting n_distinct instead of n_distinct_inherited. The plain option affects the parent’s own (empty) statistics layer, not the inherited one. Diagnostic signal: override has no effect on grouped estimates. Fix: use n_distinct_inherited on the parent.

Frequently Asked Questions #

Does autovacuum analyze partitioned tables? #

It analyzes each leaf partition, but never the partitioned parent. The parent’s inherited statistics, used for estimates above the Append, must be gathered with an explicit ANALYZE on the parent.

Why does my grouped query over a partitioned table estimate exactly 200 rows? #

200 is PostgreSQL’s default number of distinct values when no statistics exist. Over a partitioned table it usually means the parent has no inherited statistics for the grouping column.

What does ANALYZE ONLY do on a partitioned table? #

Available from PostgreSQL 18, it collects the parent’s inherited statistics without also analyzing every partition. That makes refreshing parent-level estimates cheap when the partitions’ own statistics are already current.

Up: Statistics and Selectivity Estimation