indexing - MongoDB index intersection

Question

Hey I want to evaluate the performance of index intersection but I'm not able to get an intersection between two indices. I've inserted some dummy records into my DB along this manual. http://docs.mongodb.org/manual/core/index-intersection/

Insert code:

for(var i=0;i<1000;i++){
    for(var j=0;j<100;j++){
        db.t.insert({item:"abc"+i,qty:j})
    }
}

Indices:

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "db.t"
    },
    {
        "v" : 1,
        "key" : {
            "qty" : 1
        },
        "name" : "qty_1",
        "ns" : "db.t"
    },
    {
        "v" : 1,
        "key" : {
            "item" : 1
        },
        "name" : "item_1",
        "ns" : "db.t"
    }
]

Query:

db.t.find({item:"abc123",qty:{$gt:15}}).explain()

Result of explain:

{
    "cursor" : "BtreeCursor item_1",
    "isMultiKey" : false,
    "n" : 84,
    "nscannedObjects" : 100,
    "nscanned" : 100,
    "nscannedObjectsAllPlans" : 201,
    "nscannedAllPlans" : 305,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 2,
    "nChunkSkips" : 0,
    "millis" : 1,
    "indexBounds" : {
        "item" : [
            [
                "abc123",
                "abc123"
            ]
        ]
    },
    "server" : "brews18:27017",
    "filterSet" : false
}

My question is why mongo is only using item as an index an does not use an intersection.

Thanks in advance

Answer 1

Well it actually does even though it does not in this case. To really see what is happening you need to look at the "verbose" form of explain, by adding true:

db.t.find({item:"abc123",qty:{$gt:15}}).explain(true)
{
    "cursor" : "BtreeCursor item_1",
    "isMultiKey" : false,
    "n" : 84,
    "nscannedObjects" : 100,
    "nscanned" : 100,
    "nscannedObjectsAllPlans" : 201,
    "nscannedAllPlans" : 304,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 2,
    "nChunkSkips" : 0,
    "millis" : 2,
    "indexBounds" : {
            "item" : [
                    [
                            "abc123",
                            "abc123"
                    ]
            ]
    },
    "allPlans" : [
            {
                    "cursor" : "BtreeCursor item_1",
                    "isMultiKey" : false,
                    "n" : 84,
                    "nscannedObjects" : 100,
                    "nscanned" : 100,
                    "scanAndOrder" : false,
                    "indexOnly" : false,
                    "nChunkSkips" : 0,
                    "indexBounds" : {
                            "item" : [
                                    [
                                            "abc123",
                                            "abc123"
                                    ]
                            ]
                    }
            },
            {
                    "cursor" : "BtreeCursor qty_1",
                    "isMultiKey" : false,
                    "n" : 0,
                    "nscannedObjects" : 101,
                    "nscanned" : 102,
                    "scanAndOrder" : false,
                    "indexOnly" : false,
                    "nChunkSkips" : 0,
                    "indexBounds" : {
                            "qty" : [
                                    [
                                            15,
                                            Infinity
                                    ]
                            ]
                    }
            },
            {
                    "cursor" : "Complex Plan",
                    "n" : 0,
                    "nscannedObjects" : 0,
                    "nscanned" : 102,
                    "nChunkSkips" : 0
            }
    ],

Cut short, but the last part is what you are looking for. As explained in the manual, the appearance of "Complex Plan" means an intersection is being used.

            {
                    "cursor" : "Complex Plan",
                    "n" : 0,
                    "nscannedObjects" : 0,
                    "nscanned" : 102,
                    "nChunkSkips" : 0
            }

The only case here is that while it is being "looked at" it is not being chosen by the optimizer in this case as the most "optimal" query. So the optimizer is saying that in fact the plan using just the one selected index, is the one that will complete in the most responsive fashion.

So while the "intersection" was considered, it was not the "best fit" and the single index was chosen.