Designing a MongoDB compound index comes down to three decisions: what order the keys go in, whether the query can be answered without touching documents, and whether the planner will actually pick the index once you are in production. The first decides how many index keys get scanned, the second decides how many documents get fetched after that, and the third decides whether the first two conclusions hold up over time. Most teams internalize only the first one — and they internalize it as a law. MongoDB's own documentation is titled The ESR (Equality, Sort, Range) Guideline. Guideline, not rule. The same page hands you the counterexample: when the range predicate is selective enough, the correct order is ERS.

Only the E is a hard constraint

The hard layer: equality fields must all come first. The reason is mechanical — putting equality fields first keeps the remaining index fields in sorted order. An equality predicate collapses a key to a single point, so everything after it stays contiguous and ordered. Break that and every downstream guarantee about ordering and bounds evaporates.

The relative order among equality fields does not matter to the planner — they are all point lookups. It matters to you, because of index prefixes. An index on {tenantId: 1, status: 1, createdAt: -1} serves a query filtering on tenantId alone; the reverse arrangement cannot. Prefix reuse is the only free lunch compound indexes offer, so order equality keys by "which field is most often queried on its own," not by selectivity.

The soft layer is whether sort or range comes next. The documentation's actual wording: if avoiding in-memory sorts is critical, place sort fields before range fields (ESR); if your range predicate is very selective, put it before sort fields (ERS). These are not stylistic alternatives — they are two different bills. ESR removes a blocking sort by scanning more index keys than strictly necessary; the range key sits last, so its bounds become a filter applied across an already-ordered scan. ERS buys tight index bounds and pays for them with a blocking sort.

The deciding question is how many rows survive the range predicate, and whether all of them need ordering. Paginated queries with a limit are almost always ESR: the index supplies order, so the scan stops at N matches and the tail of the range is never read.

One category error shows up constantly: the docs list range predicates as $gte, $lt, $ne, $nin, and $regex. $ne and $nin are range predicates. Treating "not equal to X" as equality and putting it at the front of the index yields an IXSCAN that walks nearly the whole index.

A worked case

An order-list endpoint filters by tenant and status, constrains a time window, and returns the 50 largest orders:

db.orders.find({
  tenantId: "t_9021",                 // equality
  status: "shipped",                  // equality
  createdAt: { $gte: from, $lt: to }  // range
}).sort({ amount: -1 }).limit(50)

The ESR index is {tenantId: 1, status: 1, amount: -1, createdAt: 1}. The equality keys pin the scan to one contiguous stretch; within it amount is already descending; the scan discards keys outside the time window and stops at 50. No SORT stage, and no dependency on how large the window is.

The ERS index is {tenantId: 1, status: 1, createdAt: 1, amount: -1}. The window is now part of the index bounds, so the candidate set is minimal — but amount ordering is scrambled across it, and every candidate must be sorted in memory.

Which wins depends entirely on the window. "Last 24 hours" against a tenant with hundreds of thousands of orders per quarter favors ERS decisively. "Last 90 days," which barely filters, favors ESR's early termination just as decisively. There is no static answer — the same measure-it-on-your-own-data dynamic that governs tuning HNSW on the vector side. Build both, hint() each, and compare on production-shaped data.

$in changes category at 201 elements

This is the most operationally dangerous rule on that page. An $in with fewer than 201 elements is treated as equality: the planner "explodes" it into one index scan per value, each individually ordered, so the index can still provide sort order. At 201 elements or more it is treated as a range predicate and belongs wherever your range fields go.

The corresponding server parameter is internalQueryMaxScansToExplode, default 200. Explain tells you whether you crossed the line: queryPlanner.maxScansToExplodeReached.

The production failure looks like this. During a canary the $in carries 20 IDs, the plan explodes, the index supplies ordering, p99 is flat. Traffic ramps, an upstream service raises its batch size to 300, and the same query grows a SORT stage. Latency jumps an order of magnitude. The index did not change. The query shape did not change. Only the length of an array did.

If the length is controlled by a caller, cap it in the application and chunk above the cap. Raising internalQueryMaxScansToExplode trades one cliff for another — it makes the planner's workload proportional to caller-supplied input.

What actually makes a sort index-provided

