Reading explain plans: a practical MongoDB & NoSQL guide

Why explain plans matter

You run a query, it's slow, and you have no idea why. MongoDB gives you data—sometimes thousands of documents—but that doesn't mean your code is wrong. The real culprit is often the way MongoDB executed your query. Reading explain plans: a practical MongoDB & NoSQL guide helps you understand what MongoDB actually did under the hood, so you can fix performance problems before they hit production.

An explain plan is MongoDB's window into query execution. It tells you which indexes were considered, whether they were used, how many documents were scanned, and what operations happened in what order. Without this knowledge, you're tuning blind.

COLLSCAN means no index was used

A COLLSCAN stage means MongoDB performed a collection scan—it read every single document in your collection to find matches. This is the slowest possible query pattern, especially as your data grows.

Here's a real scenario. You have a users collection with 10 million documents:

db.users.find({ email: "alice@example.com" }).explain("executionStats")

If the response includes a stage called COLLSCAN, MongoDB looked at all 10 million documents just to find one user. When you see this, your first instinct should be: does an index exist?

Let's check what the explain plan shows:

{
  "executionStages": {
    "stage": "COLLSCAN",
    "nReturned": 1,
    "totalDocsExamined": 10000000,
    "executionTimeMillis": 8432
  }
}

Notice totalDocsExamined is 10 million but we only got 1 document back. That's wasteful. Now create an index:

db.users.createIndex({ email: 1 })

Run the explain again, and you'll see IXSCAN instead. The query might drop from 8 seconds to 2 milliseconds. That's the power of understanding when MongoDB isn't using indexes.

COLLSCAN isn't always bad—if you're scanning a small collection or retrieving most of the documents anyway, it might be the cheapest option. But for queries on large collections with selective filters, it's a red flag.

IXSCAN uses an index

IXSCAN means MongoDB used an index to find documents. This is almost always faster than COLLSCAN because indexes are sorted and tree-structured. Instead of scanning millions of documents, MongoDB jumps to the right part of the tree and retrieves only what matches.

With our email index in place, the same query now looks like:

{
  "executionStages": {
    "stage": "IXSCAN",
    "nReturned": 1,
    "totalDocsExamined": 1,
    "executionTimeMillis": 2,
    "indexName": "email_1"
  }
}

Notice totalDocsExamined is now 1—MongoDB went straight to the right document using the index. This is efficient.

However, IXSCAN doesn't always mean your query is optimal. Consider a compound index scenario. You have an order collection and you query:

db.orders.find({ userId: 123, status: "pending" }).explain("executionStats")

If you have an index on userId only, MongoDB will use IXSCAN to find all orders for userId 123, then filter the results by status. That's better than COLLSCAN, but not ideal. Create a compound index instead:

db.orders.createIndex({ userId: 1, status: 1 })

Now MongoDB can use the index for both fields in a single traversal. The IXSCAN stage will be more selective, and totalDocsExamined will match nReturned more closely.

The key insight: IXSCAN is good, but the efficiency depends on how well the index matches your query predicate.

totalDocsExamined vs nReturned

The ratio between totalDocsExamined and nReturned tells you how much work MongoDB wasted on documents that didn't make it into your result set.

Ideally, they're equal or very close. If totalDocsExamined is 100,000 and nReturned is 10, MongoDB examined 90,000 documents that didn't match your filter—that's inefficient.

Here's a practical example. You query a products collection:

db.products.find({ 
  category: "electronics", 
  price: { $lt: 500 } 
}).explain("executionStats")

The response might show:

{
  "executionStages": {
    "nReturned": 342,
    "totalDocsExamined": 8500,
    "stage": "COLLSCAN"
  }
}

MongoDB examined 8,500 documents to return 342. That's about 24 documents examined per result—not great. Add an index:

db.products.createIndex({ category: 1, price: 1 })

After indexing, the same query might show:

{
  "executionStages": {
    "nReturned": 342,
    "totalDocsExamined": 342,
    "stage": "IXSCAN"
  }
}

Now totalDocsExamined equals nReturned. Every document examined was returned. That's optimal.

In production, watch for queries where this ratio is high. It often means your index isn't covering all the filter conditions, or you need a different index strategy.

SORT stage means an in-memory sort

A SORT stage in your explain plan means MongoDB sorted the results in memory after fetching them. This is expensive, especially for large result sets.

Consider this query on an orders collection:

db.orders.find({ status: "completed" }).sort({ createdAt: -1 }).explain("executionStats")

If you only have an index on status, the explain output might look like:

{
  "executionStages": {
    "stage": "SORT",
    "nReturned": 5000,
    "totalDocsExamined": 5000,
    "executionMemory": 2097152,
    "executionStages": {
      "stage": "IXSCAN",
      "indexName": "status_1"
    }
  }
}

MongoDB used the index on status to fetch 5,000 documents, then sorted them in memory by createdAt. This works, but it uses RAM and CPU.

To avoid the SORT stage, create a compound index that includes the sort field:

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

Now the index returns documents already sorted, and no SORT stage appears:

{
  "executionStages": {
    "stage": "IXSCAN",
    "nReturned": 5000,
    "totalDocsExamined": 5000,
    "indexName": "status_1_createdAt_-1"
  }
}

MongoDB has a hard limit—32 MB of RAM for in-memory sorting. If you sort a large result set without an index, the query fails with an error. You'll see this in explain plans when executionMemory approaches the limit.

Common mistakes

• Ignoring totalDocsExamined. New engineers focus only on nReturned. The gap between the two reveals index inefficiency. Always check the ratio.
• Assuming IXSCAN is always efficient. A query using an index isn't automatically optimized. Review totalDocsExamined relative to nReturned, and check for SORT or other expensive stages downstream.
• Creating too many indexes. Each index slows writes and consumes disk space. Index based on your most common queries, measured with explain() on real workloads.
• Not looking at executionStats mode. Use explain("executionStats") or explain("allPlansExecution"), not just explain(). The latter shows the plan without actual execution numbers, which are less useful for diagnosis.
• Forgetting about projection. Even with a good index, if your query fetches documents just to throw away most fields, you're wasting I/O. Use projection to limit what MongoDB returns: .find({...}, { field1: 1, field2: 1 }).

Key takeaways

• COLLSCAN on large collections is a performance killer. Always check if an index exists for your query's main filters.
• IXSCAN is better than COLLSCAN, but verify that totalDocsExamined is close to nReturned; a high ratio means the index isn't selective enough.
• The gap between totalDocsExamined and nReturned reveals wasted work. A 1:1 ratio is ideal.
• SORT stages indicate in-memory sorting. Use compound indexes with sort fields to avoid them on large result sets.
• Always run explain with "executionStats" mode in development and staging. It's the only way to know what MongoDB is actually doing.
• Index based on your real query patterns, measured with explain(). Guessing costs you in write performance and disk space.