-
Type:
Improvement
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Labels:None
-
Query Optimization
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.
- duplicates
-
-
- Blocked
-