"Add an index on the sort field" is not the rule. The documented rules are stricter:

  1. Direction must match the key pattern exactly, or invert it entirely. An index {a: 1, b: -1} supports sort({a: 1, b: -1}) and sort({a: -1, b: 1}). It does not support sort({a: 1, b: 1}). There is no partial credit.
  2. Index prefixes provide sorts. With {a:1, b:1, c:1, d:1}, sort({a:1, b:1}) works.
  3. Non-prefix subsets need equality to bridge the gap. The index supports a sort on a subset of keys only if the query has equality conditions on all preceding index keys. find({a: 5}).sort({b: 1, c: 1}) works; so does find({b: 3, a: 4}).sort({c: 1}) — predicate order in the query document is irrelevant.
  4. The counterexample worth memorizing: find({a: {$gt: 2}}).sort({c: 1}) cannot use the index for sorting. a is a range rather than equality, b is unconstrained, and the chain breaks there.

Multikey indexes add a rule of their own. Sorting on an array field covered by a multikey index produces an in-memory sort unless the index bounds for all sort fields are [MinKey, MaxKey] and no bounds on any multikey field share a path prefix with the sort pattern. In practice: do not plan on index-provided sorts over arrays.

Note also the structural limit on compound multikey indexes: within a single document, at most one indexed field may be an array. You cannot create the index if more than one specified field is an array, and once it exists, an insert that would make two of its fields arrays fails outright. That is a schema decision disguised as an index decision.

Covered queries fail silently

A query is covered when all of these hold: every field the query uses is in one index, every field returned is in that same index, and no field in the query is compared to null — both {field: null} and {field: {$eq: null}} disqualify it.

The parts that bite:

  • _id is usually not part of the index, and the docs are explicit: unless _id is in the index, the projection must exclude it with _id: 0. Add a field to a projection, forget to add it to the index, and coverage disappears with no error — totalDocsExamined simply goes from 0 to nReturned.
  • Multikey indexes can cover queries, provided the projection does not return the array field and the query contains no $elemMatch. A covering multikey index is therefore necessarily compound.
  • On a sharded collection queried through mongos, the index must contain the shard key — SHARDING_FILTER needs it to discard orphans.
  • Not every index type can cover. Geospatial indexes cannot.

The explain evidence is a PROJECTION_COVERED stage plus totalDocsExamined: 0. Because coverage is invisible in application behavior, assert it in a test rather than documenting it on a wiki page — a three-line integration test catches the projection change that code review will not.

Reading explain: three numbers, then two shapes

Start with executionStats: nReturned, totalKeysExamined, totalDocsExamined. The documented ideal is all three equal. How they diverge tells you which problem you have:

Shape Diagnosis Fix
keys ≈ docs ≈ returned Index is precise Leave it alone
keys ≈ docs ≫ returned Filtering happens after FETCH — a predicate field is not in the index Add that field to the index
keys ≫ docs Bounds are too loose, or multikey fan-out Check key order and indexBounds
docs = 0, PROJECTION_COVERED present Covered query Protect it with a test
COLLSCAN with large docs examined No usable index Create one

Then look at two shapes in the plan tree.

First, is there a SORT stage? That is a blocking sort. Its default ceiling is 100 MB — internalQueryMaxBlockingSortMemoryUsageBytes, i.e. 104857600 bytes — and exceeding it raises Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. A query passing today at 80 MB fails next quarter at 110 MB: a blocking sort is a latent outage, not just slow.

Second, read indexBounds on the IXSCAN. Any key whose bounds are ["MinKey", "MaxKey"] contributes nothing to narrowing the scan — it exists only to supply ordering or coverage. This is the most direct evidence available about whether your ESR ordering is right. When ESR is correct, the sort key's bounds are typically the full domain while the range key's bounds are tight. A range key sitting at [MinKey, MaxKey] means the predicate is not being pushed into bounds at all.

Version-specific fields worth knowing, from the explain results reference: in 8.0, queryHash is deprecated in favor of planCacheShapeHash, and new EXPRESS_IXSCAN / EXPRESS_DELETE / EXPRESS_UPDATE stages appear for simple point operations. In 8.2, every stage that can spill to disk reports standardized spills, spilledBytes, spilledRecords, spilledDataStorageSize. In 8.3, executionStats.peakTrackedMemBytes. For memory-bound sorts those carry far more signal than executionTimeMillis, which is polluted by cache state and concurrent load.

