## TL;DR

The query planner's choices depend on table size. On 10k rows a sequential scan is correct and the index never gets used; at 10M rows the plan is a different universe. An index validated only on tiny staging data is unvalidated. Re-test on production-scale data (sanitized snapshot or generated scale data), re-run EXPLAIN, and only then ship.

## The query

```text
agent benchmarked against the seeded staging DB with 10k rows and shipped an index that did nothing at 10M rows
```

## Steps

### 1. Confirm the scale mismatch

Check row counts: the staging table the benchmark used versus the production table. Confirm the shipped index exists in production and check whether the planner uses it (index scan counts in pg_stat_user_indexes, or EXPLAIN on the production query).

Expected: staging orders of magnitude smaller than production; the index unused in production.

### 2. Get production-scale data into the test environment

Restore a sanitized production snapshot, or generate synthetic data at production scale with a representative distribution. Then run ANALYZE so the planner has real statistics.

Expected: a test table within the same order of magnitude as production, with fresh stats.

### 3. Re-run EXPLAIN on the scaled data

Run EXPLAIN (and EXPLAIN ANALYZE where safe) for the slow query against the scaled table, with and without the shipped index.

Expected: a plan that differs from the 10k-row plan. The shipped index may be ignored, or a different index may be what the query actually needs.

### 4. Ship the correction the scaled plan calls for

Drop or keep the shipped index based on evidence, and add whatever the production-scale plan actually uses. Verify with the production query pattern, not the staging one.

Expected: an index the planner demonstrably uses at production scale.

### 5. Gate index recommendations on scale

Change the workflow: every index recommendation must include EXPLAIN output run on data at production scale, with row counts recorded. Recommendations validated only on small staging data are marked provisional, never shipped.

Expected: no index ships without a scale-representative plan attached.

## Use this when

- A shipped index shows zero usage in production
- The benchmark ran on seeded staging data far smaller than production
- EXPLAIN in staging and production disagree about the plan
- The agent validated performance without checking data scale

## Not for this skill when

- The index is used but the query is still slow (different problem: plan shape, not scale)
- Staging data genuinely matches production scale and distribution
- The planner ignores the index for selectivity reasons unrelated to size
- Write amplification from the index is the complaint (write-side analysis)

## Variant phrasings

### index did nothing in production

First suspect: it was validated at a scale where the planner never needed it.

### staging benchmark misleading

Staging lies by scale, by distribution, and by stats freshness. Fix all three before trusting it.

### EXPLAIN differs between staging and prod

The plans differ because the inputs differ. Make the inputs match.

## Why it happens

Cost-based planners choose the cheapest plan for the data they see. On a 10k-row table that fits in memory, a sequential scan is genuinely cheapest and indexes are decoration; at 10M rows the same query wants an index seek. The agent benchmarked the decoration, saw a green checkmark, and shipped it. The benchmark was honest about what it measured and wrong about what it meant, because the meaning of a plan is scale-dependent.

## Edge cases

- Distribution matters as much as row count: uniform synthetic data can validate an index that skewed production data never uses. Match the distribution, not just the count.
- Stale statistics on the restored snapshot: a fresh restore without ANALYZE gives the planner no stats, producing a third, equally fictional plan. Always ANALYZE after loading.
- Partial production data URIs restoring 10 percent of rows preserves the row count problem at a smaller multiple. Scale needs the full order of magnitude.
- The index that "did nothing" may still cost writes: an unused index still slows every insert and update. Drop confirmed-unused indexes rather than leaving them.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Js6wyeKnYr_Copb_QcdL2A