|
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.
|