Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
None
-
None
-
Query Optimization
Description
In the tpc-c workload, there is an index
{
|
"v" : 2, |
"key" : { |
"NO_W_ID" : 1, |
"NO_D_ID" : 1, |
"NO_O_ID" : 1 |
},
|
"name" : "NO_W_ID_1_NO_D_ID_1_NO_O_ID_1", |
"unique" : true |
}
|
and a findAndModify command,
{"findAndModify":"NEW_ORDER","query":{"NO_D_ID":6,"NO_W_ID":655,"$comment":"DELIVERY 6"},"new":false,"remove":true,"fields":{"_id":0,"NO_D_ID":1,"NO_W_ID":1,"NO_O_ID":1},"sort":{"NO_O_ID":1}} |
which generates a plan that looks like
"winningPlan" : { |
"isCached" : false, |
"stage" : "PROJECTION_DEFAULT", |
"transformBy" : { |
"_id" : 0, |
"NO_D_ID" : 1, |
"NO_W_ID" : 1, |
"NO_O_ID" : 1 |
},
|
"inputStage" : { |
"stage" : "DELETE", |
"inputStage" : { |
"stage" : "LIMIT", |
"limitAmount" : 1, |
"inputStage" : { |
"stage" : "FETCH", |
"inputStage" : { |
"stage" : "IXSCAN", |
"keyPattern" : { |
"NO_W_ID" : 1, |
"NO_D_ID" : 1, |
"NO_O_ID" : 1 |
},
|
"indexName" : "NO_W_ID_1_NO_D_ID_1_NO_O_ID_1", |
"isMultiKey" : false, |
"multiKeyPaths" : { |
"NO_W_ID" : [ ], |
"NO_D_ID" : [ ], |
"NO_O_ID" : [ ] |
},
|
"isUnique" : true, |
"isSparse" : false, |
"isPartial" : false, |
"indexVersion" : 2, |
"direction" : "forward", |
"indexBounds" : { |
"NO_W_ID" : [ |
"[1.0, 1.0]" |
],
|
"NO_D_ID" : [ |
"[6.0, 6.0]" |
],
|
"NO_O_ID" : [ |
"[MinKey, MaxKey]" |
]
|
}
|
}
|
}
|
}
|
}
|
},
|
"rejectedPlans" : [ ] |
The fetch above the index scan seems unnecessary since the projection can be satisfied by the index. The documentation is a bit unclear about the semantics of findAndModify, typically it will return the document it found, but if there is a covering projection, then a fetch is not required.