Info

When perform "count" with "$elemmatch" in the query it never uses COUNT_SCAN and has to FETCH document before counting. Here is an example: //create a simple document that has an array of embedded doc, like this: var doc = { "annotationList": [ { "meta": { "league": "league_value1", "class": "class_value1" } }, { "meta": { "league": "league_value1", "class": "class_value1" } } ] } //save doc to the collection db.testCollection.save(doc); //build compound index using 2 multikey field db.testCollection.ensureIndex({"annotationList.meta.league": 1, "annotationList.meta.league": 1}); Now if we perform the following: db.testCollection.explain("executionStats").count({ "annotationList": { "$elemMatch": { "meta.league": "league_value1" } } }); We can see that it does the following: XSCAN -> FETCH -> COUNT However if we do the following: cursor = db.testCollection.explain("executionStats").count({ "annotationList.meta.league": "league_value1" }); We can see that now there is not need to FETCH documents and it simply performs COUNT_SCAN

Top User Comments

asya commented on Fri, 8 Jun 2018 16:37:06 +0000: I'm closing this because the system is working correctly, however, I've linked to SERVER-27494 which is related to this. The same way to expression what you are querying with count may be with aggregation pipeline and we already have a ticket to be able to handle that pipeline with a covered index. I'll add a note to that ticket with a description of your example. asya commented on Fri, 8 Jun 2018 13:08:31 +0000: Docs mention that multikey indexes cannot cover a query: https://docs.mongodb.com/manual/core/index-multikey/#covered-queries Meaning all information about document is not in multikey index - count with one field is an exception where it can do better. You would see why it has to be covered if you add a non-array matching document into the collection, then the number counted would be lower than number examined or returned. avasilkov commented on Fri, 8 Jun 2018 09:32:48 +0000: Is there a way to get similar to index count speeds for elemMatch query for two elements equality? or is it best to just add a computed field? I think there should be a mention in the count or elemMatch docs that warns about slower speeds for elemMatch count even if all fields in the elemMatch are indexed. Although it's still strange, since execution stats for my query show keysExamined == docsExamined == nReturned, it means that it only examined all docs returned by an index and they all fit the condition? "nReturned" : 5387361, "executionTimeMillis" : 1859328, "totalKeysExamined" : 5387361, "totalDocsExamined" : 5387361, Same query but one condition is of different value "nReturned" : 20050, "executionTimeMillis" : 3163, "totalKeysExamined" : 20050, "totalDocsExamined" : 20050, And not sure if it matters but the index consists of two fields and the first field of low cardinality - only two possible values. asya commented on Fri, 8 Jun 2018 00:41:56 +0000: MongoDB can use index for counting, but there is no way to know from just the index whether a document should be counted or not when a query is constructed with $elemMatch. in your original example, why do you have $elemMatch at all? Without it you will get exactly what you are looking for. db.elem.explain().count({ "annotationList.meta.league": "league_value1" } ); { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.elem", "indexFilterSet" : false, "parsedQuery" : { "annotationList.meta.league" : { "$eq" : "league_value1" } }, "winningPlan" : { "stage" : "COUNT", "inputStage" : { "stage" : "COUNT_SCAN", "keyPattern" : { "annotationList.meta.league" : 1 }, "indexName" : "annotationList.meta.league_1", "isMultiKey" : true, "multiKeyPaths" : { "annotationList.meta.league" : [ "annotationList" ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "indexBounds" : { "startKey" : { "annotationList.meta.league" : "league_value1" }, "startKeyInclusive" : true, "endKey" : { "annotationList.meta.league" : "league_value1" }, "endKeyInclusive" : true } } }, "rejectedPlans" : [ ] } I suspect the actual query is testing more than one field, right? In which case you must have both comparisons inside $elemMatch to ensure the same element matches both conditions and the query cannot be covered as this information is not available in the index. avasilkov commented on Sat, 2 Jun 2018 13:20:46 +0000: I agree with Alexey. It would have been useful to have an option to disable this check. I've encountered this issue recently and it causes 2-3 hour counts on 147GB collection with an index that I thought would cover this query being just 3.5GB. When the query matches 37 million of documents, it examines all of them and it takes 3.5 hours on 30GB RAM server. It works fast for a small amount of matches. I've run a local test for 12GB collection with dummy documents and it takes 1104ms with elemMatch for 2 subfields vs 148ms if I add this as a computed field into a separate field. Time is average over 10 runs with cold start run not counted towards average. Unfortunately 12GB can fit into my ram, but I think if it couldn't fit everything, it would run even slower. asemen commented on Fri, 1 Jun 2018 18:50:28 +0000: Oh, I see... but is there any way to maybe hint mongo somehow that this specific field is always array and there is no need to FETCH data? Or am I right that in fact mongo can't use indexes for counting (for any query?) if I have to check something complex against an array of subdocuments? Is there any possible workaround without changing documents' structure? Also probably it makes sense to add this information to the docs about "$elemMatch" operator... asya commented on Fri, 1 Jun 2018 02:25:39 +0000: I believe this works as expected since $elemMatch means only match if the subdocument is an array element, and without FETCH we don't know if it's an array element or not (array and non-array values are the same values in the index).

Steps to Reproduce