Details
-
Bug
-
Resolution: Duplicate
-
Minor - P4
-
None
-
3.2.4
-
None
-
ALL
Description
Collection has several compound indexes that start with same field.
When using count() with a filter on the first field in a compound index, there is a huge operation time difference for execution with or without index hint, despite the fact that mongodb uses same index in both cases.
1.Case: Filtered count with index hint
Explain:
db.transaction.explain().count({"data.systemId":"TEST"},{hint:"data.systemId_1.0_data.timeReceived_-1.0"}) |
{
|
"queryPlanner" : { |
"plannerVersion" : 1, |
"namespace" : "test.transaction", |
"indexFilterSet" : false, |
"parsedQuery" : { |
"data.systemId" : { |
"$eq" : "TEST" |
}
|
},
|
"winningPlan" : { |
"stage" : "COUNT", |
"inputStage" : { |
"stage" : "COUNT_SCAN", |
"keyPattern" : { |
"data.systemId" : 1, |
"data.timeReceived" : -1 |
},
|
"indexName" : "data.systemId_1.0_data.timeReceived_-1.0", |
"isMultiKey" : false, |
"isUnique" : false, |
"isSparse" : false, |
"isPartial" : false, |
"indexVersion" : 1 |
}
|
},
|
"rejectedPlans" : [ ] |
},
|
"serverInfo" : { |
"host" : "test-db1", |
"port" : 27017, |
"version" : "3.2.4", |
"gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30" |
},
|
"ok" : 1 |
}
|
Statistics from system.profile:
{
|
"op" : "command", |
"ns" : "test.transaction", |
"command" : { |
"count" : "transaction", |
"query" : { |
"data.systemId" : "TEST" |
},
|
"hint" : "data.systemId_1.0_data.timeReceived_-1.0", |
"fields" : { |
|
|
}
|
},
|
"keyUpdates" : NumberInt(0), |
"writeConflicts" : NumberInt(0), |
"numYield" : NumberInt(32602), |
"locks" : { |
"Global" : { |
"acquireCount" : { |
"r" : NumberLong(65206) |
}
|
},
|
"Database" : { |
"acquireCount" : { |
"r" : NumberLong(32603) |
}
|
},
|
"Collection" : { |
"acquireCount" : { |
"r" : NumberLong(32603) |
}
|
}
|
},
|
"responseLength" : NumberInt(47), |
"protocol" : "op_command", |
"millis" : NumberInt(1104), |
"execStats" : { |
|
|
},
|
"ts" : ISODate("2016-04-13T11:29:46.541+0000"), |
"client" : "127.0.0.1", |
"allUsers" : [ |
{
|
"user" : "root", |
"db" : "admin" |
}
|
],
|
"user" : "root@admin" |
}
|
2.Case: Filtered count without index hint
Explain:
db.transaction.explain().count({"data.systemId":"TEST"}) |
{
|
"queryPlanner" : { |
"plannerVersion" : 1, |
"namespace" : "test.transaction", |
"indexFilterSet" : false, |
"parsedQuery" : { |
"data.systemId" : { |
"$eq" : "TEST" |
}
|
},
|
"winningPlan" : { |
"stage" : "COUNT", |
"inputStage" : { |
"stage" : "COUNT_SCAN", |
"keyPattern" : { |
"data.systemId" : 1, |
"data.timeReceived" : -1 |
},
|
"indexName" : "data.systemId_1.0_data.timeReceived_-1.0", |
"isMultiKey" : false, |
"isUnique" : false, |
"isSparse" : false, |
"isPartial" : false, |
"indexVersion" : 1 |
}
|
},
|
"rejectedPlans" : [ ] |
},
|
"serverInfo" : { |
"host" : "test-db1", |
"port" : 27017, |
"version" : "3.2.4", |
"gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30" |
},
|
"ok" : 1 |
}
|
Statistics from system.profile:
{
|
"op" : "command", |
"ns" : "test.transaction", |
"command" : { |
"count" : "transaction", |
"query" : { |
"data.systemId" : "TEST" |
},
|
"fields" : { |
|
|
}
|
},
|
"keyUpdates" : NumberInt(0), |
"writeConflicts" : NumberInt(0), |
"numYield" : NumberInt(32603), |
"locks" : { |
"Global" : { |
"acquireCount" : { |
"r" : NumberLong(65208) |
}
|
},
|
"Database" : { |
"acquireCount" : { |
"r" : NumberLong(32604) |
}
|
},
|
"Collection" : { |
"acquireCount" : { |
"r" : NumberLong(32604) |
}
|
}
|
},
|
"responseLength" : NumberInt(47), |
"protocol" : "op_command", |
"millis" : NumberInt(5370), |
"execStats" : { |
|
|
},
|
"ts" : ISODate("2016-04-13T11:28:24.711+0000"), |
"client" : "127.0.0.1", |
"allUsers" : [ |
{
|
"user" : "root", |
"db" : "admin" |
}
|
],
|
"user" : "root@admin" |
}
|
Results above are from collection with 7M records, filter above matches 4M.
Attachments
Issue Links
- duplicates
-
SERVER-22133 COUNT_SCAN plans are not generated properly from the plan cache
-
- Closed
-