MongoDB: db.collection.explain() method

db.collection.explain

The db.collection.explain() method is used to returns information on the query execution of various methods like aggregate(); count(); find(); group(); remove(); and update() .

Syntax:

db.collection.explain()

Parameters:

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: MongoDB: 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": "Italian", "address.zipcode": "10075" } ).count();

Here is the output

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

Example: MongoDB: db.collection.explain() method using executionStatsMode

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": "Brooklyn"} 
);

Here is the output

> db.restaurants.explain("executionStats").find(
   { "cuisine": 1, "borough": "Brooklyn"} 
);
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.restaurants",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "borough" : {
                                                "$eq" : "Brooklyn"
                                        }
                                },
                                {
                                        "cuisine" : {
                                                "$eq" : 1
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "borough" : {
                                                        "$eq" : "Brooklyn"
                                                }
                                        },
                                        {
                                                "cuisine" : {
                                                        "$eq" : 1
                                                }
                                        }
                                ]
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 0,
                "executionTimeMillis" : 94,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 25359,
                "executionStages" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "borough" : {
                                                        "$eq" : "Brooklyn"
                                                }
                                        },
                                        {
                                                "cuisine" : {
                                                        "$eq" : 1
                                                }
                                        }
                                ]
                        },
                        "nReturned" : 0,
                        "executionTimeMillisEstimate" : 70,
                        "works" : 25468,
                        "advanced" : 0,
                        "needTime" : 25360,
                        "needFetch" : 107,
                        "saveState" : 246,
                        "restoreState" : 246,
                        "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

The behavior of db.collection.explain() and the amount of information returned depend on the verbosity mode.

queryPlanner Mode

By default, db.collection.explain() runs in queryPlanner verbosity mode.

executionStats Mode

MongoDB runs the query optimizer to choose the winning plan, executes the winning plan to completion, and returns statistics describing the execution of the winning plan.

allPlansExecution Mode

MongoDB runs the query optimizer to choose the winning plan and executes the winning plan to completion. In "allPlansExecution" mode, MongoDB returns statistics describing the execution of the winning plan as well as statistics for the other candidate plans captured during plan selection.

Previous: db.collection.dropIndexes() method
Next: db.collection.find() method