Explain Plans & Profiling

You can’t fix what you can’t measure. The .explain() command is the X-Ray machine for your queries. It reveals exactly how MongoDB executed your query, how many keys it scanned, and how many documents it read.

1. How to Explain

You can append .explain("executionStats") to almost any operation.

  • Java
  • Go
// Run explain on a find query
Document explainDoc = collection.find(Filters.eq("email", "alice@example.com"))
                                .explain(ExplainVerbosity.EXECUTION_STATS);

System.out.println(explainDoc.toJson());

// Explain is not a direct method on Find in the Go driver.
// We execute it as a command "explain".
command := bson.D{
    {"explain", bson.D{
        {"find", "users"},
        {"filter", bson.D{{"email", "alice@example.com"}}},
    }},
    {"verbosity", "executionStats"},
}

var result bson.M
db.RunCommand(context.TODO(), command).Decode(&result)

2. Decoding executionStats

The output is a large JSON document. Focus on the executionStats section.

The Metrics (The Holy Trinity)

  1. nReturned: The number of documents sent back to the client.
  2. totalKeysExamined: The number of index entries the B-Tree traversed.
  3. totalDocsExamined: The number of full documents read from disk.

[!IMPORTANT] The Golden Ratio: In an ideal query, nReturned == totalKeysExamined == totalDocsExamined. If totalDocsExamined » nReturned, you are scanning too much data.

The Stages

  • COLLSCAN: Full Collection Scan. Bad.
  • IXSCAN: Index Scan. Good.
  • FETCH: Retrieving the full document from disk (using the pointer from the index).
  • SORT: In-Memory Sort. Avoid.
  • PROJECTION_COVERED: Index-Only Query. Best.

3. Interactive: Explain Plan Decoder

Paste a simplified JSON snippet (or use the presets) to diagnose the query.

Analysis will appear here...

4. The Query Plan Cache

MongoDB doesn’t run the optimizer every time.

  1. Plan Selection: The optimizer tests multiple candidate plans.
  2. Caching: The winner is cached and used for subsequent identical queries.
  3. Eviction: The cache is cleared if:
    • The collection has 1,000 writes.
    • An index is added or dropped.
    • The mongod process restarts.

If performance suddenly drops, the optimizer might have picked a sub-optimal plan. You can force a re-plan with db.collection.getPlanCache().clear().