Diagnosing and Resolving Slow Queries in MongoDB

The Problem: High Latency and Resource Spikes

When a MongoDB query that previously performed well suddenly slows down, or a new query causes CPU spikes, the issue is rarely a "slow database" in general. It is typically a mismatch between the query pattern and the available indexes, or a resource bottleneck where the working set—the data and indexes accessed most frequently—no longer fits in RAM.

The primary goal of this diagnostic process is to move from a COLLSCAN (Collection Scan), where MongoDB reads every document in a collection, to an IXSCAN (Index Scan), where it targets only the necessary documents.

Quick Diagnostic Matrix

Symptom	Likely Cause	Key Metric to Check
High CPU, slow reads	Missing or inefficient index	totalDocsExamined > nReturned
Slow writes, high disk I/O	Over-indexing / Index bloat	indexStats / Storage size
Intermittent latency spikes	WiredTiger cache pressure	Page faults / Cache eviction rates
Queries hanging/blocking	Write contention/Locking	currentOp() output

Step-by-Step Diagnostic Workflow

1. Identify the Execution Plan

Use the explain() method to see how MongoDB is retrieving the data. Run this in the MongoDB Shell (mongosh) with the permissions of the user executing the query.

db.collection.find({ "status": "active", "age": { "$gt": 25 } }).explain("executionStats")

Risk: Using executionStats actually runs the query. Do not run this on an unindexed query against a multi-billion document collection in production without a limit, as it may lock resources.

2. Analyze the Winning Plan

Look at the winningPlan section of the output. You are looking for the stage key:

• COLLSCAN: The database is scanning the entire collection. This is the most common cause of slow queries.
• IXSCAN: The database is using an index. If the query is still slow, the index may be inefficient.
• FETCH: The database is retrieving the actual documents from disk after finding the pointers in the index.

3. Evaluate Index Selectivity

Compare totalDocsExamined with nReturned. If you return 10 documents but examine 100,000, your index is not selective enough. A highly optimized query should have a ratio close to 1:1.

Fixing the Performance Gap

Applying the ESR Rule

When creating compound indexes (indexes on multiple fields), follow the ESR (Equality, Sort, Range) rule to minimize the number of documents scanned:

1. Equality: Fields used for exact matches (e.g., status: "active").
2. Sort: Fields used to order the results.
3. Range: Fields used for inequalities (e.g., age: { $gt: 25 }).

Example: For a query filtering by userId (equality), sorting by timestamp (sort), and filtering by amount (range), the index should be:

db.collection.createIndex({ userId: 1, timestamp: 1, amount: 1 })

Handling Write Contention

If the explain plan shows an IXSCAN but the query is still slow, check for concurrent write locks. Run the following command in the shell:

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

If you see long-running write operations or waitingForLock flags, the read latency is a symptom of write contention, not a missing index.

Verification and Limitations

To verify the fix, re-run the explain("executionStats") command. Confirm that:

• The stage is now IXSCAN.
• totalDocsExamined has decreased significantly.
• The execution time (executionTimeMillis) is within acceptable thresholds.

Limitations

• Index Overhead: Every new index slows down insert and update operations because the index must be updated synchronously.
• RAM Constraints: If your total index size exceeds the available WiredTiger cache, MongoDB will swap to disk, causing "page faults" and degrading performance regardless of index quality.

Rollback Procedure

If a newly created index causes write performance to degrade or consumes too much disk space, remove it using the following command:

db.collection.dropIndex("index_name_1")

Ensure you identify the correct index name via db.collection.getIndexes() before dropping.