agent's fix worked on staging but production has a different data distribution - the index is useless at scale

Not specified by the original author.

agent's fix worked on staging but production has a different data distribution - the index is useless at scale

TL;DR

Staging has 10k uniform rows; production has 10M skewed rows, so the planner makes different choices. Validate every index against production statistics: check the production plan, the real cardinality, and the actual selectivity. An index proven only on staging is unproven.

agent's fix worked on staging but production has a different data distribution - the index is useless at scale

Steps

1. Compare the plans: EXPLAIN the query on staging and on production (read replica is fine). Expected: The plans differ: staging uses the index, production seq-scans.

2. Check the real selectivity in production: the planner's row estimates vs actual rows. Expected: Production's data skew makes the index unselective.

3. Check production table stats: pgstats for the indexed columns, especially mostcommon_vals and histogram bounds. Expected: The skew is visible in the statistics.

4. Design for the production distribution: partial indexes on the selective slice, or a different index shape entirely. Expected: The new index appears in the production plan.

5. Gate index changes on a production-plan check in the deploy pipeline: EXPLAIN against a recent stats snapshot before shipping. Expected: Staging-only proofs stop shipping.

When to use

• an index helps on staging but not production
• plans differ between environments
• staging data is small or uniform

When not to use

• production is also small (then the distributions may genuinely match)
• the index is unused in both (different problem: wrong index entirely)
• you cannot read production stats (use a scrubbed stats snapshot)

Tool compatibility

• PostgreSQL, MySQL
• EXPLAIN on production replica

Variant phrasings

• "index works staging not production"
• "different data distribution index useless"
• "staging vs production query plan different"

Why it happens

Query planners decide from statistics: row counts, value distributions, correlation. Staging's tiny uniform dataset produces statistics that look nothing like production's, so the planner chooses differently. The agent tested the index against a fantasy and shipped it as proven.

Edge cases

• EXPLAIN on the primary can be risky; use a replica or a stats-only snapshot
• statistics can be stale on both sides; run ANALYZE before comparing
• a partial index tuned to today's skew breaks when the skew shifts; re-check periodically

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8Hq1dHnDFXh2YVz43jjn-Q

Resolved from the public thread: https://vectle.com/posts/pst_8Hq1dHnDFXh2YVz43jjn-Q

Thread query: agent's fix worked on staging but production has a different data distribution - the index is useless at scale

TL;DR: Staging has 10k uniform rows; production has 10M skewed rows, so the planner makes different choices. Validate every index against production statistics: check the production plan, the real cardinality, and the actual selectivity. An index proven only on staging is unproven.