How does MongoDB handle compound indexes of object properties nested in an array?

Question

I understand that Compound Multikey Indexes May Only Include One Array Field.

The following does not produce a "cannot index parallel arrays" error:

db.test.ensureIndex({"values.x": 1, "values.y": 1})

db.test.insert({"values": [ {"x": 1, "y": 2}, {"x": 2, "y": 2} ]})
db.test.insert({"values": [ {"x": 2, "y": 1}, {"x": 1, "y": 1} ]})
db.test.insert({"values": [ {"x": 1, "y": 1}, {"x": 1, "y": 1} ]})

So it seems that compound indexes are allowed over multiple object properties where the objects are nested in one array field.

The docs say that "MongoDB indexes each value in the array separately" so for the above scenario I expected index entries for all combinations of values.x and values.y in each document to be created.

However the following query on both nested fields suggests that only the first field in the compound index is used - nscanned is 2 suggesting that Mongo had to inspect the document added second to check for y = 2 on the element of the array that matched x = 2.

db.test.find({"values.x": 2, "values.y": 2}).explain()
{
    "cursor" : "BtreeCursor values.x_1_values.y_1",
    "isMultiKey" : true,
    "n" : 1,
    "nscannedObjects" : 2,
    "nscanned" : 2,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 2,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
            "values.x" : [
                    [
                            2,
                            2
                    ]
            ],
            "values.y" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ]
    },
    "server" : "localhost:27017"
}

What does MongoDB index and does the compound index have any value over one covering only the first field?

Answer 1

If you use an $elemMatch query operator to search for x and y values within the same element, you'll see that index bounds are also applied for y:

> db.test.find({ values: { $elemMatch: { x: 2, y: 2 }}}).explain()
{
    "cursor" : "BtreeCursor values.x_1_values.y_1",
    "isMultiKey" : true,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "values.x" : [
            [
                2,
                2
            ]
        ],
        "values.y" : [
            [
                2,
                2
            ]
        ]
    },
    "server" : "localhost:27017"
}

This was implemented for 2.4 in SERVER-3104. The ticket description explains why these index bounds cannot be used for your original query:

Mongo does not compute a cartesian product when creating a compound index on multiple fields. If the document { a:[ { b:1, c:2 }, { b:10, c:20 } ] } is indexed according to index { 'a.b':1, 'a.c':1 }, the index keys created are { '':1, '':2 } and { '':10, '':20 }. (There is no index key { '':1, '':20 } for example.)

Now, suppose we have a query { 'a.b':1, 'a.c':20 }. This query is supposed to match the document, because an 'a.b' value of 1 exists in the document, and an 'a.c' value of 20 exists in the document. However, there is no index key containing both 1 in the 'a.b' position and 20 in the 'a.c' position. As a result, the index bounds on 'a.b' will be [[ 1, 1 ]] but there will not be any index bounds on 'a.c'. This means the index key { '':1, '':2 } will be retrieved and used to find the full document, and the Matcher will determine that the full document matches the query