MongoDB Explain: Diagnose Index Usage and Fix Slow Queries

Quick Answer

• Use explain("executionStats") to verify whether your query uses an index or performs a full collection scan (COLLSCAN). This is the single most important diagnostic step when queries are slow.
• First check: Run db.collection.find(yourFilter).explain("executionStats") and examine stage (should be IXSCAN, not COLLSCAN) and totalDocsExamined vs nReturned — a large gap means poor index selectivity.
• Minimal fix: If you see COLLSCAN, create an index matching your query filter fields. For compound indexes, respect the leftmost prefix rule (order of fields in the index must match query filter order).
• Safe for production: Use the default queryPlanner mode (no query execution) for zero-impact analysis. Use executionStats only on secondary replicas or test environments.
• Applies to: MongoDB 3.0+ (all modern versions). Works in mongosh, MongoDB Compass, and all official drivers.

What Problem It Solves

When a MongoDB query becomes slow, the root cause is almost always one of:

• The query is performing a full collection scan (COLLSCAN) instead of using an available index.
• The index exists but is not selective enough — the database scans many documents to return few results.
• The query uses operators that cannot leverage the existing index (e.g., $regex without anchor, $nin).
• A compound index has the wrong field order, violating the leftmost prefix rule.

The explain() method reveals exactly which execution plan MongoDB chose, how many documents it examined, and whether an index was used. Without it, you are guessing.

When This Error or Setup Appears

You should run explain() analysis when:

• A query that used to be fast suddenly becomes slow (often after data growth or index changes).
• You see COLLSCAN in the MongoDB log or Database Profiler output.
• You are designing a new query and want to verify the index strategy before deploying.
• You are debugging an aggregation pipeline where $match or $sort stages are slow.
• You are tuning a RAG (Retrieval-Augmented Generation) system that queries MongoDB for context data — slow retrieval directly impacts LLM response latency.

Parameters and Environment Variables

The explain() method accepts a verbosity mode as a string parameter. There are exactly three modes:

Usage syntax:

JAVASCRIPT
// mongosh or driver
db.collection.find({ status: "active" }).explain("executionStats")

// For aggregation pipelines
db.collection.explain("executionStats").aggregate([
  { $match: { status: "active" } },
  { $group: { _id: "$category", count: { $sum: 1 } } }
])

Root Cause Analysis

When you run explain("executionStats"), the output contains a queryPlanner section and an executionStats section. Focus on these key fields:

1. stage — The Most Important Field

Located under executionStats.executionStages:

• IXSCAN — Index scan. The query used an index. Good.
• COLLSCAN — Collection scan. No index was used. This is the root cause of most slow queries.
• FETCH — The database retrieved full documents after the index scan. Expected for non-covered queries.
• SHARD_MERGE — For sharded clusters, results from multiple shards are merged.

2. totalDocsExamined vs nReturned

• If totalDocsExamined is much larger than nReturned, the index is not selective enough. For example, examining 10,000 documents to return 10 means the index filtered poorly.
• Ideal: totalDocsExamined equals nReturned (every examined document matched).

3. totalKeysExamined

The number of index entries scanned. If this is very high but nReturned is low, the index key range is too broad.

4. executionTimeMillis

Total execution time in milliseconds. Compare this before and after creating or modifying an index.

Common Pattern: COLLSCAN with Existing Index

If stage shows COLLSCAN but you know an index exists, check:

• Index field order: For a compound index { a: 1, b: 1 }, a query filtering only on b cannot use the index (leftmost prefix rule).
• Query operators: $regex without ^ anchor, $nin, $not, and $where often prevent index usage.
• Index type: Text indexes only support $text queries. Geospatial indexes only support geospatial queries.

Common Errors and Fixes

Error: MongoServerError: Authentication failed.

Cause: The MongoDB user does not have sufficient privileges to run explain().

Fix: Create a dedicated read-only user for explain analysis:

