Analyze Query Performance
On this page
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. See
db.collection.explain()
for details.
MongoDB Compass provides an Explain Plan tab, which displays statistics about the performance of a query. These statistics can be useful in measuring if and how a query uses an index.
Evaluate the Performance of a Query
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:
Query with No Index
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
displaysCOLLSCAN
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
displays3
to indicate that the winning query plan returns three documents.executionStats.totalKeysExamined
displays0
to indicate that this is query is not using an index.executionStats.totalDocsExamined
displays10
to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents.
The following query retrieves documents where the
quantity
field has a value between 100
and 200
,
inclusive:
Copy the following filter into the Compass query bar and click Find:
{ quantity: { $gte: 100, $lte: 200 } }
The query returns the following documents:
To view the query plan selected:
Click the Explain Plan tab for the
test.inventory
collection.Click Explain.
MongoDB Compass displays the query plan as follows:
Note
Because we are working with such a small dataset for the
purposes of this tutorial, the
Actual Query Execution Time displays
0
seconds, even though we are not using an index.
In a larger dataset, the difference in query execution time between an indexed query versus a non-indexed query would be much more substantial.
Visual Tree
The Query Performance Summary shows the execution stats of the query:
Documents Returned displays
3
to indicate that the winning query plan returns three documents.Index Keys Examined displays
0
to indicate that this query is not using an index.Documents Examined displays
10
to indicate that MongoDB had to scan ten documents (i.e. all documents in the collection) to find the three matching documents.
Below the Query Performance Summary, MongoDB Compass displays the
COLLSCAN
query stage to indicate that a collection scan was used for this query.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.
Raw JSON
The explain details can also be viewed in raw JSON format by clicking Raw JSON below the query bar:
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.
Query with 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" : { ... }, ... }, ... }
queryPlanner.winningPlan.inputStage.stage
displaysIXSCAN
to indicate index use.executionStats.nReturned
displays3
to indicate that the winning query plan returns three documents.executionStats.totalKeysExamined
displays3
to indicate that MongoDB scanned three index entries. The number of keys examined match the number of documents returned, meaning that themongod
only had to examine index keys to return the results. Themongod
did not have to scan all of the documents, and only the three matching documents had to be pulled into memory. This results in a very efficient query.executionStats.totalDocsExamined
display3
to indicate that MongoDB scanned three documents.
Click the Indexes tab for the
test.inventory
collection.Click Create Index.
Select
quantity
from the Select a field name dropdown.Select
1 (asc)
from the type dropdown.Click Create.
Note
Leaving the index name field blank causes MongoDB Compass to create a default name for the index.
You can now see your newly created index in the Indexes tab:
Return to the Explain Plan tab for the
inventory
collection and re-run the query from
the previous step:
{ quantity: { $gte: 100, $lte: 200 } }
MongoDB Compass displays the query plan as follows:
Visual Tree
The Query Performance Summary shows the execution stats of the query:
Documents Returned displays
3
to indicate that the winning query plan returns three documents.Index Keys Examined displays
3
to indicate that MongoDB scanned three index entries. The number of keys examined match the number of documents returned, meaning that themongod
only had to examine index keys to return the results. Themongod
did not have to scan all of the documents, and only the three matching documents had to be pulled into memory. This results in a very efficient query.Documents Examined displays
3
to indicate that MongoDB scanned three documents.On the right-hand side of the Query Performance Summary, MongoDB Compass shows that the query used the
quantity
index.
Below the Query Performance Summary, MongoDB Compass displays the query stages
FETCH
andIXSCAN
.IXSCAN
indicates that themongod
used an index to satisfy the query before exeuting theFETCH
stage and retrieving the documents.
Raw JSON
The explain details can also be viewed in raw JSON format by clicking Raw JSON below the query bar:
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.