Tune a Slow Workload by Changing Query Shape or Indexes and Re-Measure the Evidence

Learning objectives

1. The disciplined tuning loop

A defensible slow-query experiment keeps four things fixed: business result, dataset, query shape, and measurement method. Then it changes one variable—here, one compound index. This avoids the common anti-pattern of simultaneously changing projection, pagination, hardware, indexes, and load, then attributing the result to whichever change sounds plausible.

1. Capture the exact query and result contract.
2. Record baseline explain evidence and a latency distribution.
3. Apply one reversible change.
4. Re-run the identical query and measurement.
5. Compare work, latency distribution, storage/write cost, and correctness.
6. Keep or roll back based on measured production-relevant evidence.

2. Seed a fixed 50,000-order workload

const c=db.orders_ch12_l5;c.drop();const base=new Date("2026-01-01T00:00:00Z");let b=[];for (let i=0;i<50000;i++) {  b.push({_id:i,tenantId:`tenant-${i%50}`,status:["new","paid","processing","shipped","closed"][i%5],createdAt:new Date(base.getTime()+i*60000),totalCents:500+(i%15000),channel:["web","mobile","partner"][i%3]});  if (b.length===1000) { c.insertMany(b); b=[]; }}if (b.length) c.insertMany(b);printjson({count:c.countDocuments({}),indexes:c.getIndexes().map(x=>x.name)});

The target query asks for the newest 100 processing orders for tenant-17 after a fixed timestamp. The fixture and query remain unchanged throughout the experiment.

3. Baseline, one index change, and re-measurement

The script first explains and benchmarks the unindexed secondary query. It then creates exactly one compound index matching equality predicates plus sort, explains again, and re-runs the same latency sampler. The five warm-up iterations are excluded from the measurement set; that does not eliminate cache effects, but it makes the procedure explicit and repeatable.

const c=db.orders_ch12_l5;const filter={tenantId:"tenant-17",status:"processing",createdAt:{$gte:new Date("2026-01-10T00:00:00Z")}};const sort={createdAt:-1};const projection={_id:0,createdAt:1,totalCents:1,channel:1};function stagesOf(explainDoc) {  const out=[];  const seen=new Set();  function walk(x) {    if (!x || typeof x !== "object" || seen.has(x)) return;    seen.add(x);    if (typeof x.stage === "string") out.push(x.stage);    for (const v of Object.values(x)) {      if (v && typeof v === "object") walk(v);    }  }  walk(explainDoc.queryPlanner?.winningPlan);  return [...new Set(out)];}function summary(explainDoc) {  return {    stages: stagesOf(explainDoc),    nReturned: explainDoc.executionStats?.nReturned,    totalKeysExamined: explainDoc.executionStats?.totalKeysExamined,    totalDocsExamined: explainDoc.executionStats?.totalDocsExamined,    executionTimeMillis: explainDoc.executionStats?.executionTimeMillis,    planCacheShapeHash: explainDoc.queryPlanner?.planCacheShapeHash,    planCacheKey: explainDoc.queryPlanner?.planCacheKey,    queryShapeHash: explainDoc.queryShapeHash  };}function runQuery(){ return c.find(filter,projection).sort(sort).limit(100).comment("ch12-l5-controlled").toArray(); }function percentile(sorted,p){ if(!sorted.length)return null; return sorted[Math.min(sorted.length-1,Math.ceil(p*sorted.length)-1)]; }function sample(label,rounds=40){  for(let i=0;i<5;i++) runQuery();  const ms=[];  for(let i=0;i<rounds;i++){ const t=Date.now(); runQuery(); ms.push(Date.now()-t); }  ms.sort((a,b)=>a-b);  const out={label,rounds,p50:percentile(ms,.50),p95:percentile(ms,.95),p99:percentile(ms,.99),min:ms[0],max:ms[ms.length-1]};  printjson(out); return out;}const beforeExplain=c.find(filter,projection).sort(sort).limit(100).explain("executionStats");print("BEFORE"); printjson(summary(beforeExplain));const beforeLatency=sample("before");c.createIndex({tenantId:1,status:1,createdAt:-1},{name:"idx_tenant_status_created"});const afterExplain=c.find(filter,projection).sort(sort).limit(100).explain("executionStats");print("AFTER"); printjson(summary(afterExplain));const afterLatency=sample("after");printjson({sameResultCount:runQuery().length,beforeLatency,afterLatency});

