Analyze Query Performance

  • Mongo Shell
  • Compass

Thecursor.explain("executionStats")and the db.collection.explain("executionStats") methods provide statistics aboutthe performance of a query. These statistics can be useful inmeasuring if and how a query uses an index.

db.collection.explain() provides information on the executionof other operations, such as db.collection.update(). Seedb.collection.explain() for details.

MongoDB Compass provides anExplain Plantab, which displays statistics about the performance of aquery. These statistics can be useful in measuring if and howa query uses an index.

Evaluate the Performance of a Query

Consider a collection inventory with the following documents:

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

The documents appear in MongoDB Compass as the following:

Compass Inventory collection documents

Query with No Index

  • Mongo Shell
  • Compass

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

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

The query returns the following documents:

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

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

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

explain() returns the following results:

  1. {
  2.    "queryPlanner" : {
  3.          "plannerVersion" : 1,
  4.          ...
  5.          "winningPlan" : {
  6.             "stage" : "COLLSCAN",
  7.             ...
  8.          }
  9.    },
  10.    "executionStats" : {
  11.       "executionSuccess" : true,
  12.       "nReturned" : 3,
  13.       "executionTimeMillis" : 0,
  14.       "totalKeysExamined" : 0,
  15.       "totalDocsExamined" : 10,
  16.       "executionStages" : {
  17.          "stage" : "COLLSCAN",
  18.          ...
  19.       },
  20.       ...
  21.    },
  22.    ...
  23. }

Collection scans indicate that themongod had to scan the entire collectiondocument by document to identify the results. This is agenerally expensive operation and can result in slowqueries.

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

Copy the following filter into the Compass query bar and clickFind:

  1. { quantity: { $gte: 100, $lte: 200 } }

The query returns the following documents:

Compass no index query results

To view the query plan selected:

  • Click the Explain Plan tab for thetest.inventory collection.
  • Click Explain.MongoDB Compass displays the query plan as follows:

Compass no index query plan

Note

Because we are working with such a small dataset for thepurposes of this tutorial, theActual Query Execution Time displays0 seconds, even though we are not using an index.

In a larger dataset, the difference in queryexecution time between an indexed query versus anon-indexed query would be much more substantial.

Visual Tree

  • The Query Performance Summary shows theexecution stats of the query:

    • Documents Returned displays 3 to indicatethat the query matches and returns three documents.
    • Index Keys Examined displays 0 toindicate that this query is not using an index.
    • Documents Examined displays 10 to indicatethat MongoDB had to scan ten documents (i.e. all documentsin the collection) to find the three matching documents.
  • Below the Query Performance Summary, MongoDB Compassdisplays the COLLSCAN query stage to indicate that acollection scan was used for this query.

Collection scans indicate that themongod had to scan the entire collectiondocument by document to identify the results. This is agenerally expensive operation and can result in slowqueries.

Raw JSON

The explain details can also be viewed in raw JSON format byclicking Raw JSON below the query bar:

Compass no index query plan raw JSON

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

Query with Index

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

  • Mongo Shell
  • Compass
  1. db.inventory.createIndex( { quantity: 1 } )

To view the query plan statistics, use theexplain("executionStats") method:

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

The explain() method returns the followingresults:

  1. {
  2.    "queryPlanner" : {
  3.          "plannerVersion" : 1,
  4.          ...
  5.          "winningPlan" : {
  6.                "stage" : "FETCH",
  7.                "inputStage" : {
  8.                   "stage" : "IXSCAN",
  9.                   "keyPattern" : {
  10.                      "quantity" : 1
  11.                   },
  12.                   ...
  13.                }
  14.          },
  15.          "rejectedPlans" : [ ]
  16.    },
  17.    "executionStats" : {
  18.          "executionSuccess" : true,
  19.          "nReturned" : 3,
  20.          "executionTimeMillis" : 0,
  21.          "totalKeysExamined" : 3,
  22.          "totalDocsExamined" : 3,
  23.          "executionStages" : {
  24.             ...
  25.          },
  26.          ...
  27.    },
  28.    ...
  29. }
  • Click the Indexes tab for thetest.inventory collection.
  • Click Create Index.
  • Select quantity from theSelect a field name dropdown.
  • Select 1 (asc) from the type dropdown.
  • Click Create.Create inventory index in Compass

Note

Leaving the index name field blank causes MongoDB Compass tocreate a default name for the index.

You can now see your newly created index in theIndexes tab:

Compass show new index

Return to the Explain Plan tab for theinventory collection and re-run the query fromthe previous step:

  1. { quantity: { $gte: 100, $lte: 200 } }

MongoDB Compass displays the query plan as follows:

Compass explain plan with index

