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