MongoDB - searching in array using $elemMatch slower with index than without

Question

I have a collection with 500k documents with the following structure:

{
    "_id" : ObjectId("5f2d30b0c7cc16c0da84a57d"),
    "RecipientId" : "6a28d20f-4741-4c14-a055-2eb2593dcf13",
    
    ...
    
    "Actions" : [ 
        {
            "CampaignId" : "7fa216da-db22-44a9-9ea3-c987c4152ba1",
            "ActionDatetime" : ISODate("1998-01-13T00:00:00.000Z"),
            "ActionDescription" : "OPEN"
        }, 
        ...
    ]
}

I need to count the top level documents whose subdocuments inside the "Actions" array meet certain criteria, and for this I've created the following Multikey index (taking only the "ActionDatetime" field as an example):

db.getCollection("recipients").createIndex( { "Actions.ActionDatetime": 1 } )

The problem is that when I write the query using an $elemMatch, the operation is much slower than when I don't use the Multikey index at all:

db.getCollection("recipients").count({
  "Actions":
    { $elemMatch:{ ActionDatetime: {$gt: new Date("1950-08-04")} }}}
)

The stats for this query:

{
    "executionSuccess" : true,
    "nReturned" : 0,
    "executionTimeMillis" : 13093,
    "totalKeysExamined" : 8706602,
    "totalDocsExamined" : 500000,
    "executionStages" : {
        "stage" : "COUNT",
        "nReturned" : 0,
        "executionTimeMillisEstimate" : 1050,
        "works" : 8706603,
        "advanced" : 0,
        "needTime" : 8706602,
        "needYield" : 0,
        "saveState" : 68020,
        "restoreState" : 68020,
        "isEOF" : 1,
        "nCounted" : 500000,
        "nSkipped" : 0,
        "inputStage" : {
            "stage" : "FETCH",
            "filter" : {
                "Actions" : {
                    "$elemMatch" : {
                        "ActionDatetime" : {
                            "$gt" : ISODate("1950-08-04T00:00:00.000Z")
                        }
                    }
                }
            },
            "nReturned" : 500000,
            "executionTimeMillisEstimate" : 1040,
            "works" : 8706603,
            "advanced" : 500000,
            "needTime" : 8206602,
            "needYield" : 0,
            "saveState" : 68020,
            "restoreState" : 68020,
            "isEOF" : 1,
            "docsExamined" : 500000,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 500000,
                "executionTimeMillisEstimate" : 266,
                "works" : 8706603,
                "advanced" : 500000,
                "needTime" : 8206602,
                "needYield" : 0,
                "saveState" : 68020,
                "restoreState" : 68020,
                "isEOF" : 1,
                "keyPattern" : {
                    "Actions.ActionDatetime" : 1.0
                },
                "indexName" : "Actions.ActionDatetime_1",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "Actions.ActionDatetime" : [ 
                        "Actions"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Actions.ActionDatetime" : [ 
                        "(new Date(-612576000000), new Date(9223372036854775807)]"
                    ]
                },
                "keysExamined" : 8706602,
                "seeks" : 1,
                "dupsTested" : 8706602,
                "dupsDropped" : 8206602
            }
        }
    }
}

This query took 14sec to execute, whereas if I remove the index, the COLLSCAN takes 1 second.

I understand that I'd have a better performance by not using $elemMatch, and filtering by "Actions.ActionDatetime" directly, but in reality I'll need to filter by more than one field inside the array, so the $elemMatch becomes mandatory.

I suspect that it's the FETCH phase which is killing the performance, but I've noticed that when i use the "Actions.ActionDatetime" directly, MongoDB is able to use a COUNT_SCAN instead of the fetch, but the performance is still poorer than the COLLSCAN (4s).

I'd like to know if there's a better indexing strategy for indexing subdocuments with high cardinality inside an array, or if I'm missing something with my current approach. As the volume grows, indexing this information will be a necessity and I don't want to rely on a COLLSCAN.

Answer 1

The problem here is twofold:

• Every document matches your query
  Consider the analogy of an index being the catalog in a library. If you want to find a single book, looking it up in the catalog permits you to go straight to the shelf holding it, which is much faster than starting at the first shelf and searching through the books (unless of course it actually is on that first shelf). However, if you want to get all of the books in the library, it will be much faster to just start taking them off them shelf than checking the catalog for each one and then going to get it.
  While this analogy is far from perfect it does show that the collection scan can be expected to be much more efficient than index lookups when a large percentage of the documents will be considered.

• Multikey indexes have more than one entry for each document
  When mongod builds an index on an array, it creates a separate entry in the index for each discreet element. When you match a value from an array element, the index can get you to a matching document quickly, but because a single document is expected to have multiple entries in the index deduplication is required afterward.

These are further exacerbated by the $elemMatch. Since the index contains values for the separate indexed fields, it is unable to determine if the values for different fields occur within the same array element from the index, so it must load each document to check that.

Essentially, when using elemMatch with the index and a query that matches every document, the mongod node will examine the index to identify matching values, deduplicate that list, then load each document (likely in the order encountered in the index) to see if a single array value satisfies the elemMatch.

When compared with the non-indexed collection scan execution where the mongod must load each document in the order encountered on disk, and check if a single array element matches satisfies the elemMatch, it should be apparent that the indexed query will perform worse if a large percentage of the documents match the query.

Answer 2

TLDR: this is the expected behaviour of a multikey index combined with an $elemMatch.

Why is this happening?

So it is the FETCH stage that's ruining your query performance, unfortunately this is the expected behaviour.

From the covered query section of the multikey index documents:

Multikey indexes cannot cover queries over array field(s).

Meaning all information about a sub-document is not in the multikey index - count with one field is an exception where it can do better. But in this case $elemMatch is still forcing a FETCH stage? why it is only using a single field.

Imagine this scenario:

//doc1
{
   "Actions" : [ 
        {
            "CampaignId" : "7fa216da-db22-44a9-9ea3-c987c4152ba1",
            "ActionDatetime" : ISODate("1998-01-13T00:00:00.000Z"),
            "ActionDescription" : "OPEN"
        }, 
        ...
    ]
}
//doc2
{
   "Actions" : {
            "CampaignId" : "7fa216da-db22-44a9-9ea3-c987c4152ba1",
            "ActionDatetime" : ISODate("1998-01-13T00:00:00.000Z"),
            "ActionDescription" : "OPEN"
   }
}

Because Mongo "flattens" the array it indexes, once the index is built Mongo cannot differentiate between these 2 documents, but $elemMatch requires an array object to match it has to fetch these documents to determine which one qualifies. This is the exact problem you're facing.

What can you do?

Well not much sadly. I'm not sure how dynamic your queries are but the only way to solve this issue is to preprocess the documents to contain the "answers" to your queries.

I still find it hard to believe that COLSCAN is doing better than the index query. I'm assuming you're matching a large portion of your collection combined with the fact that Actions array are very large.

What I would suggest as performance will keep being an issue especially if your queries will continue to match a large portion of the collection is to restructure you data. Just save each Actions entry as it's own document.

{
   "Actions" : {
            "CampaignId" : "7fa216da-db22-44a9-9ea3-c987c4152ba1",
            "ActionDatetime" : ISODate("1998-01-13T00:00:00.000Z"),
            "ActionDescription" : "OPEN"
   }
}

Then your query will be allowed to use an index, you'll have to use a different query than count. A distinct on RecipientId sounds like a valid options.