-
Type: Improvement
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
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
-
SERVER-17148 Remove plans do not need a FETCH stage
- Blocked