Reproducing Production Query Plans in Staging

The migration added an index and dropped an old one that “nothing used”. In staging, every important query still used an index scan afterwards. In production, the checkout query flipped to a sequential scan over forty million rows the moment the old index disappeared, and p99 latency went from 8 ms to 3 seconds. Nobody made a mistake reading the plans; the staging plans were simply not the production plans. A query planner chooses based on table statistics, configuration and data distribution, and a staging database with a tenth of the data, default settings and fresh statistics will make different choices. This guide shows how to make staging predict production’s plans closely enough that index and schema changes can be validated before they ship. It is a specific application of Environment Parity Strategies.

Symptom / Error Signatures

Plan disparity usually shows up only after a deploy:

• After a migration that adds or drops an index, pg_stat_statements shows a query’s mean_exec_time jumping by an order of magnitude in production while staging load tests were flat.
• EXPLAIN for the same query shows Index Scan in staging and Seq Scan or a different join order in production.
• Estimated row counts in staging’s plan (rows=) are orders of magnitude off production’s actual row counts.
• On MySQL, EXPLAIN FORMAT=JSON shows a different access_type or key between environments, or optimizer_trace reveals different cost estimates.
• A dropped index that pg_stat_user_indexes.idx_scan reported as unused in staging was used in production.

Root Cause Analysis

Both PostgreSQL and MySQL use cost-based optimizers: they estimate the cost of candidate plans and pick the cheapest. Estimates come from statistics — table row counts, the fraction of NULLs, most-common values and histograms per column, and physical correlation between column order and table order — combined with configuration that sets the relative cost of random and sequential I/O and how much memory the plan may use. A plan is only as reproducible as those inputs.

Volume matters less than people expect, and distribution and settings matter more. A staging database with the same statistics and settings as production will usually choose the same plan even with less data, which is why the most effective techniques transplant statistics and configuration rather than copying full datasets.

Immediate Mitigation

1. Capture the production plan before you change anything. Run EXPLAIN (without ANALYZE, so the query does not execute) on a production replica for each query the migration could affect, and save the output as the reference.

-- PostgreSQL · read-only · run on a production replica · EXPLAIN without ANALYZE does not execute the query
EXPLAIN (FORMAT TEXT, SETTINGS)
SELECT o.id, o.total FROM orders o
WHERE o.customer_id = 42 AND o.status = 'pending'
ORDER BY o.created_at DESC LIMIT 20;

The SETTINGS option (PostgreSQL 12+) prints every non-default planner setting that affected the plan — the quickest way to spot a configuration difference.

2. Copy planner settings to staging. Match random_page_cost, effective_cache_size, work_mem, default_statistics_target and any per-column statistics targets. Settings can be applied per database so staging’s other workloads are unaffected.

-- PostgreSQL · staging only · superuser or database owner
-- WARNING: values must mirror production's, not be tuned for staging's hardware.
ALTER DATABASE app SET random_page_cost = 1.1;
ALTER DATABASE app SET effective_cache_size = '48GB';
ALTER DATABASE app SET work_mem = '32MB';
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;
-- ROLLBACK PATH: ALTER DATABASE app RESET ALL;

3. Test hypothetical indexes without building them. The hypopg extension lets you create an index that exists only for the planner, so you can see whether production’s planner would use it — on a production replica, without any build cost.

-- PostgreSQL · requires the hypopg extension · hypothetical indexes live only in this session
CREATE EXTENSION IF NOT EXISTS hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders (customer_id, status, created_at DESC)');
EXPLAIN SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;
SELECT hypopg_reset();

4. On MySQL, align optimizer switches and build histograms. Compare SELECT @@optimizer_switch between environments and create histograms on skewed, unindexed columns that appear in filters.

-- MySQL 8.0 · staging and production · ANALYZE TABLE ... UPDATE HISTOGRAM reads the table, schedule off-peak
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, region WITH 256 BUCKETS;
EXPLAIN FORMAT=TREE SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'pending';

Permanent Fix / Long-Term Pattern

Make plan parity a property of staging rather than a one-off effort. Keep planner configuration in the same configuration management as production and apply it to staging automatically. Refresh staging data from production with anonymisation that preserves distribution — skew, NULL fractions and correlations — as described in seeding anonymized production data into staging, and run ANALYZE afterwards with the same statistics targets. Where full refreshes are too expensive, transplant statistics instead: PostgreSQL 18 added functions for restoring relation and column statistics so a schema-only copy can plan like production, and MySQL histograms can be recreated from production’s information_schema.COLUMN_STATISTICS.

Then add plan checks to the migration pipeline. For each migration that adds or drops an index, run EXPLAIN for a curated list of critical queries before and after in staging, and fail if a plan changes from an index scan to a sequential scan on a large table. Use pg_stat_statements on production to build that list from the queries that actually matter, as in tracking schema migration metrics and SLOs. Before dropping any index, confirm on production — not staging — that idx_scan has not increased over a full business cycle, as covered in dropping indexes online without blocking queries.

Verification Checklist

• EXPLAIN (SETTINGS) shows the same non-default planner settings in staging and on a production replica.
• Row estimates in staging plans for critical queries are within the same order of magnitude as production actuals.
• Staging data preserves the skew and NULL fraction of the columns used in critical filters.
• The pipeline compares before-and-after plans for critical queries on every index migration.
• Index drops are justified by production idx_scan data over a full business cycle.

Frequently Asked Questions

Do I need a full production-sized copy for plans to match? Usually not. Planners are driven by statistics and settings more than raw size, so a smaller dataset with production-like distribution, matching settings and analysed statistics reproduces most plans. Full-size copies are still valuable for timing and lock-duration tests.

Is it safe to run EXPLAIN on production? Plain EXPLAIN only plans the query and does not execute it, so it is safe and cheap. EXPLAIN ANALYZE executes the query, including any writes, and should be run on production only for read-only queries and ideally on a replica.

What is hypopg and is it safe on a replica? It is a PostgreSQL extension that creates hypothetical indexes visible only to the planner in the current session. Nothing is written to disk, so it is safe on a replica, provided the extension is installed there — which requires installing it on the primary, since replicas mirror the primary’s catalog.

Why did an index look unused in staging but was used in production? Because staging’s workload and statistics differ. Queries that run in production may never run in staging, and planner choices differ with data distribution. Always base index-drop decisions on production usage counters.