I’m confused about why the explain output for the following includes a sort stage (assume blocking?) when the compound index appears to comply with the ESR rule. Could it be because of this in the documentation where it says the sort will be blocking (and not an index sort) unless:
No boundaries for any multikey-indexed field have the same path prefix as the sort pattern.
However, I’m confused on the meaning of the terms “path prefix” and “sort pattern” and how it fits into the below scenario if at all.
db.STUDENT.insertMany([{
"_id": 1,
"FIRST_NAME": "Aaron",
"PHONE": [
{"PHONE_NUMBER": 1234, "PHONE_ID": 11 },
{"PHONE_NUMBER": 2345, "PHONE_ID": 12, }
]
}, {
"_id": 2,
"FIRST_NAME": "Aaron",
"PHONE": [
{"PHONE_NUMBER": 3456, "PHONE_ID": 13 },
{"PHONE_NUMBER": 4567, "PHONE_ID": 14, }
]
}])
db.STUDENT.createIndex( { "FIRST_NAME": 1, "PHONE.PHONE_ID" : 1, "PHONE.PHONE_NUMBER": 1} )
and the query:
db.STUDENT.find({
"$and": [{ "FIRST_NAME": "Aaron" }, {
"PHONE": {
"$elemMatch": {
"$and": [{
"PHONE_NUMBER": {
"$gte": 3456
}
},
{
"PHONE_NUMBER": {
"$lte": 4567
}
}]
}
}
}]
}).sort({ "PHONE.PHONE_ID": -1 })
.hint("FIRST_NAME_1_PHONE.PHONE_ID_1_PHONE.PHONE_NUMBER_1").explain(true)["executionStats"]
Result:
{
executionSuccess: true,
nReturned: 1,
executionTimeMillis: 0,
totalKeysExamined: 4,
totalDocsExamined: 1,
executionStages: {
stage: 'SORT',
nReturned: 1,
executionTimeMillisEstimate: 0,
works: 7,
advanced: 1,
needTime: 5,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
sortPattern: { 'PHONE.PHONE_ID': -1 },
memLimit: 104857600,
type: 'simple',
totalDataSizeSorted: 160,
usedDisk: false,
inputStage: {
stage: 'FETCH',
filter: {
PHONE: {
'$elemMatch': {
'$and': [
{ PHONE_NUMBER: { '$lte': 4567 } },
{ PHONE_NUMBER: { '$gte': 3456 } }
]
}
}
},
nReturned: 1,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 1,
needTime: 3,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 1,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 1,
executionTimeMillisEstimate: 0,
works: 5,
advanced: 1,
needTime: 3,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { FIRST_NAME: 1, 'PHONE.PHONE_ID': 1, 'PHONE.PHONE_NUMBER': 1 },
indexName: 'FIRST_NAME_1_PHONE.PHONE_ID_1_PHONE.PHONE_NUMBER_1',
isMultiKey: true,
multiKeyPaths: {
FIRST_NAME: [],
'PHONE.PHONE_ID': [ 'PHONE' ],
'PHONE.PHONE_NUMBER': [ 'PHONE' ]
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: {
FIRST_NAME: [ '["Aaron", "Aaron"]' ],
'PHONE.PHONE_ID': [ '[MinKey, MaxKey]' ],
'PHONE.PHONE_NUMBER': [ '[3456, 4567]' ]
},
keysExamined: 4,
seeks: 3,
dupsTested: 2,
dupsDropped: 1
}
}
},
allPlansExecution: []
}