Basic ESR example

Yesterday, we knew that the query below scans the index keys (IXSCAN) to select the right documents and fetches the documents (FETCH). The collection having a utilized total_price_1_status_1 index is already efficient, but there is more to it. We saw that totalKeysExamined is 4; what if we just examined the keys we need?

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

Let’s examine what we will find with this new index. The previous one should be deleted.

db.orders.createIndex({ status: 1, total_price: 1 }, { name: "status_1_total_price_1" });

Let’s see the keys of the new index.

db.orders.find().hint("status_1_total_price_1").returnKey();

[
	{
		_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,
	},
];

I transformed the result above into the array below. Notice that performing a scan range is tighter here compared to yesterday’s keys list.

[
	{ status: "complete", total_price: 200 },
	{ status: "complete", total_price: 600 },
	{ status: "complete", total_price: 1200 },
	{ status: "placed", total_price: 100 },
	{ status: "placed", total_price: 500 },
	{ status: "placed", total_price: 1000 },
];

executionStats shows that it examines the keys (IDXSCAN) twice now (totalKeysExamined). This is more efficient than the previous index. This is an example of following the ESR (Equality-Sort-Range) guideline. Our query performs an equality and range check. With that, the index should start with high selectivity field first, which is status. With that, we set more specific query. Only after that we place the field we use for a range query, which is total_price.

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: 2,
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'FETCH',
      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: 3,
    totalKeysExamined: 2,
    totalDocsExamined: 2,
    executionStages: {
      stage: 'FETCH',
      nReturned: 2,
      executionTimeMillisEstimate: 0,
      works: 3,
      advanced: 2,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      docsExamined: 2,
      alreadyHasObj: 0,
      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 } },
    '$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
}