Visual Tree

  • The Query Performance Summary shows theexecution stats of the query:
    • Documents Returned displays 3 to indicatethat the query matches and returns three documents.
    • Index Keys Examined displays 3to indicate that MongoDB scanned three index entries. Thenumber of keys examined match the number of documentsreturned, meaning that the mongod only hadto examine index keys to return the results. Themongod did not have to scan all of thedocuments, and only the three matching documents had to bepulled into memory. This results in a very efficientquery.
    • Documents Examined displays 3 to indicatethat MongoDB scanned three documents.
    • On the right-hand side of theQuery Performance Summary, MongoDB Compass showsthat the query used the quantity index.
  • Below the Query Performance Summary, MongoDB Compassdisplays the query stages FETCH and IXSCAN.IXSCAN indicates that themongod used an index to satisfy the querybefore exeuting the FETCH stage and retrieving thedocuments.

Raw JSON

The explain details can also be viewed in raw JSON format byclicking Raw JSON below the query bar:

Compass query plan with index raw JSON

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

When run with an index, the query scanned 3 index entriesand 3 documents to return 3 matching documents, resultingin a very efficient query.

  • Mongo Shell
  • Compass

Compare Performance of Indexes

To manually compare the performance of a query using morethan one index, you can use the hint()method in conjunction with the explain()method.

Consider the following query:

  1. db.inventory.find( {
  2.    quantity: {
  3.       $gte: 100, $lte: 300
  4.    },
  5.    type: "food"
  6. } )

The query returns the following documents:

  1. { "_id" : 2, "item" : "f2", "type" : "food", "quantity" : 100 }
  2. { "_id" : 5, "item" : "f3", "type" : "food", "quantity" : 300 }

To support the query, add a compound index. With compound indexes, the order of the fields matter.

For example, add the following two compound indexes. Thefirst index orders by quantity field first, and then thetype field. The second index orders by type first,and then the quantity field.

  1. db.inventory.createIndex( { quantity: 1, type: 1 } )
  2. db.inventory.createIndex( { type: 1, quantity: 1 } )

Evaluate the effect of the first index on the query:

  1. db.inventory.find(
  2.    { quantity: { $gte: 100, $lte: 300 }, type: "food" }
  3. ).hint({ quantity: 1, type: 1 }).explain("executionStats")

The explain() method returns the followingoutput:

  1. {
  2.    "queryPlanner" : {
  3.       ...
  4.       "winningPlan" : {
  5.          "stage" : "FETCH",
  6.          "inputStage" : {
  7.             "stage" : "IXSCAN",
  8.             "keyPattern" : {
  9.                "quantity" : 1,
  10.                "type" : 1
  11.             },
  12.             ...
  13.             }
  14.          }
  15.       },
  16.       "rejectedPlans" : [ ]
  17.    },
  18.    "executionStats" : {
  19.       "executionSuccess" : true,
  20.       "nReturned" : 2,
  21.       "executionTimeMillis" : 0,
  22.       "totalKeysExamined" : 5,
  23.       "totalDocsExamined" : 2,
  24.       "executionStages" : {
  25.       ...
  26.       }
  27.    },
  28.    ...
  29. }

MongoDB scanned 5 index keys(executionStats.totalKeysExamined) to return 2matching documents (executionStats.nReturned).

Evaluate the effect of the second index on the query:

  1. db.inventory.find(
  2.    { quantity: { $gte: 100, $lte: 300 }, type: "food" }
  3. ).hint({ type: 1, quantity: 1 }).explain("executionStats")

The explain() method returns the followingoutput:

  1. {
  2.    "queryPlanner" : {
  3.       ...
  4.       "winningPlan" : {
  5.          "stage" : "FETCH",
  6.          "inputStage" : {
  7.             "stage" : "IXSCAN",
  8.             "keyPattern" : {
  9.                "type" : 1,
  10.                "quantity" : 1
  11.             },
  12.             ...
  13.          }
  14.       },
  15.       "rejectedPlans" : [ ]
  16.    },
  17.    "executionStats" : {
  18.       "executionSuccess" : true,
  19.       "nReturned" : 2,
  20.       "executionTimeMillis" : 0,
  21.       "totalKeysExamined" : 2,
  22.       "totalDocsExamined" : 2,
  23.       "executionStages" : {
  24.          ...
  25.       }
  26.    },
  27.    ...
  28. }

MongoDB scanned 2 index keys(executionStats.totalKeysExamined) to return 2matching documents (executionStats.nReturned).

For this example query, the compound index { type: 1, quantity: 1 }is more efficient than the compound index { quantity: 1, type: 1 }.

See also

Query Optimization, Query Plans,Optimize Query Performance,Indexing Strategies

See also