When using $elemMatch, why is MongoDB performing a FETCH stage if index is covering the filter?

user_1083855 0
Viewed 593

I have the following table : 

> db.foo.find()
{ "_id" : 1, "k" : [ { "a" : 50, "b" : 10 } ] }
{ "_id" : 2, "k" : [ { "a" : 90, "b" : 80 } ] }

With an compound index on k field :

"key" : {
        "k.a" : 1,
        "k.b" : 1
},
"name" : "k.a_1_k.b_1"

If I run the following query :

db.foo.aggregate([ 
    { $match:   { "k.a" : 50 } },
    { $project: { _id : 0, "dummy": {$literal:""} }} 
])

The index if used (make sense) and there is no need of FETCH stage :

"winningPlan" : {
        "stage" : "COUNT_SCAN",
        "keyPattern" : {
                "k.a" : 1,
                "k.b" : 1
        },
        "indexName" : "k.a_1_k.b_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
                "k.a" : [ ],
                "k.b" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "indexBounds" : {
                "startKey" : {
                        "k.a" : 50,
                        "k.b" : { "$minKey" : 1 }
                },
                "startKeyInclusive" : true,
                "endKey" : {
                        "k.a" : 50,
                        "k.b" : { "$maxKey" : 1 }
                },
                "endKeyInclusive" : true
        }
}

However, If I run the following query that use $elemMatch :

db.foo.aggregate([  
   { $match:   { k: {$elemMatch: {a : 50, b : { $in : [5, 6, 10]}}}} },
   { $project: { _id : 0, "dummy" : {$literal : ""}} } 
])

There is a FETCH stage (which AFAIK is not necessary) : 

"winningPlan" : {
        "stage" : "FETCH",
        "filter" : {
                "k" : {
                        "$elemMatch" : {
                                "$and" : [
                                        { "a" : { "$eq" : 50 } },
                                        { "b" : { "$in" : [ 5, 6, 10 ] } }
                                ]
                        }
                }
        },
        "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                        "k.a" : 1,
                        "k.b" : 1
                },
                "indexName" : "k.a_1_k.b_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                        "k.a" : [ ],
                        "k.b" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                        "k.a" : [
                                "[50.0, 50.0]"
                        ],
                        "k.b" : [
                                "[5.0, 5.0]",
                                "[6.0, 6.0]",
                                "[10.0, 10.0]"
                        ]
                }
        }
},

I am using MongoDB 3.4. I ask this because I have a DB with lot of documents and there is a query that use aggregate() and $elemMatch (it perform more useful things than projecting nothing as in this question OFC, but theoretically things does not require a FETCH stage). I found out main reason of query being slow if is the FETCH stage, which AFAIK is not needed.

Is there some logic that force MongoDB to use FETCH when $elemMatch is used, or is it just a missing optimization ?

mongodb fetch indexing optimization
2 Answers
user_98471370

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

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.

Let's imagine the following scenario:

//doc1
{
   "k" : [ { "a" : 50, "b" : 10 } ]
}
//doc2
{
  "k" : { "a" : 50, "b" : 10 }
}

Because Mongo "flattens" the array it indexes, once the index is built Mongo cannot differentiate between these 2 documents, $elemMatch specifically requires an array object to match (i.e doc2 will never match an $elemMatch query). Meaning Mongo is forced to FETCH the documents to determine which docs will match, this is the premise causing the behaviour you see.

user_107703700
Related