-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Minor - P4
-
None
-
Affects Version/s: 3.2.4
-
Component/s: Performance
-
None
-
ALL
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.
- duplicates
-
-
- Closed
-