Navigation

Analyze Query Performance

The cursor.explain("executionStats") and the db.collection.explain("executionStats") methods provide statistics about the performance of a query. These statistics can be useful in measuring if and how a query uses an index.

db.collection.explain() provides information on the execution of other operations, such as db.collection.update(). See db.collection.explain() for details.

Consider a collection inventory with the following documents:

{ "_id" : 1, "item" : "f1", type: "food", quantity: 500 }
{ "_id" : 2, "item" : "f2", type: "food", quantity: 100 }
{ "_id" : 3, "item" : "p1", type: "paper", quantity: 200 }
{ "_id" : 4, "item" : "p2", type: "paper", quantity: 150 }
{ "_id" : 5, "item" : "f3", type: "food", quantity: 300 }
{ "_id" : 6, "item" : "t1", type: "toys", quantity: 500 }
{ "_id" : 7, "item" : "a1", type: "apparel", quantity: 250 }
{ "_id" : 8, "item" : "a2", type: "apparel", quantity: 400 }
{ "_id" : 9, "item" : "t2", type: "toys", quantity: 50 }
{ "_id" : 10, "item" : "f4", type: "food", quantity: 75 }

The documents appear in MongoDB Compass as the following:

Compass Inventory collection documents

The following query retrieves documents where the quantity field has a value between 100 and 200, inclusive:

db.inventory.find( { quantity: { $gte: 100, $lte: 200 } } )

The query returns the following documents:

{ "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
{ "_id" : 3, "item" : "p1", "type" : "paper", "quantity" : 200 }
{ "_id" : 4, "item" : "p2", "type" : "paper", "quantity" : 150 }

To view the query plan selected, chain the cursor.explain("executionStats") cursor method to the end of the find command:

db.inventory.find(
{ quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")

explain() returns the following results:

{
"queryPlanner" : {
"plannerVersion" : 1,
...
"winningPlan" : {
"stage" : "COLLSCAN",
...
}
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 10,
"executionStages" : {
"stage" : "COLLSCAN",
...
},
...
},
...
}
  • queryPlanner.winningPlan.stage displays COLLSCAN to indicate a collection scan.

    Collection scans indicate that the mongod had to scan the entire collection document by document to identify the results. This is a generally expensive operation and can result in slow queries.

  • executionStats.nReturned displays 3 to indicate that the query matches and returns three documents.
  • executionStats.totalKeysExamined displays 0 to indicate that this is query is not using an index.
  • executionStats.totalDocsExamined displays 10 to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents.

The difference between the number of matching documents and the number of examined documents may suggest that, to improve efficiency, the query might benefit from the use of an index.

To support the query on the quantity field, add an index on the quantity field:

db.inventory.createIndex( { quantity: 1 } )

To view the query plan statistics, use the explain() method:

db.inventory.find(
{ quantity: { $gte: 100, $lte: 200 } }
).explain("executionStats")

The explain() method returns the following results:

{
"queryPlanner" : {
"plannerVersion" : 1,
...
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"quantity" : 1
},
...
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
...
},
...
},
...
}

Without the index, the query would scan the whole collection of 10 documents to return 3 matching documents. The query also had to scan the entirety of each document, potentially pulling them into memory. This results in an expensive and potentially slow query operation.

When run with an index, the query scanned 3 index entries and 3 documents to return 3 matching documents, resulting in a very efficient query.

Give Feedback