# 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.

```text
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: pg_stats for the indexed columns, especially most_common_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