Basic query plan and execution stats

For the explain demo, we have these 6 documents in the orders collection.

db.orders.find().sort({ status: 1, total_price: 1 })

[
  {
    _id: ObjectId("69e229b79cd866c677544caa"),
    status: "complete",
    total_price: 200,
  },
  {
    _id: ObjectId("69e229b79cd866c677544cab"),
    status: "complete",
    total_price: 600,
  },
  {
    _id: ObjectId("69e229b79cd866c677544cac"),
    status: "complete",
    total_price: 1200,
  },
  {
    _id: ObjectId("69e229b79cd866c677544ca7"),
    status: "placed",
    total_price: 100,
  },
  {
    _id: ObjectId("69e229b79cd866c677544ca8"),
    status: "placed",
    total_price: 500,
  },
  {
    _id: ObjectId("69e229b79cd866c677544ca9"),
    status: "placed",
    total_price: 1000,
  },
]

The query we will investigate is this. It uses a specific status and value range and results in 2 documents.

db.orders.find({ status: "placed", total_price: { $gte: 500, $lte: 1000 } })

[
  {
    _id: ObjectId("69e229b79cd866c677544ca8"),
    status: "placed",
    total_price: 500,
  },
  {
    _id: ObjectId("69e229b79cd866c677544ca9"),
    status: "placed",
    total_price: 1000,
  },
]

explain by default just provides you with queryPlanner data, which is the winning plan selected by the query optimizer. But if you also care about the winning plan execution details, provide executionStats function call argument.

With the plan, you will notice that:

1. Winning plan stage is COLLSCAN — that means, the query will need to scan the documents linearly to find the documents that match the query
2. The query result returned 2 documents (nReturned), matching the query result above.
3. Total number of documents the query scanned is 6 (totalDocsExamined)

Based on that, we can understand that as the documents grow, queries that have the same winning plan stage may be slower over time.

db.orders.find({ status: "placed", total_price: { $gte: 500, $lte: 1000 } }).explain("executionStats")
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'explain_demo.orders',
    indexFilterSet: false,
    parsedQuery: {
      '$and': [
        { status: { '$eq': 'placed' } },
        { total_price: { '$lte': 1000 } },
        { total_price: { '$gte': 500 } }
      ]
    },
    queryHash: '3FAEB4DF',
    planCacheKey: '3FAEB4DF',
    optimizationTimeMillis: 0,
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'COLLSCAN',
      filter: {
        '$and': [
          { status: { '$eq': 'placed' } },
          { total_price: { '$lte': 1000 } },
          { total_price: { '$gte': 500 } }
        ]
      },
      direction: 'forward'
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 2,
    executionTimeMillis: 0,
    totalKeysExamined: 0,
    totalDocsExamined: 6,
    executionStages: {
      stage: 'COLLSCAN',
      filter: {
        '$and': [
          { status: { '$eq': 'placed' } },
          { total_price: { '$lte': 1000 } },
          { total_price: { '$gte': 500 } }
        ]
      },
      nReturned: 2,
      executionTimeMillisEstimate: 0,
      works: 7,
      advanced: 2,
      needTime: 4,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      direction: 'forward',
      docsExamined: 6
    }
  },
  command: {
    find: 'orders',
    filter: { status: 'placed', total_price: { '$gte': 500, '$lte': 1000 } },
    '$db': 'explain_demo'
  },
  serverInfo: {
    host: 'ea3f589b0ffc',
    port: 27017,
    version: '7.0.29',
    gitVersion: '415cc13e900a82a2e00e4f4417dc7159a883e975'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600,
    internalQueryFrameworkControl: 'forceClassicEngine'
  },
  ok: 1
}