Finally, the part that sends people down the wrong path: explain() ignores every existing plan cache entry and does not create new ones. What it shows is "which plan would win if we planned this right now," not necessarily what is running in production. For that, use $planCacheStats — it must be the first pipeline stage, the same positional constraint $vectorSearch and $search carry inside a $rankFusion hybrid search pipeline — and read isActive, works, and planCacheKey. It returns entries from a single node per shard by default, so run it on each node you care about.

MongoDB 8.3 changed how plans are chosen

Starting in MongoDB 8.3, multi-planning with a cost-based ranker (CBR) backup is the default plan selection mechanism for eligible queries. The classic multi-planner still runs its trial period first; only if it fails to settle on a suitable plan does the server decide whether to continue or hand off to CBR. The documentation is explicit that CBR is currently invoked for only a small subset of queries, so do not treat it as a substitute for index design.

From 8.3.3, explain output can include CBR's estimates: costEstimate, cardinalityEstimate, numKeysEstimate, numDocsEstimate, and estimatesMetadata.ceSource with values sampling, heuristics, mixed, metadata, or code. Read ceSource first — heuristics means the estimate is a rule of thumb, not anything derived from your data.

Version context, since minor releases now ship to self-managed deployments: 8.0 is the current major release (GA October 2024), while 8.2 and 8.3 are minor releases, with 8.3 released in May 2026. The release notes warn that minor releases may not support some features, including Atlas Live Migration and mongosync. If you depend on those, stay on the major.

When an index makes things slower

  • Low-selectivity leading key. {status: 1, createdAt: -1} where status has three values splits the index into three chunks, each still near-collection-scale. The only remaining benefit is ordering — possibly worth it, but filtering is not happening.
  • Too many candidate indexes. Every new plan cache shape triggers a multi-planning trial, and more candidates make that trial more expensive. MongoDB 8.2's performance notes list "reduced query multi-planning costs" as a shipped improvement — a decent sign this is a measured cost, not a theoretical worry.
  • Write amplification and cache competition. Every index is maintained on write and occupies memory and disk. Documented limits: 64 indexes per collection, 32 fields per compound index. Index builds default to a 200 MB budget per createIndexes command (maxIndexBuildMemoryUsageMegabytes) shared equally among all indexes in that command — ten indexes in one command means 20 MB each, with the rest spilling to _tmp.
  • Hidden indexes do not save writes. hidden: true (requires featureCompatibilityVersion 6.0 or greater) only makes the index invisible to the planner. Hidden indexes are still updated on writes and still consume disk and memory; unique constraints are still enforced and TTL indexes still expire documents. It validates a drop; it does not save cost. You cannot hide _id, and you cannot hint() a hidden index.
  • Expecting two single-field indexes to add up to a compound one. The indexing strategies docs state plainly that MongoDB generally uses only one index per query, with the exception that each clause of an $or may use a different index.

For cleanup, $indexStats is the right tool, with caveats. accesses.ops counts user operations on that node only and excludes internal work such as TTL deletions and chunk migrations — run it on every node, the same per-node discipline that change stream resume debugging demands. Counters reset on mongod restart, on index drop and recreation, and when collMod modifies the index. ops: 0 is not sufficient evidence to drop: confirm accesses.since spans a full business cycle including monthly reporting paths, then hide the index for a week or two, then drop.

Pinning a plan, the supported way

When you need to force an index choice in production, do not reach for index filters — they are deprecated as of MongoDB 8.0. Use query settings via setQuerySettings, introduced in 8.0: they persist across restarts and apply cluster-wide, whereas index filters are per-node and evaporate on restart. They restrict the planner with indexHints.allowedIndexes, pin the query framework to classic or sbe, and — the underrated one — set reject: true on a query shape to block a bad query that already shipped, without a deploy.

The order to do this in

  1. Take real query shapes from the slow query log, not from application code; the shapes that hurt are rarely the ones you expect.
  2. Put all equality fields first, most commonly standalone-queried field leftmost.
  3. Paginated with a limit → start from ESR. Very selective range plus a full sort → start from ERS. Build both, hint() each, compare the three numbers.
  4. Check whether any $in can cross 201 elements under production load.
  5. Cover the query if you can, and assert totalDocsExamined: 0 in a test so it stays covered.
  6. Before dropping any index, hide it and wait through a full business cycle.
  7. If a plan must be pinned, use query settings — not index filters, not hint() scattered through application code.