Hi,
I have issues to understand why counts are so slow. I am using the C# driver with CountDocumentAsync. My collection has 1Mio+ records with around 1,5 GB of data. Not that much actually.
In my use case I count the number of documents covered by a filter. The filter can be fulfilled by an index. When I let Mongo explain the query I get the following result:
{
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"plannerVersion" : NumberInt(1),
"namespace" : "SquidexContent.States_Contents_All3",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"_ai" : {
"$eq" : "ff0c76e5-0459-416a-8680-601ab07fdb72"
}
},
{
"_si" : {
"$eq" : "3abca025-a594-4d13-8836-acd4da20d0b1"
}
},
{
"id" : {
"$gt" : "00000000-0000-0000-0000-000000000000"
}
},
{
"mt" : {
"$gt" : ISODate("1970-01-01T00:00:00.000+0000")
}
},
{
"dl" : {
"$not" : {
"$eq" : true
}
}
}
]
},
"queryHash" : "FD73CE49",
"planCacheKey" : "5216DDAA",
"winningPlan" : {
"stage" : "IXSCAN",
"keyPattern" : {
"mt" : NumberInt(-1),
"id" : NumberInt(1),
"_ai" : NumberInt(1),
"_si" : NumberInt(1),
"dl" : NumberInt(1),
"rf" : NumberInt(1)
},
"indexName" : "mt_-1_id_1__ai_1__si_1_dl_1_rf_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"mt" : [
],
"id" : [
],
"_ai" : [
],
"_si" : [
],
"dl" : [
],
"rf" : [
"rf"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"mt" : [
"[new Date(9223372036854775807), new Date(0))"
],
"id" : [
"(\"00000000-0000-0000-0000-000000000000\", {})"
],
"_ai" : [
"[\"ff0c76e5-0459-416a-8680-601ab07fdb72\", \"ff0c76e5-0459-416a-8680-601ab07fdb72\"]"
],
"_si" : [
"[\"3abca025-a594-4d13-8836-acd4da20d0b1\", \"3abca025-a594-4d13-8836-acd4da20d0b1\"]"
],
"dl" : [
"[MinKey, true)",
"(true, MaxKey]"
],
"rf" : [
"[MinKey, MaxKey]"
]
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"id" : {
"$gt" : "00000000-0000-0000-0000-000000000000"
}
},
{
"mt" : {
"$gt" : ISODate("1970-01-01T00:00:00.000+0000")
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_ai" : NumberInt(1),
"dl" : NumberInt(1),
"_si" : NumberInt(1)
},
"indexName" : "_ai_1_dl_1__si_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_ai" : [
],
"dl" : [
],
"_si" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"_ai" : [
"[\"ff0c76e5-0459-416a-8680-601ab07fdb72\", \"ff0c76e5-0459-416a-8680-601ab07fdb72\"]"
],
"dl" : [
"[MinKey, true)",
"(true, MaxKey]"
],
"_si" : [
"[\"3abca025-a594-4d13-8836-acd4da20d0b1\", \"3abca025-a594-4d13-8836-acd4da20d0b1\"]"
]
}
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"_ai" : {
"$eq" : "ff0c76e5-0459-416a-8680-601ab07fdb72"
}
},
{
"id" : {
"$gt" : "00000000-0000-0000-0000-000000000000"
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_si" : NumberInt(1),
"dl" : NumberInt(1),
"mt" : NumberInt(-1)
},
"indexName" : "_si_1_dl_1_mt_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_si" : [
],
"dl" : [
],
"mt" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"_si" : [
"[\"3abca025-a594-4d13-8836-acd4da20d0b1\", \"3abca025-a594-4d13-8836-acd4da20d0b1\"]"
],
"dl" : [
"[MinKey, true)",
"(true, MaxKey]"
],
"mt" : [
"[new Date(9223372036854775807), new Date(0))"
]
}
}
}
]
}
}
},
{
"$group" : {
"_id" : {
"$const" : NumberInt(1)
},
"n" : {
"$sum" : {
"$const" : NumberInt(1)
}
}
}
}
],
"serverInfo" : {
"host" : "b0d41a1197f0",
"port" : NumberInt(27017),
"version" : "4.4.6",
"gitVersion" : "72e66213c2c3eab37d9358d5e78ad7f5c1d0d0d7"
},
"ok" : 1.0
}
So my understanding is that the query can be fulfilled by an index.
But when I check the result in the profiler I get the following document:
{
"op" : "command",
"ns" : "SquidexContent.States_Contents_All3",
"command" : {
"aggregate" : "States_Contents_All3",
"pipeline" : [
{
"$match" : {
"mt" : {
"$gt" : ISODate("1970-01-01T00:00:00.000+0000")
},
"id" : {
"$gt" : "00000000-0000-0000-0000-000000000000"
},
"_ai" : "ff0c76e5-0459-416a-8680-601ab07fdb72",
"_si" : {
"$in" : [
"3abca025-a594-4d13-8836-acd4da20d0b1"
]
},
"dl" : {
"$ne" : true
}
}
},
{
"$group" : {
"_id" : NumberInt(1),
"n" : {
"$sum" : NumberInt(1)
}
}
}
],
"cursor" : {
},
"allowDiskUse" : false,
"$db" : "SquidexContent",
"lsid" : {
"id" : UUID("ebb255c8-9140-4f66-986f-4e787fd70a5b")
}
},
"keysExamined" : NumberInt(1271036),
"docsExamined" : NumberInt(0),
"cursorExhausted" : true,
"numYield" : NumberInt(1271),
"nreturned" : NumberInt(1),
"queryHash" : "FD73CE49",
"planCacheKey" : "5216DDAA",
"locks" : {
"ReplicationStateTransition" : {
"acquireCount" : {
"w" : NumberLong(1274)
}
},
"Global" : {
"acquireCount" : {
"r" : NumberLong(1274)
}
},
"Database" : {
"acquireCount" : {
"r" : NumberLong(1273)
}
},
"Collection" : {
"acquireCount" : {
"r" : NumberLong(1273)
}
},
"Mutex" : {
"acquireCount" : {
"r" : NumberLong(2)
}
}
},
"flowControl" : {
},
"storage" : {
"data" : {
"bytesRead" : NumberLong(2374652),
"timeReadingMicros" : NumberLong(10476)
}
},
"responseLength" : NumberInt(148),
"protocol" : "op_msg",
"millis" : NumberInt(1301),
"planSummary" : "IXSCAN { mt: -1, id: 1, _ai: 1, _si: 1, dl: 1, rf: 1 }",
"ts" : ISODate("2021-06-07T19:32:44.014+0000"),
"client" : "172.18.0.1",
"allUsers" : [
],
"user" : ""
}
So it is actually reading from storage, even though the index for this collection should fit into RAM.