Optimizing Compound Indexes in MongoDB with the ESR Rule: Prevent 'Sort Exceeded Memory Limit' Errors - ITFROMZERO - Share tobe shared! Apply the ESR (Equality, Sort, Range) rule to design effective MongoDB compound indexes, eliminating in-memory sorting and cutting query times from 1.8s to 4ms.

Table of Contents

1. A Real-World Problem: When MongoDB Crashes Due to In-Memory Sorts

During our recent 11/11 flash sale, our MongoDB Replica Set spiked to 98% CPU utilization. Order creation and retrieval requests ground to a halt across the board. Service logs were flooded with 500 status codes and a familiar error:

Executor error during query :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not allow sorting on disk.

By default, MongoDB caps RAM usage at 100MB per sort operation (and 32MB in MongoDB 4.2 and earlier). If the dataset to be sorted exceeds this threshold without a supporting index, the engine aborts the query immediately to protect the server from memory exhaustion.

At the time, our orders collection contained roughly 14 million documents (~18GB). The development team had already indexed all three query fields: status, amount, and createdAt. Despite this, MongoDB was still pulling hundreds of thousands of records into RAM to sort them from scratch. Indexing fields in the wrong order dropped write throughput by 35% while leaving read queries painfully slow.

2. The Root Cause: Why Does the Query Still Scan RAM Despite Having Indexes?

Consider a sample document from the orders collection:

{
  _id: ObjectId("65e0a1b2c3d4e5f6a7b8c9d0"),
  status: "COMPLETED",
  amount: 250000,
  createdAt: ISODate("2026-03-01T10:00:00Z")
}

Here is the query executed by the admin dashboard:

db.orders.find({
  status: "COMPLETED",
  amount: { $gte: 100000 }
}).sort({ createdAt: -1 });

And the existing index on the database:

db.orders.createIndex({ status: 1, amount: 1, createdAt: -1 });

Running explain("executionStats") reveals how the engine actually processes the query:

"stage": "SORT",
"sortPattern": { "createdAt": -1 },
"memLimit": 104857600,
"inputStage": {
  "stage": "IXSCAN",
  "indexName": "status_1_amount_1_createdAt_-1"
}

What Does the B-Tree Structure Tell Us?

Compound indexes organize keys sequentially from left to right across the B-Tree. When you place a range comparison field like amount before createdAt, the execution flow breaks down as follows:

The engine quickly navigates to the branch matching status: "COMPLETED".
Next, it scans across the range of values where amount >= 100000. Because this is a range scan, the matching records are scattered across multiple leaf nodes.
The ordering of createdAt is fragmented within each individual amount entry. MongoDB cannot retrieve pre-sorted results directly from the index. Consequently, the engine must load all matching documents into RAM to perform a blocking in-memory sort.

3. Temporary Workarounds and Their Drawbacks

Workaround 1: Enabling the allowDiskUse Flag

A common quick fix is passing the allowDiskUse: true option:

db.orders.find({
  status: "COMPLETED",
  amount: { $gte: 100000 }
}).sort({ createdAt: -1 }).allowDiskUse(true);

The Drawback: While the 500 errors disappear, latency jumps from 60ms to over 3.2 seconds. Continuously writing temporary sort files to disk exhausts SSD IOPS, degrading overall cluster performance.

Workaround 2: Creating Multiple Single-Field Indexes

Another attempt is creating three separate indexes on status, amount, and createdAt, relying on Index Intersection.

The Drawback: MongoDB wastes extra CPU cycles merging RecordId sets. This approach inflates index storage requirements without eliminating the in-memory SORT stage.

4. The Ultimate Solution: Applying the ESR Rule

To optimize both filtering and sorting speed, field order in a compound index must strictly follow the ESR rule: Equality → Sort → Range.

How the ESR Rule Works

E – Equality: Place exact-match fields first (e.g., status, shopId). This narrows the dataset down to a single contiguous sub-tree in the B-Tree.
S – Sort: Place the sort field immediately after equality fields (in this case, createdAt). Since the dataset narrowed by the equality step is contiguous, the index cursor can traverse keys directly in the target sort order without reordering in RAM.
R – Range: Place range filter fields last (e.g., amount with $gte, $lt, or $in). The index scans records in the required sort order while filtering out entries that fall outside the range boundaries.

Implementing on Production

First, drop the old, inefficient index:

db.orders.dropIndex("status_1_amount_1_createdAt_-1");

Create the new index following the ESR formula (Equality: status, Sort: createdAt, Range: amount):

db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 });

Evaluating with executionStats

db.orders.find({
  status: "COMPLETED",
  amount: { $gte: 100000 }
}).sort({ createdAt: -1 })
  .explain("executionStats");

The updated execution plan:

"stage": "FETCH",
"inputStage": {
  "stage": "IXSCAN",
  "indexName": "status_1_createdAt_-1_amount_1",
  "direction": "forward"
}

The "stage": "SORT" node has completely vanished. MongoDB now performs an Index-assisted Sort, streaming documents in their pre-sorted index order. Query execution time dropped from 1,840ms to 4ms, and totalBytesSorted fell to 0.

Practical Caveats with the $in Operator

Syntactically, $in looks like an equality predicate. Under the hood, however, an $in condition containing multiple values (such as status: { $in: ["PENDING", "PAID", "SHIPPING"] }) behaves like a union of multiple OR operations. The index cursor must jump across distinct branches, making it harder for MongoDB to maintain a seamless sort order. Always run explain() to test { status: 1, createdAt: -1 } versus { createdAt: -1, status: 1 } based on the cardinality and distribution of your data.