The explain plan I read before the CEO called back!

Saqib Ameen Subhan — Tue, 04 Aug 2026 05:09:05 +0000

The escalation reached our CEO before it reached my team.

An enterprise customer, one of the largest on the platform, was seeing latency spikes bad enough that they raised it commercially, not technically. I had about twenty minutes before a call with their leadership, so I did the only thing that ever works in that situation: I stopped guessing and pulled the profiler.

db.setProfilingLevel(1, { slowms: 500 })
// ...wait one cycle...
db.system.profile.find().sort({ millis: -1 }).limit(3)

I set the threshold high on purpose. The default log already captures slow queries >100ms, but the profiler gives me something the log doesn't: structured records I can sort and aggregate.

One aggregation stood out. It ran every five minutes, scanned a collection of roughly 200 million documents, and had no index supporting it. Every five minutes, a full collection scan, on shared infrastructure. The "noisy neighbor" wasn't a mystery, it was on a schedule.

How I actually read an explain plan

Run the slow query with execution stats:

eg:
db.orders.explain("executionStats").aggregate([
  { $match: { accountId: "ACME", status: "active", created: { $gte: ISODate("2026-08-01") } } },
  { $sort: { created: -1 } }
])

I look at exactly three numbers before anything else:

"nReturned" : 214, "totalKeysExamined" : 0, "totalDocsExamined" : 198236411

The ratio is the diagnosis. Healthy queries examine roughly as many keys as they return. This one examined 198 million documents to return 214. totalKeysExamined: 0 means no index was touched at all. The winning plan was COLLSCAN.

That's the whole skill, honestly. Everything else in the explain output is detail. If totalDocsExaminedis orders of magnitude above nReturned, the query is doing the database's job by hand.

ESR: the field order that makes or breaks the index

The fix was not "add an index." It was "add the right index" and the difference is field order. MongoDB compound indexes follow what the docs call the ESR rule - Equality, Sort, Range:

Equality predicates first (accountId, status)
Sort fields next (created as sort key)
Range predicates last (created: { $gte: ... } - here sort and range share a field, which is the friendly case)

db.orders.createIndex({ accountId: 1, status: 1, created: -1 })

Get the order wrong, range before sort and the index still gets used, but MongoDB has to fetch and sort in memory. You'll see it in the plan as a SORT stage instead of the sort being absorbed by index order. On 200 million documents, an in-memory sort is not a detail.

One refinement we used because the query only ever cared about active records:

db.orders.createIndex(
  { accountId: 1, created: -1 },
  { partialFilterExpression: { status: "active" } }
)

A partial index keeps only the entries that match the filter. Smaller index, less RAM, faster writes and the query planner picks it up as long as the query includes the same predicate.

The lab, if you want to see it yourself

Five minutes on a local mongod:

for (let i = 0; i < 500000; i++) {
  db.orders.insertOne({ accountId: "A" + (i % 50), status: i % 3 ? "active" : "closed",
                        created: new Date(Date.now() - i * 60000), amount: i % 997 })
}
db.orders.explain("executionStats").find(
  { accountId: "A7", status: "active" }).sort({ created: -1 })
// note totalDocsExamined, then:
db.orders.createIndex({ accountId: 1, status: 1, created: -1 })
// run the explain again and compare

The before/after on totalDocsExamined makes the argument better than any slide deck.

What happened with the customer

Root cause explained in plain language on the call. I described the collection as a library with no catalogue, where every request meant walking every aisle. Partial index shipped the same day. Latency resolved within six hours of the escalation, and two weeks later I presented them a health report instead of an apology. They renewed.

The profiler and three numbers in an explain plan. That's the twenty-minute version of fourteen years.

DEV Community: Saqib Ameen Subhan

The explain plan I read before the CEO called back!