MongoDB - interpret information on the query plan for the db.collection.find() method

To interpret information on the query plan for the db.collection.find() method in MongoDB, use the explain() method. This provides detailed insights into query execution including index usage and performance characteristics.

Syntax

db.collection.find(query).explain()
db.collection.find(query).explain("executionStats")

Sample Data

Create a collection with an index and insert sample documents:

db.demo637.createIndex({ClientName: 1});
db.demo637.insertMany([
    {ClientName: "John"},
    {ClientName: "Bob"},
    {ClientName: "Johnson"}
]);

{
    "createdCollectionAutomatically": true,
    "numIndexesBefore": 1,
    "numIndexesAfter": 2,
    "ok": 1
}

Display all documents from the collection:

db.demo637.find();

{ "_id" : ObjectId("5e9c26916c954c74be91e6db"), "ClientName" : "John" }
{ "_id" : ObjectId("5e9c26936c954c74be91e6dc"), "ClientName" : "Bob" }
{ "_id" : ObjectId("5e9c269d6c954c74be91e6dd"), "ClientName" : "Johnson" }

Case 1: Regex Query (Middle Match)

Query using regular expression to find names containing "john":

db.demo637.find({ClientName: /john/}).explain();

{
    "queryPlanner": {
        "plannerVersion": 1,
        "namespace": "test.demo637",
        "parsedQuery": {
            "ClientName": {
                "$regex": "john"
            }
        },
        "winningPlan": {
            "stage": "FETCH",
            "inputStage": {
                "stage": "IXSCAN",
                "filter": {
                    "ClientName": {
                        "$regex": "john"
                    }
                },
                "indexName": "ClientName_1",
                "direction": "forward"
            }
        }
    }
}

Case 2: Regex Query (Prefix Match)

Query using anchored regular expression to find names starting with "john":

db.demo637.find({ClientName: /^john/}).explain();

{
    "queryPlanner": {
        "plannerVersion": 1,
        "namespace": "test.demo637",
        "parsedQuery": {
            "ClientName": {
                "$regex": "^john"
            }
        },
        "winningPlan": {
            "stage": "FETCH",
            "inputStage": {
                "stage": "IXSCAN",
                "indexName": "ClientName_1",
                "indexBounds": {
                    "ClientName": [
                        "["john", "joho")",
                        "[/^john/, /^john/]"
                    ]
                }
            }
        }
    }
}

Key Points

• IXSCAN indicates index usage, while COLLSCAN means collection scan
• indexBounds shows the range of values the query examines
• Prefix regex patterns (/^pattern/) can use index bounds more efficiently
• Use explain("executionStats") for detailed performance metrics

Conclusion

The explain() method reveals query execution plans, showing whether indexes are used effectively. Prefix regex queries perform better than middle-match patterns due to more efficient index bounds.