JAVASCRIPT
use your_database
db.createUser({
  user: "explain_user",
  pwd: "secure_password",
  roles: [{ role: "read", db: "your_database" }]
})

Then connect with: mongosh "mongodb://explain_user:secure_password@localhost:27017/your_database"

Error: MongoNetworkError: connection timed out

Cause: MongoDB is not reachable at the specified host/port, or network/firewall rules block the connection.

Fix:

1. Verify MongoDB is running: systemctl status mongod (Linux) or check the Windows Service.
2. Confirm the connection URI: mongodb://hostname:27017/database
3. For remote connections, ensure bindIp in mongod.conf includes the client IP or 0.0.0.0, and TLS is configured.

Error: TypeError: db.collection(...).explain is not a function

Cause: explain() is a method on the cursor object, not on the collection directly. You must call a query method first.

Fix:

JAVASCRIPT
// Correct — explain() on the cursor
db.collection.find({ status: "active" }).explain("executionStats")

// Wrong — explain() on the collection directly
// db.collection.explain("executionStats")  // This works only for aggregation

For aggregation, the syntax is different:

JAVASCRIPT
db.collection.explain("executionStats").aggregate([...])

Error: Explain output shows COLLSCAN but index exists

Cause: The index definition does not match the query pattern.

Fix:

1. List existing indexes: db.collection.getIndexes()
2. Check index field order against query filter fields.
3. Verify query operators are index-friendly (avoid $nin, unanchored $regex).
4. For compound indexes, ensure the query filters on the first field of the index.

Production Notes and Security Checks

Performance Impact

• queryPlanner mode: Safe to run on any environment, including production. It does not execute the query.
• executionStats and allPlansExecution modes: These execute the query. On large collections, this can be expensive. Never run executionStats on a production primary with large datasets. Use a secondary replica or a staging environment.
• For aggregation pipelines, executionStats runs the entire pipeline. Be especially careful with $lookup, $unwind, and $group stages that process many documents.

Security Best Practices

• Least privilege: Create a dedicated MongoDB user with only read permission on the target database. Do not use root or dbAdmin accounts for explain analysis.
• Connection security: Always use TLS/SSL for remote MongoDB connections. The URI should look like: mongodb://user:pass@host:27017/db?tls=true
• Credential management: Never hardcode credentials in configuration files. Use environment variables or a secrets manager.
• Network isolation: Restrict MongoDB port access to trusted IP ranges. Do not expose MongoDB to the public internet.

Data Consistency Considerations

• On a replica set secondary, explain() may return slightly stale data (eventual consistency). For index analysis, this is almost always acceptable because index structure is the same across replicas.
• In sharded clusters, explain() returns results from all shards. The shards field contains per-shard execution stats.

FAQ

Q: How can I analyze index usage without affecting production performance?

A: Use the default queryPlanner mode — it generates the execution plan without executing the query, so there is zero performance impact. If you need actual execution statistics, run executionStats on a secondary replica node. Alternatively, enable the MongoDB Database Profiler (db.setProfilingLevel(1, { slowms: 100 })) to capture explain output for slow queries automatically.

Q: What does it mean when totalDocsExamined is much larger than nReturned? How do I fix it?

A: This means the query scanned many documents but returned few results — the index is not selective enough. For example, scanning 10,000 documents to return 10 indicates poor filtering. Fixes:

1. Create a more specific compound index that covers all filter fields.
2. Use more restrictive query conditions (e.g., add a range filter on a date field).
3. Consider a covered query that only returns fields present in the index, eliminating the FETCH stage entirely.

Q: How do I use explain() with aggregation pipelines?

A: Use the syntax db.collection.explain("executionStats").aggregate([...]). The output shows execution stats for each pipeline stage. Focus on the $match and $sort stages — they should show IXSCAN if indexes are used. For $lookup stages, ensure the foreign field has an index. The aggregation explain output is more complex than find() explain, so examine each stage's stage field individually.