skip to content

After restoring a PostgreSQL 18 pg_dump archive into staging, queries plan badly at first; what do pg_dump's --statistics option and a post-restore ANALYZE each cover?

level: seniorimportance: should knowfreq 32%

answer

  1. empty pg_statistic after a load
  2. opt-in on the dump side
  3. on by default on the restore side
  4. not extended or cumulative stats
  5. masking changes the distribution

basics

~20 s

A logical restore loads rows without planner statistics. PostgreSQL 18's pg_dump --statistics, off by default, dumps table and column statistics that pg_restore applies, but not extended statistics, so ANALYZE is still needed for those and after masking.

solid answer

~40 s

Without help, the planner's column statistics in `pg_statistic` are empty for freshly restored tables until `ANALYZE` runs, by hand or through auto-analyze, and until then the planner guesses. Before 18, a dump never carried statistics. In PostgreSQL 18, `pg_dump --statistics` adds `STATISTICS DATA` entries that call `pg_restore_relation_stats()` and `pg_restore_attribute_stats()`. It is **off by default** (`--no-statistics` is the documented default), while `pg_restore` applies statistics whenever the archive has them. What is not included: data for extended statistics made with `CREATE STATISTICS`, statistics added by extensions, and the cumulative counters in views such as `pg_stat_user_tables`. So still run `ANALYZE` on tables with extended statistics, and on everything you changed after loading, such as masked columns, because the copied statistics describe production's data.

code

bash · 8 lines
bash
# Dump schema, data and optimizer statistics (opt-in in PostgreSQL 18)
pg_dump -h prod-pg -Fc --statistics -f cust_harbor.dump cust_harbor

# pg_restore applies statistics found in the archive by default
pg_restore -h staging-pg -d cust_harbor -j 4 cust_harbor.dump

# Fill what the dump could not carry, e.g. extended statistics
vacuumdb -h staging-pg -d cust_harbor --analyze-only --missing-stats-only

go deeper

for a junior

Remember that a freshly restored database needs ANALYZE before its query plans can be trusted.

for a middle

Explain the 18 options: --statistics is opt-in for pg_dump, pg_restore applies what the archive holds, and --statistics-only exists on both sides.

for a senior

List what dumped statistics miss (extended statistics, extension statistics, cumulative counters), and re-analyze after masking or skipped data before trusting staging plans.

for a principal

Decide whether staging refreshes should carry production statistics, for plan fidelity, or rebuild them from masked data, for honest estimates, and make that choice explicit in the pipeline.

