Basic query improvement

Yesterday, we saw that a executionStats on the query below that returns 2 docs performs a collection scan (COLLSCAN) and examined a total of 6 documents (which is the total number of documents in the collection)

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

A basic query improvement is if we want to perform a query that returns “selective” result, we must use an index. Selective means the result is narrower. If we want to query all or many documents, then we may need a collection scan. But in the query, we specify specific values to search.

The index below consists of status and total_price in the index key since those are fields provided in the query.

[
	({ v: 2, key: { _id: 1 }, name: "_id_" },
	{
		v: 2,
		key: { total_price: 1, status: 1 },
		name: "total_price_1_status_1",
	}),
];

db.orders.getIndexes();

[
	{ v: 2, key: { _id: 1 }, name: "_id_" },
	{
		v: 2,
		key: { total_price: 1, status: 1 },
		name: "total_price_1_status_1",
	},
];

Notice the winning plan execution compared to a query without an index:

1. executionStats.executionStages.stage = FETCH and executionStats.executionStages.inputStage.stage = IXSCAN — that means an index is used to locate results (IXSCAN) and documents in the query result are read after using the index (FETCH); remember that before, the stage was COLLSCAN
2. keysExamined is now 4 (before, it was 6) — this means it took mongodb to read 4 index keys in the index scan process

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: '9D5FC0B4',
    optimizationTimeMillis: 0,
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { total_price: 1, status: 1 },
        indexName: 'total_price_1_status_1',
        isMultiKey: false,
        multiKeyPaths: { total_price: [], status: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          total_price: [ '[500, 1000]' ],
          status: [ '["placed", "placed"]' ]
        }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 2,
    executionTimeMillis: 1,
    totalKeysExamined: 4,
    totalDocsExamined: 2,
    executionStages: {
      stage: 'FETCH',
      nReturned: 2,
      executionTimeMillisEstimate: 0,
      works: 4,
      advanced: 2,
      needTime: 1,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      docsExamined: 2,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 2,
        executionTimeMillisEstimate: 0,
        works: 4,
        advanced: 2,
        needTime: 1,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: { total_price: 1, status: 1 },
        indexName: 'total_price_1_status_1',
        isMultiKey: false,
        multiKeyPaths: { total_price: [], status: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          total_price: [ '[500, 1000]' ],
          status: [ '["placed", "placed"]' ]
        },
        keysExamined: 4,
        seeks: 2,
        dupsTested: 0,
        dupsDropped: 0
      }
    }
  },
  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
}