According to the docs, MongoDB should skip the FETCH stage when an index covers a query.
If I correctly understood, this sentences explain this behavior:
Covered Queries When an index covers a query, MongoDB can both match
the query conditions and return the results using only the index keys;
i.e. MongoDB does not need to examine documents from the collection to
return the results.
When an index covers a query, the explain result has an IXSCAN stage
that is not a descendant of a FETCH stage, and in the executionStats,
the totalDocsExamined is 0.
In earlier versions of MongoDB, cursor.explain() returned the
indexOnly field to indicate whether the index covered a query.
(https://docs.mongodb.com/manual/reference/explain-results/)
And this
With this in place, the query takes less than 2 Ms. Because the index
‘covered’ the query, MongoDB was able to match the query conditions
and return the results using only the index keys; without even needing
to examine documents from the collection to return the results. (if
you see an IXSCAN stage that is not a child of a FETCH stage, in the
execution plan then the index ‘covered’ the query.) (https://studio3t.com/knowledge-base/articles/mongodb-index-strategy/)
But in a test scenario it doesn't occur:
Example to test:
db.Test.insert({"Field1":"data on field1: 1","Field2":"data on field2: 1"});
db.Test.insert({"Field1":"data on field1: 2","Field2":"data on field2: 2"});
db.Test.insert({"Field1":"data on field1: 3","Field2":"data on field2: 3"});
db.Test.insert({"Field1":"data on field1: 4","Field2":"data on field2: 4"});
db.Test.insert({"Field1":"data on field1: 5","Field2":"data on field2: 5"});
db.Test.insert({"Field1":"data on field1: 6","Field2":"data on field2: 6"});
After I created an index to Field2.
db.Test.createIndex({"Field2":1})
And then I query the collection:
db.Test.find({"Field2":"data on field2: 5"}).explain("executionStats");
I expected a stage IDXSCAN
that isn't child of a FETCH
stage. But the output is like this:
[...]
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Campo2" : 1.0
},
"indexName" : "Field2_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Campo2" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Field2" : [
"[\"data on field2: 5", \"data on field2: 5\"]"
]
}
}
},
[...]
There are two stages: one "stage" : "FETCH", and its child "stage" : "IXSCAN",.
Can anyone explain what I am misunderstanding?
*** ABOUT PROJECTION
When running query with projection
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"Campo2" : 1.0
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Field2" : 1.0
},
"indexName" : "Field2_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Campo2" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Field2" : [
"[\"data on field2: 5", \"data on field2: 5\"]"
]
}
}
}
},
With Adam's answer: it worked!
I realized that projection shouldn't include "_id" to avoid FETCH
.