Postgres query plans without production data

A query returns the right rows in CI in 40 ms, ships, and takes 4.8 seconds in production. Nothing about the SQL changed, the schema is identical, and the index definitions match. What changed is the data: the planner saw 100 rows in CI and 50 million in production, and it made different decisions.

A passing test suite is not evidence about production performance. The variable almost nobody controls is pg_statistic: the table PostgreSQL's planner consults to decide how many rows a predicate matches, and therefore whether an index is worth the trouble.

There are two ways to make a test database plan like production. One is new in PostgreSQL 18 and ships the statistics. The other ships the rows. They solve different halves of the problem.

Two reasons a dev plan is not a prod plan

The distinction matters because each cause has a different fix, and people routinely reach for the wrong one.

Row count. The planner believes a table is large or small. At 100 rows a sequential scan beats any index, so the index the production query uses never gets exercised in CI. PostgreSQL does not create indexes on foreign key columns automatically, which makes this vivid: an unindexed FK join costs nothing at 100 rows and seconds at a million.

Selectivity. The planner believes a value is common or rare. WHERE status = 'cancelled' matches 2% of your production orders, so production reaches for an index. Your seed script drew statuses uniformly at random, so locally it matches 25%, and the planner correctly chooses a sequential scan. The estimate is right about the data you generated and wrong about the data you ship.

Row count is fixed by generating more rows. Selectivity is only fixed by generating rows whose value frequencies match. Uniform random data at a million rows still has the wrong shape, and a million uniformly distributed status values will produce a plan production will never pick.

Route A: ship the statistics

PostgreSQL 18 added functions that write optimizer statistics directly into the catalog: pg_restore_relation_stats for table-level numbers, pg_restore_attribute_stats for column-level MCV lists, histograms, and correlation. pg_dump grew a --statistics-only flag that emits nothing but calls to those functions.

The workflow is short:

pg_dump --schema-only     -d production_db > schema.sql
pg_dump --statistics-only -d production_db > stats.sql
createdb test_db && psql -d test_db -f schema.sql
psql -d test_db -f stats.sql

The artifact is small. Hundreds of tables and thousands of columns of statistics fit in well under 1 MB of plain SQL, against a production database that might be hundreds of gigabytes. That asymmetry is the whole appeal.

Four things to know before you rely on it:

• The rows are not there. Injected statistics describe data that does not exist. The planner will now pick the production plan, and then run it against a table with no rows. This is fine for reading a plan. It is not a test database: joins, foreign keys, aggregates, and window functions have nothing to operate on.

• Autovacuum eventually erases your work. The autovacuum launcher runs ANALYZE when a table accumulates enough modified rows, and ANALYZE recomputes statistics from the data actually on disk. That overwrites what you injected. Teams testing this turn it off per table:

  ALTER TABLE orders SET (autovacuum_enabled = false);

  That is the right call for a database you only read plans from. It is a slow-drifting lie for any database you keep writing to.

• Absolute costs still shrink. The planner checks the real size of the file on disk and scales reltuples and relpages proportionally. Inject 50 million rows into a 74-page table and you get estimates in the thousands, not the millions. boringSQL demonstrates this on the PostgreSQL 18 functions. The ratios between estimates survive the scaling, and ratios are what decide plan shape, so the flip you were chasing usually still happens. The numbers in the corners of the plan do not match production.

• You need a privilege and a pipeline. The restore functions require MAINTAIN on the target table. In a pipeline that is GRANT pg_maintain TO ci_service_account, which covers every table in the database without handing out superuser. And you have to dump from production on a schedule, which puts a production-derived artifact in every environment that loads it.

Extended statistics created with CREATE STATISTICS are not covered: the PostgreSQL 18 release notes state plainly that extended statistics are not preserved. Multivariate correlations and column-group dependencies still need ANALYZE against real data.

Route B: ship the rows

The alternative is to generate data whose statistics come out correct on their own, so that ANALYZE reports production's shape because the rows genuinely have it.

PostgreSQL already computes the statistics you need and stores them where anything can read them. pg_stats holds most_common_vals and most_common_freqs per column: the values that appear most often, and the fraction of the table each one occupies.

Weavori reads exactly those two columns from your source database and draws generated values in the same proportions.

SELECT attname, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

 attname |     most_common_vals      |     most_common_freqs
---------+---------------------------+---------------------------
 status  | {paid,pending,shipped,cancelled} | {0.82,0.11,0.05,0.02}

Generate 200,000 orders and you get roughly 164,000 paid, 22,000 pending, 10,000 shipped, 4,000 cancelled. ANALYZE reads those rows and rebuilds the same MCV list. The planner sees the same selectivity as production, and this time it is not a lie: the data really is skewed that way.

Then autovacuum works for you instead of against you. Anything it recomputes from your generated rows keeps matching, because the rows carry the shape. Foreign keys stay intact while you do it, since parents generate before children.

The ten-minute comparison

This is the experiment worth running on your own schema, because it takes the argument out of my hands.

Pick a source database that already has production's shape: a replica, or a staging environment that gets real traffic. Then generate the same table twice into two targets, and change one thing.

--no-sampling skips source statistics entirely and draws from type and column-name rules alone. The second run is the default path: read pg_stats, weight values accordingly. Both create tables in the target and stream rows in via COPY.

Now look at the statistics each one produced, and at what the planner does with them:

ANALYZE orders;
 
SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE status = 'cancelled' ORDER BY placed_at DESC LIMIT 20;

In the flat database, cancelled is roughly one value in four, and the planner treats a quarter of the table as a legitimate fraction to scan. In the sampled database it is 2%, and the estimate tightens by an order of magnitude, which is usually where the plan changes. You will see it in the estimated row count before you see it in the operator choice.

Which route, when

If you are reviewing a plan change in a pull request, route A is lighter and faster. If you want a database your tests can actually run against, route B is the only one that gives you both the plan and the rows. The two are not mutually exclusive, and a team could sample distributions at generation time and still inject production histograms for numeric columns.

Honest boundaries

The volume case

Selectivity is one half. Raw volume is the other, and it has its own reproducible recipe in the cookbook: Recipe 04, query performance at volume.

It builds a four-million-row database across customers, orders, order_items, and products, with every foreign key intact, runs one report query with no indexes on the FK columns, then adds the two indexes Postgres did not create for you. On the reference run: 4.8 seconds before, 320 ms after. Reproduce it on your own hardware, the schema, the generator config, and both queries are in the repo.

That recipe demonstrates the row-count half of the problem: a query's cost depends on the volume and shape of the data, not on whether the data is real.

Summary

1. Name the cause before fixing it. Wrong row counts and wrong selectivity produce different plans and need different fixes.
2. PostgreSQL 18 lets you ship statistics with pg_dump --statistics-only. Small, fast, and only valid for read-only plan review: autovacuum overwrites it, and no rows means no joins.
3. You can ship the rows instead. Sample most_common_vals and most_common_freqs at generation time, run ANALYZE, and the planner rebuilds production's numbers because the data genuinely has them.
4. Prove it on your schema. Generate twice, once with --no-sampling, and compare pg_stats and estimated rows.

Start with your own staging database:

weavori estimate shows what a run will produce before anything is written. For the flags, exit codes, and output formats, see the generate reference. For how dependency ordering and sampling work internally, see How it works.

• Why distributions matter: Postgres test data without seed scripts
• Postgres synthetic data, explained
• How to generate realistic test data for PostgreSQL
• Large-scale generation guide