MongoDB: cursor.explain() method

Last update on August 19 2022 21:50:43 (UTC/GMT +8 hours)

cursor.explain

The cursor.explain() method is used to provide information on the query plan for thedb.collection.find()method.

Syntax:

cursor.explain(verbosity)

Parameters:

Name	Description	Required / Optional	Type
verbose	Specifies whether to consider the effects of the cursor.skip() and cursor.limit() methods in the count. By default, the count() method ignores the effects of the cursor.skip() and cursor.limit(). Set applySkipLimit to true to consider the effect of these methods.	Optional	string

Return

The explain() method returns a document with the query plan and, optionally, the execution statistics.

Sample document in the restaurants collection:


{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

Example: db.collection.explain() method using queryPlanner mode

The following example runs in queryPlanner verbosity mode to return the query planning information for the specified count() operation:

db.restaurants.explain().find( { "cuisine": "Bakery", "address.zipcode": "10462" } ).count();

Output:

> db.restaurants.explain().find( { "cuisine": "Bakery", "address.zipcode": "10462" } ).count();
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.restaurants",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "address.zipcode" : {
                                                "$eq" : "10462"
                                        }
                                },
                                {
                                        "cuisine" : {
                                                "$eq" : "Bakery"
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COUNT",
                        "inputStage" : {
                                "stage" : "COLLSCAN",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "address.zipcode" : {
                                                                "$eq" : "10462"
                                                        }
                                                },
                                                {
                                                        "cuisine" : {
                                                                "$eq" : "Bakery"
                                                        }
                                                }
                                        ]
                                },
                                "direction" : "forward"
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "server",
                "port" : 27017,
                "version" : "3.0.7",
                "gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
        },
        "ok" : 1
}

Example: db.collection.explain() method using executionStats Mode

The following example runs db.collection.explain() in executionStats verbosity mode to return the query planning and execution information for the specified find() operation:


db.restaurants.explain("executionStats").find(
   { "cuisine": 1, "borough": "Manhattan"} 
);

Output:

> db.restaurants.explain("executionStats").find(
...    { "cuisine": 1, "borough": "Manhattan"}
... );
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.restaurants",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "borough" : {
                                                "$eq" : "Manhattan"
                                        }
                                },
                                {
                                        "cuisine" : {
                                                "$eq" : 1
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "borough" : {
                                                        "$eq" : "Manhattan"
                                                }
                                        },
                                        {
                                                "cuisine" : {
                                                        "$eq" : 1
                                                }
                                        }
                                ]
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 0,
                "executionTimeMillis" : 86,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 25359,
                "executionStages" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "borough" : {
                                                        "$eq" : "Manhattan"
                                                }
                                        },
                                        {
                                                "cuisine" : {
                                                        "$eq" : 1
                                                }
                                        }
                                ]
                        },
                        "nReturned" : 0,
                        "executionTimeMillisEstimate" : 50,
                        "works" : 25465,
                        "advanced" : 0,
                        "needTime" : 25360,
                        "needFetch" : 104,
                        "saveState" : 245,
                        "restoreState" : 245,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "direction" : "forward",
                        "docsExamined" : 25359
                }
        },
        "serverInfo" : {
                "host" : "server",
                "port" : 27017,
                "version" : "3.0.7",
                "gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
        },
        "ok" : 1
}

Retrieve the restaurants data from here

Behavior

Verbosity Modes

On a sharded cluster, count() can result in an inaccurate count if orphaned documents exist or if a chunk migration is in progress.

queryPlanner Mode

MongoDB runs the query optimizer to choose the winning plan for the operation under evaluation. cursor.explain() returns the queryPlanner information for the evaluated method.

executionStats Mode

cursor.explain() returns the queryPlanner and executionStats information for the evaluated method. However, executionStats does not provide query execution information for the rejected plans.

allPlansExecution Mode

cursor.explain() returns the queryPlanner and executionStats information for the evaluated method. The executionStats includes the completed query execution information for the winning plan.

db.collection.explain().find()

db.collection.explain().find() is similar to db.collection.find().explain() with the following key differences:

The db.collection.explain().find() construct allows for the additional chaining of query modifiers.
The db.collection.explain().find() returns a cursor, which requires a call to .next(), or its alias .finish(), to return the explain() results.

Previous: cursor.count() method
Next: cursor.forEach() method