4. Optional driver-side latency distribution

The application cares about client-observed latency, not only server execution time. PyMongo adds driver/network/deserialization behavior to the measurement. Run the same script once before creating the index and once afterward, or remove/recreate the disposable index between runs. The script is intentionally simple; serious benchmarking also controls concurrency, warm/cold cache, coordinated omission, CPU/I/O saturation, and network placement.

from pymongo import MongoClientfrom time import perf_counter_nsfrom statistics import medianclient=MongoClient("mongodb://127.0.0.1:27076/?directConnection=true", serverSelectionTimeoutMS=3000)c=client.atlasmart.orders_ch12_l5flt={"tenantId":"tenant-17","status":"processing","createdAt":{"$gte":__import__('datetime').datetime(2026,1,10)}}def once():    return list(c.find(flt,{"_id":0,"createdAt":1,"totalCents":1,"channel":1},comment="ch12-l5-pymongo").sort("createdAt",-1).limit(100))for _ in range(5): once()ms=[]for _ in range(50):    t=perf_counter_ns(); once(); ms.append((perf_counter_ns()-t)/1_000_000)ms.sort()def pct(p): return ms[min(len(ms)-1, int((p*len(ms)+0.999999))-1)]print({"p50_ms":pct(.50),"p95_ms":pct(.95),"p99_ms":pct(.99),"min_ms":ms[0],"max_ms":ms[-1]})client.close()

5. Deliberately wrong: improve the average by changing three things

Suppose the “after” test adds an index, reduces the result limit from 100 to 10, and runs after restarting Docker. The average may improve, but causality is lost. Another misleading practice is reporting only the mean; a few severe outliers can be hidden. The repair is a one-variable experiment with p50/p95/p99, explain work counters, the same result assertion, and a written test matrix.

6. Production judgment and rollback

The compound index is useful only if its read improvement justifies index storage, cache pressure, build cost, and extra maintenance on inserts/updates. On replica sets, builds and plan behavior involve members; on sharded collections, routing and per-shard work must be measured separately. A local standalone cannot prove production tail latency, replica lag, or shard balance.

Before deployment, capture production-like query distributions, index size estimates, build-window constraints, and rollback steps. After deployment, compare the same shape hashes and service-level latency/error signals. If the expected improvement does not materialize or write/storage cost is unacceptable, hide/drop the index through the evidence-driven lifecycle from Chapter 10 rather than keeping it “just in case.”

Bridge to Chapter 13. Performance tuning should not distort correctness boundaries. The next chapter asks when single-document atomicity is enough and when a multi-document transaction is actually required.

docker rm -f atlasmart-mongo-ch12-l5docker volume rm atlasmart-mongo-ch12-l5-data

Authoritative references

• MongoDB 8.3 release notes — Current stable series, patch-sensitive behavior, 8.3 query-planning/profiling additions.
• Explain command — Verbosity modes and explain behavior.
• Explain results — Plan stages, execution statistics, query-shape hashes, and version-dependent output.
• Query plans — Candidate selection, cost-based ranker backup, plan-cache states, and cache invalidation.
• PlanCache.list() — Current mongosh plan-cache inspection interface.
• $planCacheStats — Plan-cache documents and engine-dependent output.
• Database profiler — Profiler levels, overhead, filters, thresholds, and security considerations.
• $currentOp — Preferred live-operation inspection stage.
• Slow query monitoring — Profiler/currentOp diagnostic workflow.
• $queryStats — Managed-deployment query-shape statistics and stability/availability caveats.
• Query shapes — MongoDB 8.x query-shape and plan-cache-shape terminology.
• PyMongo documentation — Driver behavior and versioned client API reference.
• Indexing strategies — Evidence-driven indexing and write/read tradeoffs.