Home / Databases / MongoDB

DatabasesTroubleshootingIntermediate

MongoDB slow queries: profiler, explain() and the ESR index rule

Find slow operations with the profiler or logs, read explain('executionStats') for COLLSCAN and in-memory sorts, and build compound indexes with the Equality-Sort-Range rule.

Deepak Bhardwaj2 min read

Applies toMongoDB 5.0–8.0, MongoDB Atlas, Amazon DocumentDB (partially), Azure Cosmos DB for MongoDB vCore (partially)

1. Find slow operations

The server logs operations slower than slowms (default 100 ms). To capture them in a collection:

db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find().sort({ millis: -1 }).limit(10).pretty()

Running right now:

db.currentOp({ active: true, secs_running: { $gt: 5 } })

On Atlas, use the Query Profiler and Performance Advisor tabs.

2. Explain the query

db.orders.find({ customerId: 42, status: "OPEN" }).sort({ createdAt: -1 }).explain("executionStats")

Look at:

Field Bad sign
winningPlan.stage contains COLLSCAN No usable index; scans the whole collection
totalDocsExamined ≫ nReturned The index isn't selective, or only part of the filter uses it
A SORT stage (not SORT_MERGE) In-memory sort, limited to 100 MB unless it spills to disk
totalKeysExamined ≫ nReturned Index order doesn't match the query

Ideal: nReturned ≈ totalKeysExamined ≈ totalDocsExamined.

3. The ESR rule for compound indexes

Order the fields: Equality → Sort → Range.

// find({customerId: 42, status: "OPEN", amount: {$gt: 100}}).sort({createdAt: -1})
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1, amount: 1 })

Putting the range field (amount) before the sort field forces an in-memory sort.

4. Build indexes safely

Since MongoDB 4.2, index builds hold an exclusive lock only briefly at the start and end. On replica sets the build runs on all members at once. Still:

  • Build big indexes off-peak and watch replication lag.
  • Check disk space first. The index needs room.
db.orders.aggregate([{ $indexStats: {} }])   // usage per index: drop unused ones

5. Other common causes

  • Regex not anchored (/term/) can't use an index efficiently. Use ^term, or Atlas Search.
  • $or needs an index for each branch.
  • Large documents / unbounded arrays make every read heavy. Use projections and keep arrays bounded.
  • Working set larger than RAM: high page faults and disk reads. Check db.serverStatus().wiredTiger.cache (bytes read into cache) and size up.

Tip

Atlas Performance Advisor suggests indexes based on real slow queries. Review them; don't apply blindly. Each index costs write performance and memory.

Related fixes