79749609

Date: 2025-08-28 21:22:04
Score: 1.5
Natty:
Report link

It should not have to do an additional sort as "bin" comes in order:

for (let bin of [1, 2, 3]) {
  for (let gender of ["M", "F"]) {
    for (let age of [20, 30]) {
      for (let loc of ["NY", "LA"]) {
        for (let i = 0; i < 3; i++) {
          db.users.insertOne({ bin, gender, age, location: loc, name: `User_${bin}_${gender}_${age}_${loc}_${i}`
          })
        }
      }
    }
  }
}

db.users.createIndex({
  bin: 1, gender: 1, age: 1, location: 1
})

db.users.find({
  bin: { $in: [1, 2, 3] },
  gender: "M", age: 20, location: "NY"
}).sort({ bin: 1 }).explain("executionStats").executionStats

One IXSCAN with seeks and no SORT:

{
  executionSuccess: true,
  nReturned: 9,
  executionTimeMillis: 1,
  totalKeysExamined: 14,
  totalDocsExamined: 9,
  executionStages: {
    isCached: false,
    stage: 'FETCH',
    nReturned: 9,
    executionTimeMillisEstimate: 0,
    works: 14,
    advanced: 9,
    needTime: 4,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 9,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'IXSCAN',
      nReturned: 9,
      executionTimeMillisEstimate: 0,
      works: 14,
      advanced: 9,
      needTime: 4,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      keyPattern: { bin: 1, gender: 1, age: 1, location: 1 },
      indexName: 'bin_1_gender_1_age_1_location_1',
      isMultiKey: false,
      multiKeyPaths: { bin: [], gender: [], age: [], location: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: {
        bin: [ '[1, 1]', '[2, 2]', '[3, 3]' ],
        gender: [ '["M", "M"]' ],
        age: [ '[20, 20]' ],
        location: [ '["NY", "NY"]' ]
      },
      keysExamined: 14,
      seeks: 5,
      dupsTested: 0,
      dupsDropped: 0
    }
  }
}

If you have something different, please share your execution plan

Reasons:
  • RegEx Blacklisted phrase (2.5): please share your
  • Long answer (-1):
  • Has code block (-0.5):
  • Low reputation (0.5):
Posted by: FranckPachot