Index Selection: The ESR Rule

A compound index on {a: 1, b: 1, c: 1} is not the same as {b: 1, a: 1, c: 1}. Field order in compound indexes determines whether the query can use the index for equality matching, sorting, or range scanning. The wrong order forces MongoDB to perform an in-memory sort, scan excessive keys, or both.

The ESR rule provides the field ordering that minimizes work for the most common query patterns:

1. Equality fields first: fields used with $eq, $in
2. Sort fields next: fields used in .sort()
3. Range fields last: fields used with $gt, $lt, $gte, $lte

Why Order Matters

Consider the dashboard query: “Find readings for sensor-00042, sorted by timestamp descending, where temperature is above 30.”

collection.find(Filters.and(
    Filters.eq("sensorId", "sensor-00042"),        // Equality
    Filters.gt("temperature", 30.0)                 // Range
)).sort(Sorts.descending("ts"))                     // Sort
  .limit(50);

Index A: {sensorId: 1, ts: -1, temperature: 1} (ESR order)

// explain() output with ESR index
{
  "winningPlan": {
    "stage": "LIMIT",
    "inputStage": {
      "stage": "FETCH",
      "filter": { "temperature": { "$gt": 30 } },
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "sensorId": 1, "ts": -1, "temperature": 1 },
        "indexBounds": {
          "sensorId": ["[\"sensor-00042\", \"sensor-00042\"]"],
          "ts": ["[MaxKey, MinKey]"],
          "temperature": ["(30, inf.0]"]
        }
      }
    }
  },
  "executionStats": {
    "totalKeysExamined": 127,
    "totalDocsExamined": 50,
    "executionTimeMillis": 2
  }
}

MongoDB uses the index to match sensorId (equality), traverse in ts order (sort), and then FETCH documents to apply the temperature filter. It examines 127 keys and 50 documents. No in-memory sort.

Index B: {sensorId: 1, temperature: 1, ts: -1} (E-R-S, wrong order)

// explain() output with wrong index order
{
  "winningPlan": {
    "stage": "SORT",           // In-memory sort required
    "sortPattern": { "ts": -1 },
    "memoryUsageBytes": 4200000,
    "inputStage": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "sensorId": 1, "temperature": 1, "ts": -1 },
        "indexBounds": {
          "sensorId": ["[\"sensor-00042\", \"sensor-00042\"]"],
          "temperature": ["(30, inf.0]"],
          "ts": ["[MaxKey, MinKey]"]
        }
      }
    }
  },
  "executionStats": {
    "totalKeysExamined": 8500,
    "totalDocsExamined": 8500,
    "executionTimeMillis": 45
  }
}

MongoDB uses the index for sensorId (equality) and temperature (range), but the sort field ts comes after the range field. The index cannot provide sorted output after a range scan, because the range scan breaks the sort ordering. MongoDB must scan all 8,500 matching keys, fetch all 8,500 documents, sort them in memory, and then return the top 50.

The result: 2ms vs 45ms. 127 keys examined vs 8,500.