The 3:00 AM Production Incident
Last quarter, our analytics dashboard ground to a halt during prime checkout hours. API response latency shot from an average of 42ms straight past 4,500ms. MongoDB replica set CPU hit 98%, paging the on-call engineer at 3:14 AM.
The culprit looked completely harmless on the surface:
db.orders.find({
status: "COMPLETED",
createdAt: { $gte: ISODate("2026-09-01T00:00:00Z") }
}).sort({ totalAmount: -1 }).limit(20);In our staging environment with 12,000 seeded documents, this call returned in under 4ms. In production, across 8.5 million rows and 62GB of collection data, it saturated our WiredTiger cache and choked disk I/O. If you seed staging data using bulk tools like toolcraft.app/en/tools/data/csv-to-json, keep sample volumes realistic. Low document counts hide index ordering bugs every time.
Diagnosing Bottlenecks with explain()
Stop guessing how MongoDB executes your filters. Pass "executionStats" directly into explain() to view actual plan execution metrics:
db.orders.find({
status: "COMPLETED",
createdAt: { $gte: ISODate("2026-09-01T00:00:00Z") }
}).sort({ totalAmount: -1 }).limit(20).explain("executionStats");Reading the Diagnostic Signals
Three warning signs stood out immediately in our trace:
stage: "COLLSCAN": A full table scan. The database read 8,520,412 documents off NVMe storage sequentially because no matching prefix index existed.totalDocsExamined: 8520412vsnReturned: 20: Healthy queries target an examined-to-returned ratio of roughly 1:1. Reading over 8.5 million records just to serve 20 payload items wastes vast memory and CPU bandwidth.stage: "SORT"without an index pipeline: MongoDB limits in-memory sorts to 100MB by default (or 32MB in older MongoDB <4.4 versions). If sorting filtered rows exceeds that buffer withoutallowDiskUse: true, the query immediately throws anExecutor error during find command: Sort exceeded memory limitfailure.
Comparing Three Indexing Strategies
When multi-attribute queries stall, teams frequently try one of three index layouts. Here is how they behave under real production traffic:
1. Multiple Single-Field Indexes
db.orders.createIndex({ status: 1 });
db.orders.createIndex({ createdAt: 1 });
db.orders.createIndex({ totalAmount: -1 });MongoDB can try index intersection (AND_SORTED), but it rarely holds up under load. The engine scans separate index B-trees, merges record identifiers in memory, and still performs a blocking in-memory sort. Meanwhile, every insert and update incurs three write overhead penalties instead of one.
2. Naive Compound Index (Range First)
// Bad ordering: range field placed before equality and sort
db.orders.createIndex({ createdAt: 1, status: 1, totalAmount: -1 });Placing createdAt at the front ruins index efficiency. Once MongoDB evaluates a range operator ($gte), it cannot walk deeper into the compound B-tree for exact status lookups. Even worse, it loses ordered navigation on totalAmount, triggering that same expensive in-memory sort.
3. ESR-Compliant Compound Index
// Order matters: Equality (status) -> Sort (totalAmount) -> Range (createdAt)
db.orders.createIndex({ status: 1, totalAmount: -1, createdAt: 1 });This layout aligns with physical B-tree structure. The query planner navigates straight to matching status: "COMPLETED" records, traverses records in exact sorted order, and stops scanning as soon as the date boundary or limit hits.
How to Apply the ESR Rule
The ESR rule stands for Equality, Sort, and Range. Arrange compound index keys in this sequence whenever queries combine multiple filter styles.
1. Equality Fields First
Put exact matches (such as tenantId, status, or userId) at the start of your index key. This immediately narrows the evaluation set to a tight, contiguous partition of the B-tree.
2. Sort Fields Second
Next, define keys specified in .sort(). By matching the requested sort direction or its exact reverse, MongoDB streams sorted rows straight off the index. The database avoids buffering records in RAM, eliminating memory pressure.
3. Range Fields Last
Place range operators ($gt, $gte, $lt, $lte) or set-inclusion filters at the end. After the engine begins a range scan across non-contiguous index blocks, subsequent index fields can no longer satisfy sort operations or point checks.
Validating the Fix in Production
We created the ESR index in the background to avoid locking write traffic:
// 1. Build the optimized index
db.orders.createIndex(
{ status: 1, totalAmount: -1, createdAt: 1 },
{ background: true }
);
// 2. Validate the query execution path
const plan = db.orders.find({
status: "COMPLETED",
createdAt: { $gte: ISODate("2026-09-01T00:00:00Z") }
}).sort({ totalAmount: -1 }).limit(20).explain("executionStats");
const stats = plan.executionStats;
print("Root Stage: " + stats.executionStages.stage);
print("Keys Examined: " + stats.totalKeysExamined);
print("Docs Examined: " + stats.totalDocsExamined);
print("Returned: " + stats.nReturned);
print("Latency (ms): " + stats.executionTimeMillis);The operational metrics shifted dramatically:
totalKeysExamineddropped from 8,520,412 down to 20.totalDocsExaminedfell from 8,520,412 down to 20 (a clean 1:1 ratio).executionTimeMillisfell from 4,510ms down to 1ms.- The memory-heavy
SORTstage disappeared, replaced by an index scan (IXSCAN) pipeline straight intoFETCH.
Replica set CPU dropped back down to 14%. Check your explain() output whenever latency ticks up, and structure your compound indexes around ESR principles.
