Why is this $elemMatch query not using my index?

Question

My query:

{
    "unique_contact_method.enrichments": {
        "$not": {
            "$elemMatch": {
                "created_by.name": "fullcontact"
            }
        }
    }
}

My Index:

{
    v: 1,
    name: "unique_contact_method.enrichments.created_by.name_1",
    key: {
        "unique_contact_method.enrichments.created_by.name": 1
    },
    ns: "app27434806.unique_contact_methods",
    background: true,
    safe: true
}

The .explain() result:

enter image description here

Why no index?

Neil Lunn · Accepted Answer

The use of the $not operator here is what makes index usage impossible. There is one statement in the documentation that "implies" this, if not completely clearly:

"Remember that the $not operator only affects other operators and cannot check fields and documents independently. So, use the $not operator for logical disjunctions and the $ne operator to test the contents of fields directly."

The essential phrase there is "cannot check fields", which means it does not actually "test" the value of the field as can be done with an index. A simple document explains this the best:

{ 
    "_id" : ObjectId("53f3e414deee3a78e47e57e2"), 
    "created" : [ { "name" : "Bill" }, { "name" : "Ted" } ]
}

Where of course an index is created on "created.name".

Now consider the following query and explain output:

db.doctest.find({ "created": { "$elemMatch": { "name": "Bill" } } }).explain()

{
    "cursor" : "BtreeCursor created.name_1",
    "isMultiKey" : true,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
            "created.name" : [
                    [
                            "Bill",
                            "Bill"
                    ]
            ]
    },
    "server" : "ubuntu:27017",
    "filterSet" : false
}

That simply selects the index and shows the index bounds as expected.

Not look at this with $not, and I'm going to "force" the index with .hint():

db.doctest.find({ "created": { "$not": { "$elemMatch": { "name": "Bill" } } } }).hint({ "created.name": 1 }).explain()
{
    "cursor" : "BtreeCursor created.name_1",
    "isMultiKey" : true,
    "n" : 0,
    "nscannedObjects" : 1,
    "nscanned" : 2,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 2,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
            "created.name" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ]
    },
    "server" : "ubuntu:27017",
    "filterSet" : false
}

The important part to look at here is "indexBounds". This explains why without the hint the index would not be used, as simply put there are no "bounds" to select by. The $not operation basically says:

"Look at every value tested by the condition and if it is true then consider it false or essentially the reverse"

The end evaluation here is that "Ted" is not "Bill" therefore the condition is true, but there is no way to "look for that" using an index.

So the consideration here is how do you do the same thing and use an index? The passage from the documentation tells you that in order to consider the "field" you need to use the $ne operator instead:

db.doctest.find({ "created": { "$elemMatch": { "name": { "$ne": "Bill" } } } }).explain()
{
    "cursor" : "BtreeCursor created.name_1",
    "isMultiKey" : true,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 2,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 2,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
            "created.name" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            "Bill"
                    ],
                    [
                            "Bill",
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ]
    },
    "server" : "ubuntu:27017",
    "filterSet" : false
}

Now the "indexBounds" shows you that the index is used to essentially "filter out" the values that were supplied. So the index is used to pull any other value than "Bill".

The conclusion here is that $not has it's logical uses, but in many cases what you actually want is $ne instead. Where $not must be applied, take into consideration that and index for the field values will not be used to make the comparison.

Why is this $elemMatch query not using my index?

Answers (2)

Run on mongo shell, V2.6.4

Related Questions