0

I am visiting the MongoDB learn DBA course. In one of the labs, it was shown how to optimize the compound index by examining the explain plan.

The lab used the sample training dataset. Following query was given:

db.zips.find({
    pop: { $gte: 100000, $lte: 111000 },
    city: 'NEW YORK',
    state: 'NY'
}).sort({ zip: 1 })

We were expected to figure out and create an optimal compound index by inspecting the executionStats.

My first attempt is:

db.zips.createIndex({
    city: 1,
    state: 1,
    pop: 1,
    zip: 1
})

And here is the executionStats result:

{
  executionSuccess: true,
  nReturned: 2,
  executionTimeMillis: 1,
  totalKeysExamined: 2,
  totalDocsExamined: 2,
  executionStages: {
    stage: 'FETCH',
    nReturned: 2,
    executionTimeMillisEstimate: 2,
    works: 6,
    advanced: 2,
    needTime: 3,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 2,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'SORT',
      nReturned: 2,
      executionTimeMillisEstimate: 1,
      works: 6,
      advanced: 2,
      needTime: 3,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      sortPattern: [Object],
      memLimit: 33554432,
      type: 'default',
      totalDataSizeSorted: 190,
      usedDisk: false,
      spills: 0,
      spilledDataStorageSize: 0,
      inputStage: [Object]
    }
  }
}

You can see nReturned = totalKeysExamined = totalDocsExamined = 2. So I considered this index optimal and submitted the solution.

However, my submission is considered failed. The suggested solution is to create an index following ESR rule:

db.zips.createIndex({
    city: 1,
    state: 1,
    zip: 1,
    pop: 1
})

But then the executionStats have become:

{
  executionSuccess: true,
  nReturned: 2,
  executionTimeMillis: 0,
  totalKeysExamined: 41,
  totalDocsExamined: 2,
  executionStages: {
    stage: 'FETCH',
    nReturned: 2,
    executionTimeMillisEstimate: 0,
    works: 41,
    advanced: 2,
    needTime: 38,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 2,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 2,
      executionTimeMillisEstimate: 0,
      works: 41,
      advanced: 2,
      needTime: 38,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      keyPattern: [Object],
      indexName: 'city_1_state_1_zip_1_pop_1',
      isMultiKey: false,
      multiKeyPaths: [Object],
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: [Object],
      keysExamined: 41,
      seeks: 39,
      dupsTested: 0,
      dupsDropped: 0
    }
  }
}

You can observe that totalKeysExamined is increased so more keys are scanned for the suggested index.

I understand that best practice, ESR rule is followed in the suggested solution and IXSCAN appeared. But, how is the suggested compound index "optimal", given the executionStats result?

2
  • You are seeking the needle in a haystack. Do you like to create an index for each individual query? Commented Apr 22 at 11:03
  • @WernfriedDomscheit I just want to learn principles of optimizing indexes through statistics of explain plan. Not sure if I am understanding correctly, but the course seems to be implying thay as long as ESR rule is followed, compound index created would be optimal no matter what. Should this be canonically accepted?
    – ray
    Commented Apr 22 at 11:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.