Postgresql Planner-Statistics Restore Enables Production-Like Query Planning Without Production Data

Issue 68 Edition 2026-03-09 5 min read

General

Sources: 1 • Confidence: High • Updated: 2026-04-12 10:23

Key takeaways

Radim Marek reports that database statistics dumps are typically under 1MB even for databases with hundreds of tables and thousands of columns.
SQLite exposes query-planner statistics in the writable sqlite_stat1 table and optionally sqlite_stat4 (when compiled with SQLITE_ENABLE_STAT4), allowing users to inject alternative statistics.
PostgreSQL 18 introduced the functions pg_restore_relation_stats() and pg_restore_attribute_stats() to restore planner statistics.
Restoring column distribution statistics can change PostgreSQL plan selection such that rare values may use an index while common values may use a full table scan.
PostgreSQL's query planner relies on internal statistics to choose an execution plan for a query.

Radim Marek reports that database statistics dumps are typically under 1MB even for databases with hundreds of tables and thousands of columns.
PostgreSQL 18 introduced the functions pg_restore_relation_stats() and pg_restore_attribute_stats() to restore planner statistics.
Restoring column distribution statistics can change PostgreSQL plan selection such that rare values may use an index while common values may use a full table scan.
PostgreSQL's query planner relies on internal statistics to choose an execution plan for a query.
PostgreSQL's new features allow copying production statistics into development to simulate production query plans without copying production data.

SQLite exposes query-planner statistics in the writable sqlite_stat1 table and optionally sqlite_stat4 (when compiled with SQLITE_ENABLE_STAT4), allowing users to inject alternative statistics.
SQLite documentation describes manual control of query plans using sqlite_stat tables and the ability to fix ANALYZE results.
The SQLite CLI .fullschema command outputs both schema and sqlite_statN contents to help reproduce query problems without loading multi-terabyte database files.

What specific PostgreSQL objects and statistics are covered by pg_restore_relation_stats() and pg_restore_attribute_stats(), and what is excluded?
What is the exact end-to-end workflow for exporting production statistics and restoring them safely into development (including any required privileges and operational steps)?
Under what conditions does statistics-only replication fail to reproduce production query plans (e.g., which environment differences still matter)?
How representative is the reported 'typically under 1MB' statistics-dump size across different workloads and schemas?
For SQLite, what practical guidance exists for generating, validating, and safely modifying sqlite_stat1/sqlite_stat4 to reproduce or control planner behavior?

PostgreSQL 18 planner statistics restore enables production like query plan reproduction without copying production data, which could reduce debugging cycles and improve performance testing workflows for teams running PostgreSQL.
SQLite writable planner statistics tables plus schema export can support lightweight reproductions of query planner issues, suggesting tooling and workflow demand around sharing minimal reproductions instead of full database files.

PostgreSQL 18 release notes and docs clearly enumerate which objects and statistics pg_restore_relation_stats and pg_restore_attribute_stats cover, plus an endorsed workflow for exporting and restoring stats safely.
Real world case studies show statistics only replication reliably reproduces production query plans and performance issues across environments when schema and configuration match.
SQLite documentation and community guidance standardize how to generate, validate, and modify sqlite_stat1 and sqlite_stat4 for controlled planner behavior and reproducible bug reports.

Documentation reveals pg_restore_relation_stats and pg_restore_attribute_stats exclude key statistics needed for plan parity, making statistics only replication insufficient for common performance investigations.
Frequent failures to reproduce production plans despite restored statistics due to environment differences, implying limited practical value for debugging and performance testing.
The claimed small statistics dump size is not representative across typical schemas or workloads, reducing portability and the attractiveness of stats only sharing workflows.