The ESR rule: how compound indexes really get used

Every MongoDB engineer hits a moment where they've added the index, the field is in the query, and yet explain() shows a COLLSCAN. The answer is almost always the same: the index doesn't match the shape of the query, and the planner is too honest to pretend otherwise.

What the planner actually does

Before MongoDB picks an index, it builds a set of candidate plans, runs each one for a few hundred work units, and keeps whichever returns the most documents per unit of work. That's it — there's no cost model in the Postgres sense, just "who got the most done in the shortest time".

That race is decided by a single number: totalKeysExamined. Every plan that walks fewer index entries to satisfy your filter wins. The ESR rule is just a memorable shorthand for "order your index so the planner walks the fewest keys."

Note

TL;DR.

Put equality fields first, the sort field next, and range fields last. Any other order forces the planner to either scan extra index entries or do an in-memory sort.

ESR: equality, sort, range

Take this query — a perfectly reasonable "show me the most recent failed payments for one merchant" thing you'd write on day one:

db.payments.find({
  merchantId: "acme_42",   // equality
  status:     "failed",    // equality
  amount:     { $gte: 10000 }, // range
}).sort({ createdAt: -1 })    // sort
  .limit(50);

Three different kinds of predicate: two equality filters, one sort, one range. A naive index on { createdAt, merchantId, status, amount } looks right — every queried field is there. It is wrong. Here's why.

The shape of an index

A compound index is a B-tree where every entry is the concatenation of the indexed fields, in order. Querying it is exactly like looking up a name in a phone book sorted by (last, first, middle). If you know the last name, the book narrows to a page. If you also know the first, you narrow to a line. Skip the last name, and the book is useless.

Anatomy of a compound index

Let's run the same query against the right index and watch what changes. ESR says equality first, then sort, then range — so:

db.payments.createIndex({
  merchantId:  1,   // E
  status:      1,   // E
  createdAt:  -1,   // S
  amount:      1    // R
}, { name: "payments_esr_v1" });

With this index, the planner walks the B-tree to ("acme_42", "failed", *, *), then reads keys in createdAt order, descending. The limit(50) kicks in after 50 keys. It does 50 key examinations. Total. On a 40-million document collection.

Why field order matters (and the planner won't fix it for you)

Swap two fields in the index — put createdAt before status — and the same query suddenly examines 12,400 keys to return 50 documents. Why? Because the index is now sorted primarily by date across all statuses. The planner has to either:

• Walk the index date-descending and discard every non-failed entry it sees, or
• Walk every status: "failed" entry and sort the result in memory.

Both are losing strategies. The first is what explain() will actually show; the second is what it falls back to when you exceed the 32MB sort buffer.

Reading explain() output

Run explain("executionStats") and you get back a small JSON tree. Skip 90% of it. These three numbers are what you actually want:

{
  "nReturned":           50,
  "totalKeysExamined":   50,   // ← the magic number
  "totalDocsExamined":   50,   // ideally = nReturned
  "executionTimeMillis":  3,
  "executionStages": { "stage": "IXSCAN", "..." : "..." }
}

The ratio totalKeysExamined / nReturned is your selectivity score. 1.0 is perfect. 10 is fine. 1,000 means the planner is walking a lot of index entries it shouldn't have to, and there's an ESR violation hiding somewhere.

Five common ESR pitfalls

1. An $in with many values acts like a range. A twenty-element $in creates twenty equality bounds the planner must walk. Put it before sort, but expect higher key counts than a true equality.
2. $exists: true isn't an equality. It's a non-null range. Treat it like one.
3. Sort direction matters. An index on { createdAt: 1 } can serve { createdAt: -1 } — but only as the full sort key, never as part of a compound sort with mixed directions.
4. Descending range, ascending sort = in-memory sort. The planner can't reverse part of an index.
5. Adding fields to the end is free; reordering is not. Build a new index under a new name, ship to one secondary, validate with $indexStats, then drop the old one. Never alter in place.

Wrapping up

ESR isn't a law of physics — it's a heuristic that holds up because compound indexes are sorted left-to-right and the planner can't reorder them on the fly. Once that fact lives in your head, half of "why is this query slow" becomes a 30-second explain() read.

Next week we'll do the same dissection for $lookup pipelines — where the rules bend and a 'covered' join is a thing you can actually engineer.