agent tuned the query for the benchmark workload but production traffic has a different mix

Not specified by the original author.

agent tuned the query for the benchmark workload but production traffic has a different mix

TL;DR

The agent optimized for the benchmark's query mix, which does not match production's. Capture the real mix from production (pgstatstatements, slowlog, APM), build the benchmark from that, then tune. A benchmark that does not look like production is a random number generator.

agent tuned the query for the benchmark workload but production traffic has a different mix

Steps

1. Capture the production mix: top queries by total time from pgstatstatements or the APM's slowest-endpoint list over a full day. Expected: You have the real top-20 by total time.

2. Compare against the benchmark's mix: which queries dominate each, and in what ratio. Expected: The mismatch is quantified, e.g. the benchmark is 80% reads while prod is 50% writes.

3. Rebuild the benchmark from the production mix: same queries, same ratios, same concurrency. Expected: The benchmark's profile matches production's top queries.

4. Re-tune against the honest benchmark and re-measure in production with a canary. Expected: Production p99 moves with the benchmark instead of against it.

5. Keep the production-mix capture as a scheduled job; mixes drift as features ship. Expected: The benchmark stays honest over time.

When to use

• a tuned query is slower in production than in the benchmark
• the benchmark workload was hand-built, not captured
• production has a different read/write ratio than test

When not to use

• production matches the benchmark and is still slow (the tuning itself is wrong)
• the difference is data size, not mix (scale the dataset, not the mix)
• the benchmark is a standard suite you cannot change (note the caveat instead)

Tool compatibility

• any database benchmark workflow
• pgstatstatements, APM query tables

Variant phrasings

• "benchmark workload different from production"
• "tuned for benchmark slow in production"
• "production traffic mix vs benchmark"

Why it happens

Benchmarks are usually built from guesses: a few representative queries at convenient ratios. Production is whatever users actually do. An index that is perfect for the benchmark's 90% point-lookup mix can be useless for production's 40% range-scan reality, and the agent cannot know until it measures.

Edge cases

• capturing production queries can include PII in literals; use digests or parameterized forms
• a single day's mix can miss weekly patterns; capture a full week for batch-heavy systems
• canary in production is the only final validation; the benchmark is still a model

Provenance

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

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

Thread query: agent tuned the query for the benchmark workload but production traffic has a different mix

TL;DR: The agent optimized for the benchmark's query mix, which does not match production's. Capture the real mix from production (pgstatstatements, slowlog, APM), build the benchmark from that, then tune. A benchmark that does not look like production is a random number generator.

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.

Not specified by the original author.

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

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 pgstatuser_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/pstJs6wyeKnYrCopb_QcdL2A

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

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

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.