Covered query

Yesterday, the query is already optimal when the keysExamined (the number of visited index keys) is equal to docsExamined (the number of the visited documents). This is also equal to nReturned, which is the total number of query results. However, if you think about it, total_price and status are already in the index, so why does MongoDB need to visit the documents, resulting to increased docsExamined? It is because the project, if not provided, will select everything. In our example, _id is not part of the index keys, so MongoDB needs to visit the resulting documents. What we want to achieve is actually called a covered query. A covered query is satisfied by the index alone without having to examaine the documents.

Below, we know the index covers the query because IXSCAN is not a descendant of FETCH and totalDocsExamined is 0.

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: 'A97D92B6',
    planCacheKey: '1B7B86B4',
    optimizationTimeMillis: 0,
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'PROJECTION_COVERED',
      transformBy: { _id: 0, status: 1, total_price: 1 },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { status: 1, total_price: 1 },
        indexName: 'status_1_total_price_1',
        isMultiKey: false,
        multiKeyPaths: { status: [], total_price: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          status: [ '["placed", "placed"]' ],
          total_price: [ '[500, 1000]' ]
        }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 2,
    executionTimeMillis: 2,
    totalKeysExamined: 2,
    totalDocsExamined: 0,
    executionStages: {
      stage: 'PROJECTION_COVERED',
      nReturned: 2,
      executionTimeMillisEstimate: 0,
      works: 3,
      advanced: 2,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      transformBy: { _id: 0, status: 1, total_price: 1 },
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 2,
        executionTimeMillisEstimate: 0,
        works: 3,
        advanced: 2,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: { status: 1, total_price: 1 },
        indexName: 'status_1_total_price_1',
        isMultiKey: false,
        multiKeyPaths: { status: [], total_price: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          status: [ '["placed", "placed"]' ],
          total_price: [ '[500, 1000]' ]
        },
        keysExamined: 2,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    }
  },
  command: {
    find: 'orders',
    filter: { status: 'placed', total_price: { '$gte': 500, '$lte': 1000 } },
    projection: { _id: 0, status: 1, total_price: 1 },
    '$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
}