This is the 14th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

In MongoDB, the Explain command tells the MongoDB server to return statistics about how it executed the query, rather than the result of the query. Mongoose queries have an Explain () method that converts queries to explain().

const Character = mongoose.model('Character', mongoose.Schema({
  name: String.age: Number
}))
​
await Character.create([
  { name: 'O.O'.age: 18 },
  { name: 'D.O'.age: 19 },
  { name: 'K.O'.age: 28 },
  { name: 'O.K'.age: 29 },
  { name: 'LAY'.age: 24}])const explain = await Character.find({ name: /Y/ }).explain()
  .then(res= > res[0])
​
// Describe how the MongoDB plan executes queries
console.log(explain.queryPlanner) // { ... }
// Contains statistics about how MongoDB executes queries
console.log(explain.executionStats) // { ... }
Copy the code

readqueryPlannerThe output

The queryPlanner object contains more detailed information about how MongoDB decides to execute the query. For example, here is the queryPlanner object from the explain() call above.

{
  plannerVersion: 1.namespace: 'test.characters'.indexFilterSet: false.parsedQuery: { name: { '$regex': 'Y'}},winningPlan: {
    stage: 'COLLSCAN'.filter: { name: { '$regex': 'Y'}},direction: 'forward'
  },
  rejectedPlans: []}Copy the code

The most important information is the winningPlan property, which contains information about the Plan MongoDB that decides to execute the query. In fact, winningPlan is used to check whether MongoDB uses indexes for queries.

A query plan is a list of stages used to identify documents that match the query. The above plan has only one phase, COLLSCAN, which means that MongoDB performs a full collection scan to answer queries. Collection scanning means that MongoDB searches each document in the Characters collection to see if the name matches a given query.

Query plans become more complex when you introduce indexes. For example, suppose you add an index on name, as shown below.

await Character.collection.createIndex({ name: 1 })
​
const explain = await Character.find({ name: 'O.O' }).explain()
  .then(res= > res[0])
​
explain.queryPlanner
Copy the code

The queryPlanner output looks like this:

{
  plannerVersion: 1.namespace: 'test.characters'.indexFilterSet: false.parsedQuery: { name: { '$eq': 'O.O'}},winningPlan: {
    stage: 'FETCH'.inputStage: {
      stage: 'IXSCAN'.keyPattern: { name: 1 },
      indexName: 'name_1'.isMultiKey: false.multiKeyPaths: { name: []},isUnique: false.isSparse: false.isPartial: false.indexVersion: 2.direction: 'forward'.indexBounds: { name: [ '["O.O", "O.O"]']}}},rejectedPlans: []}Copy the code

The winningPlan property is a recursive structure: winningPlan points to the last stage in the winning query plan, and each stage has an inputStage property that describes the previous stage.

In the above plan, there are two phases: IXSCAN and FETCH. This means that the first MongoDB uses the {name: 1} index to determine which documents match the query, and then retrieves the individual documents.

readexecutionStatsThe output

The executionStats output is more complex than queryPlanner: it includes statistics on the time spent for each stage and the number of documents scanned for each stage.

For example, here is the executionStats output for a simple collection scan:

{
  executionSuccess: true,
  nReturned: 1,
  executionTimeMillis: 0,
  totalKeysExamined: 0,
  totalDocsExamined: 5,
  executionStages: {
    stage: 'COLLSCAN',
    filter: { name: { '$regex': 'Y' } },
    nReturned: 1,
    executionTimeMillisEstimate: 0,
    works: 7,
    advanced: 1,
    needTime: 5,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    direction: 'forward',
    docsExamined: 5
  },
  allPlansExecution: []
}
Copy the code

The important details to notice here are the top-level executionTimeMillis and TotalDocsChecked attributes. ExecutionTimeMillis is the time it takes MongoDB to execute a query, and TotalDocsDemined is the number of documents MongoDB must look at when answering a query.

Keep in mind that executionTimeMillis does not include times when the network is delayed or blocked. Just because executionTimeMillis are small doesn’t mean the end user will see the results immediately.

When you have an index and multiple phases, executionStats breaks down the approximate execution time for each phase and the number of documents scanned. Here is executionStats for queries with indexes, excluding some of the less important details for brevity:

{
  executionSuccess: true.nReturned: 1.executionTimeMillis: 2.totalKeysExamined: 1.totalDocsExamined: 1.executionStages: {
    stage: 'FETCH'.nReturned: 1.executionTimeMillisEstimate: 0.// ...
    docsExamined: 1.// ...
    inputStage: {
      stage: 'IXSCAN'.nReturned: 1.executionTimeMillisEstimate: 0.// ...}},allPlansExecution: []}Copy the code

The executionStats output above shows that there are two phases: IXSCAN and FETCH. The IXSCAN phase executes in 0 ms and causes a document to be sent to the FETCH phase. The FETCH phase checks and returns 1 document, which is the final result of the query.