Why MongoDB doesn't use Index Intersection?

Question

I am trying to reproduce the first example of index intersection instruction (http://docs.mongodb.org/manual/core/index-intersection/) but facing a problem: mongo doesn't uses both indexes

My steps:

Download mongo (3.0.3) and install it
Run mongod: mongod.exe --dbpath d:\data (folder is empty)
Run mongo: mongo.exe

Add indexes:

db.orders.ensureIndex({ qty: 1 })
db.orders.ensureIndex({ item: 1 })
db.orders.getIndexes()
[{
        "v" : 1,
        "key" : {
                "_id" : 1
        },
        "name" : "_id_",
        "ns" : "test.orders"
},
{
        "v" : 1,
        "key" : {
                "qty" : 1
        },
        "name" : "qty_1",
        "ns" : "test.orders"
},
{
        "v" : 1,
        "key" : {
                "item" : 1
        },
        "name" : "item_1",
        "ns" : "test.orders"
}]

Check query explain:

db.orders.find( { item: "abc123", qty: { $gt: 15 } } ).explain()
{
    "queryPlanner" : {
            "plannerVersion" : 1,
            "namespace" : "test.orders",
            "indexFilterSet" : false,
            "parsedQuery" : {
                    "$and" : [
                            {
                                    "item" : {
                                            "$eq" : "abc123"
                                    }
                            },
                            {
                                    "qty" : {
                                            "$gt" : 15
                                    }
                            }
                    ]
            },
            "winningPlan" : {
                    "stage" : "KEEP_MUTATIONS",
                    "inputStage" : {
                            "stage" : "FETCH",
                            "filter" : {
                                    "qty" : {
                                            "$gt" : 15
                                    }
                            },
                            "inputStage" : {
                                    "stage" : "IXSCAN",
                                    "keyPattern" : {
                                            "item" : 1
                                    },
                                    "indexName" : "item_1",
                                    "isMultiKey" : false,
                                    "direction" : "forward",
                                    "indexBounds" : {
                                            "item" : [
                                                    "[\"abc123\", \"abc123\"]"
                                            ]
                                    }
                            }
                    }
            },
            "rejectedPlans" : [
                    {
                            "stage" : "KEEP_MUTATIONS",
                            "inputStage" : {
                                    "stage" : "FETCH",
                                    "filter" : {
                                            "item" : {
                                                    "$eq" : "abc123"
                                            }
                                    },
                                    "inputStage" : {
                                            "stage" : "IXSCAN",
                                            "keyPattern" : {
                                                    "qty" : 1
                                            },
                                            "indexName" : "qty_1",
                                            "isMultiKey" : false,
                                            "direction" : "forward",
                                            "indexBounds" : {
                                                    "qty" : [
                                                            "(15.0, 1.#INF]"
                                                    ]
                                            }
                                    }
                            }
                    }
            ]
    },
    "serverInfo" : {
            "host" : "localhost",
            "port" : 27017,
            "version" : "3.0.3",
            "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105"
    },
    "ok" : 1
}

As you can see winningPlan contains only item_1 index. There is rejectedPlans which contains qty_1 index. But there is no plans which contains index intersection. I know that there are a lot of conditions to select specific index. But in my case mongo doesn't even plans it!

Could anybody help me?

How many documents are in the database? What does `explain(true)` say? How long does the query take? What is the distribution of the data fields, if any? — mnemosyn, Jun 03 '15 at 19:10

score 4 · Accepted Answer · answered Jun 03 '15 at 18:56

There are some details about the index selection in the SERVER-3071 JIRA issue but I cannot say if all is still relevant for 3.0. Anyway:

MongoDB 3.0.2 seems not consider index interaction for range query. But it will for point intervals:

> db.orders.find( { item: {$eq : "abc123"}, qty: { $eq: 15 } } ).explain()
...

        {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "KEEP_MUTATIONS",
                "inputStage" : {
                    "stage" : "AND_SORTED",
                    "inputStages" : [
                        {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "qty" : 1
                            },
                            "indexName" : "qty_1",
                            "isMultiKey" : false,
                            "direction" : "forward",
                            "indexBounds" : {
                                "qty" : [
                                    "[15.0, 15.0]"
                                ]
                            }
                        },
                        {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "item" : 1
                            },
                            "indexName" : "item_1",
                            "isMultiKey" : false,
                            "direction" : "forward",
                            "indexBounds" : {
                                "item" : [
                                    "[\"abc123\", \"abc123\"]"
                                ]
                            }
                        }
                    ]
                }

so basically index intersection seems to never happens in real life queries? Does not work either with $in queries with more than 1 item, according to my tests :( — Sebastien Lorber, Dec 23 '16 at 12:03
Further observations: `hint()` does not work with a prefix of a composite index (entire composite index only); index intersection appears not to work with composite index prefixes (single indexes only) — mils, Dec 14 '17 at 02:09

score 1 · Answer 2 · answered Jun 30 '17 at 08:31

We are hosting a multi-tenant site. So there is a mongo collection holding some info about all customers, for example,

{customerId: 22, category: "category", region: "region", balance: 23434... }

After we learnt about Index Intersection, we tried to create multiple single field indexes to support queries with different query criteria with variable fields, such as

{ customerId: 22, category: "1" }
{ customerId: 22, region: "somewhere" }
{ customerId: 22, balance: {$gt:0} }
{ customerId: 22, region: {$in: ["R1", "R2"]},balance: {$gt:0} }
....

But it simply turned out no intersection happened by learning from "db.collection.explain". As the fields are limited, we finally went for a solution to build a compound index includes all fields.

{customerId: 1, category: 1, region: 1, balance:1...}

And then it turned out that below all queries use the "large" compound index, as far as "customerId" is in the query.

{ customerId: 22, category: "1" }
{ customerId: 22, region: "somewhere" }
{ customerId: 22, balance: {$gt:0} }
{ customerId: 22, region: {$in: ["R1", "R2"]},balance: {$gt:0} }
....

Just adding such a large compound index won't help much, though. Of the four query examples you provide, the last three only use the `customerId` prefix of the compound index, while the first one uses the `customerId` plus `category` prefix of the compound index. If you were trying to index for those four queries, a compound index on just the first two fields would be better. Just sayin' so no-one gets the idea that a massive compound index will speed up arbitrary queries filtering on any of the fields included in it :) — DanielSmedegaardBuus, Sep 26 '17 at 11:01

Why MongoDB doesn't use Index Intersection?

2 Answers2

Linked