## Why a fresh restore plans badly PostgreSQL's planner estimates row counts from two kinds of statistics: - **relation-level** numbers in `pg_class`: `relpages`, `reltuples`, `relallvisible`; - **column-level** statistics in `pg_statistic` (readable through `pg_stats`): null fraction, number of distinct values, most common values, histograms. A logical restore creates new tables and loads their rows with `COPY`. Unless the dump carried statistics, nothing fills `pg_statistic` for those columns until `ANALYZE` runs, either by hand or when autovacuum's auto-analyze reaches the table. Until then the planner falls back to default selectivity guesses, which can be badly wrong for joins and skewed filters. How `ANALYZE` samples and what `pg_stats` shows belongs to the statistics topic. The question here is what a dump can carry across. ## What PostgreSQL 18 adds Up to 17, `pg_dump` had no way to carry optimizer statistics. PostgreSQL 18 adds them as optional archive items: - `pg_dump --statistics` writes `STATISTICS DATA` entries. Each is a `SELECT` calling `pg_restore_relation_stats()` (relation-level values) or `pg_restore_attribute_stats()` (per-column values). - **It is off by default.** The 18 manual documents `--no-statistics` as the default, so an ordinary `pg_dump -Fc` contains no statistics. - `--statistics-only` dumps only the statistics. Combining `--statistics` with `-a`/`--data-only` or `-s`/`--schema-only` is rejected with `options ... cannot be used together`. - Table statistics sit in the **data** section and index statistics in **post-data**. Materialized-view statistics are restored after the view's data, because refreshing it resets them. - `pg_dumpall` has the same options. | | pg_dump 18 | pg_restore 18 | |---|---|---| | Default | statistics not dumped | statistics restored if present | | Opt in or out | `--statistics` | `--no-statistics` | | Only statistics | `--statistics-only` | `--statistics-only` | The restore functions require the `MAINTAIN` privilege on the table, or ownership of the database. A restore connected as the staging database's owner therefore applies them. Compare `pg_upgrade`, which in 18 carries statistics by default. That is part of the upgrade path, a separate topic. ## What the dumped statistics do not include The manual lists three gaps: 1. **Extended statistics** created with `CREATE STATISTICS`. The object's definition is dumped as schema, but its collected data is not, so multi-column correlation estimates are missing until `ANALYZE`. Dumping that data is a PostgreSQL 19 beta change, not part of 18. 2. **Custom statistics added by an extension.** 3. The **cumulative statistics system**, meaning counters in views such as `pg_stat_user_tables`: scans, tuples inserted, dead tuples, last analyze time. The copy starts these from zero. ## What to run after the restore - **Dumped with `--statistics`:** the core table and column statistics are in place as soon as the data section is restored. You can then analyze only what is missing, with `vacuumdb --analyze-only --missing-stats-only` (new in 18). It analyzes only relations missing statistics for a column, an index expression or an extended statistics object. - **Dumped without them:** run `ANALYZE` on the database, or `vacuumdb --analyze-only`, before you trust any plan or timing. - **Changed the data after loading?** The copied statistics describe production's values. If staging masks emails or names, or skips a table's rows with `--exclude-table-data`, `ANALYZE` the changed tables so the estimates match what is really there. ## Checking what the restore brought Two quick checks tell you where you stand after the restore: - `pg_stats` for a restored table shows rows per column when statistics arrived, and nothing when they did not; - `pg_stat_user_tables.last_analyze` stays empty even when statistics were restored, because restoring statistics is not an `ANALYZE` and the cumulative counters start from zero. The rows loaded by the restore still count as modifications in the cumulative statistics. Auto-analyze therefore typically revisits the larger tables soon after the load anyway, which is harmless when the copied statistics were right and useful when they were not. ## Why this is interview-worthy - It is a common source of 'staging is slower than production' reports right after a refresh. - The 18 default is the trap. The release notes announce new statistics options, and it is easy to assume they are on. In `pg_dump` they are opt-in. - A good answer separates **planner statistics**, which can be dumped in 18, from **activity counters**, which never are.

  • Why might pg_dump --data-only --statistics fail immediately?
    In 18, `pg_dump` rejects `--statistics` together with `-a`/`--data-only`, and together with `-s`/`--schema-only`, with `options ... cannot be used together`. The `-only` switches exclude statistics by definition. To get rows plus statistics without definitions, use `--no-schema --statistics`. For statistics alone, use `--statistics-only`.
  • After a restore with statistics, why can staging plans still differ from production's?
    Extended statistics data is not dumped, so multi-column estimates are missing until `ANALYZE`. Any masking, or skipped table data, makes the copied statistics describe values that are no longer there. And the cumulative counters start from zero, so autovacuum's view of the tables restarts too. Analyze what changed before comparing plans.

  • Since PostgreSQL 18, pg_dump dumps optimizer statistics by default.
  • With --statistics in the dump, ANALYZE is never needed after the restore.
  • pg_dump --statistics also copies the counters in pg_stat_user_tables.
  • Extended statistics data travels with pg_dump 18's --statistics.
  • pg_restore skips archived statistics unless --statistics is passed.

Assumes PostgreSQL 18; pg_dump has no statistics options before 18, statistics are opt-in for pg_dump but applied by default by pg_restore, and dumping extended statistics exists only in the 19 beta.