The ESR Rule & Query Targeting

Creating an index is easy. Creating the right index for a complex query is an art form. In MongoDB, the gold standard for index design is the ESR Rule.

1. The ESR Rule (Equality, Sort, Range)

When creating a compound index, you should arrange the fields in this specific order to maximize performance:

  1. Equality: Fields that are filtered by exact values (e.g., status: "active").
  2. Sort: Fields used to sort the results (e.g., createdAt: -1).
  3. Range: Fields filtered by ranges (e.g., $gt, $lt, $in).

Why this order? (The Genesis)

Think about how a B-Tree is traversed:

  1. Equality: We jump directly to the branch where status = "active". This massively prunes the search space.
  2. Sort: Within the “active” branch, the data is already sorted by createdAt (if we put Sort next). We can just walk the tree in order. No extra sorting needed.
  3. Range: We traverse the sorted data and stop when the Range condition (price > 100) is no longer met.

If you swap them (e.g., Range before Sort), the B-Tree gives you a range of documents that are NOT in sorted order. MongoDB must then load them all into memory and sort them manually.

2. Interactive: The ESR Builder

Build the optimal index for this query: db.users.find({ country: "US", age: { $gte: 21 } }).sort({ score: -1 })

1. Equality
2. Sort
3. Range
country: "US"
score: -1
age: $gte 21
Drag fields to the correct slots above.

3. Covered Queries

A Covered Query is the holy grail of performance. It happens when:

  1. All fields in the query filter are in the index.
  2. All fields in the query projection are in the index.

Result? MongoDB satisfies the query purely from the Index (RAM). It never touches the documents on Disk. totalDocsExamined becomes 0.

  • Java
  • Go
// Index: { username: 1, email: 1 }
// We want ONLY username and email. We must exclude _id because it's not in our index!
Document projection = new Document("username", 1).append("email", 1).append("_id", 0);

// This query is COVERED. Zero Disk I/O.
collection.find(Filters.eq("username", "jules"))
          .projection(projection)
          .forEach(doc -> System.out.println(doc));

// Index: { username: 1, email: 1 }
// Projection: Include username, email. Exclude _id.
opts := options.Find().SetProjection(bson.D{
    {"username", 1},
    {"email", 1},
    {"_id", 0}, // Crucial! _id is NOT in our custom index
})

// Covered Query
cursor, _ := collection.Find(context.TODO(), bson.D{{"username", "jules"}}, opts)

4. The 100MB Death Limit (In-Memory Sort)

If you violate the ESR rule (put Range before Sort), MongoDB cannot use the index to sort. It attempts to sort the results in memory.

  • Limit: 100 MB of RAM.
  • Consequence: If the result set exceeds 100 MB, the query fails instantly with an error.

[!CAUTION] Executor error during find command :: caused by :: Sort operation used more than the maximum 33554432 bytes of RAM (Old limit was 32MB, new is 100MB).

You can bypass this with allowDiskUse: true, but that spills data to disk, killing performance. The real fix is ESR.

5. Index Intersection

MongoDB can use two separate indexes for one query (e.g., one on status and one on age). This is called Index Intersection.

  • Reality: It is almost always slower than a single Compound Index.
  • Why: Merging two sorted lists is computationally expensive compared to traversing one tailored B-Tree.