Why is mongodb not using full index

Question

I have a collection with one 4 key compound index:

> db.event.getIndexes()
[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
    },
    {
        "v" : 2,
        "key" : {
            "epochWID" : 1,
            "category" : 1,
            "mos.types" : 1,
            "mos.name" : 1
        },
        "name" : "epochWID_category_motype_movalue",
    }
]

Query is as follows:

> db.event.explain().find({ "epochWID": 1510456188087, "category": 6, "mos.types": 9, "mos.name": "ctx_1" })
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "category" : {
                        "$eq" : 6
                    }
                },
                {
                    "epochWID" : {
                        "$eq" : 1510456188087
                    }
                },
                {
                    "mos.name" : {
                        "$eq" : "ctx_1"
                    }
                },
                {
                    "mos.types" : {
                        "$eq" : 9
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "mos.name" : {
                    "$eq" : "ctx_1"
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "epochWID" : 1,
                    "category" : 1,
                    "mos.types" : 1,
                    "mos.name" : 1
                },
                "indexName" : "epochWID_category_motype_movalue",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "epochWID" : [ ],
                    "category" : [ ],
                    "mos.types" : [
                        "mos",
                        "mos.types"
                    ],
                    "mos.name" : [
                        "mos"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "epochWID" : [
                        "[1510456188087.0, 1510456188087.0]"
                    ],
                    "category" : [
                        "[6.0, 6.0]"
                    ],
                    "mos.types" : [
                        "[9.0, 9.0]"
                    ],
                    "mos.name" : [
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "version" : "3.4.9",
    },
    "ok" : 1
}

Now if you look at the plan's indexBounds: it uses the first 3 keys but not the 4th mos.name, why?

                "indexBounds" : {
                    "epochWID" : [
                        "[1510456188087.0, 1510456188087.0]"
                    ],
                    "category" : [
                        "[6.0, 6.0]"
                    ],
                    "mos.types" : [
                        "[9.0, 9.0]"
                    ],
                    "mos.name" : [
                        "[MinKey, MaxKey]"
                    ]
                }

I think it might be because, inside 'mos', 'types' is an array, while 'name' is a string. Can anyone confirm and suggest a way to make this work? — na_ka_na, Nov 12 '17 at 21:05
Flipping the order of keys in the index: 'mos.name' and then 'mos.types' didn't help. It still uses the first 3 keys in the index. Event if it had worked, that is not the solution I'm looking for. — na_ka_na, Nov 12 '17 at 21:12

na_ka_na · Accepted Answer · 2017-11-27T23:39:11.060

Based on https://docs.mongodb.com/manual/core/index-multikey/#compound-multikey-indexes we need to use $elemMatch, so following query uses the full index

> db.event.explain().find({ "epochWID": 1510456188087, "category": 6, "mos": { $elemMatch: {"types": 9, "name": "ctx_1"} } })
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "mos" : {
                        "$elemMatch" : {
                            "$and" : [
                                {
                                    "name" : {
                                        "$eq" : "ctx_1"
                                    }
                                },
                                {
                                    "types" : {
                                        "$eq" : 9
                                    }
                                }
                            ]
                        }
                    }
                },
                {
                    "category" : {
                        "$eq" : 6
                    }
                },
                {
                    "epochWID" : {
                        "$eq" : 1510456188087
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "mos" : {
                    "$elemMatch" : {
                        "$and" : [
                            {
                                "types" : {
                                    "$eq" : 9
                                }
                            },
                            {
                                "name" : {
                                    "$eq" : "ctx_1"
                                }
                            }
                        ]
                    }
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "epochWID" : 1,
                    "category" : 1,
                    "mos.types" : 1,
                    "mos.name" : 1
                },
                "indexName" : "epochWID_category_motype_movalue",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "epochWID" : [ ],
                    "category" : [ ],
                    "mos.types" : [
                        "mos",
                        "mos.types"
                    ],
                    "mos.name" : [
                        "mos"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "epochWID" : [
                        "[1510456188087.0, 1510456188087.0]"
                    ],
                    "category" : [
                        "[6.0, 6.0]"
                    ],
                    "mos.types" : [
                        "[9.0, 9.0]"
                    ],
                    "mos.name" : [
                        "[\"ctx_1\", \"ctx_1\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "version" : "3.4.9",
    },
    "ok" : 1
}

EDIT: I contacted MongoDb support. Regarding multi key indexes and array fields - tl;dr is - An index is fine as long as only one of the indexed fields ever contains an array value (which is true in my case). Nesting level doesn't matter. The problem is indeed parallel arrays due to need of cartesian product.

score 0 · Answer 2 · answered Nov 12 '17 at 21:15

0

A multi-key index cannot be across multiple arrays within a document. See the Limitations and reasoning in the documentation https://docs.mongodb.com/manual/core/index-multikey/#compound-multikey-indexes

answered Nov 12 '17 at 21:15

Kevin Smith

13,746
4
52
77

I read the documentation, its talking about 2 parallel arrays. Not sure if my case is similar. My document looks like : {epochWID: 100, category: 22, mos: [{types: [10,11], name: "abc"}]}. – na_ka_na Nov 12 '17 at 21:22
1

@na_ka_na Which is exactly what the documentation says. You have "an array within an array. So `"mos.types"` counts as `2` compounded combinations. So it's not just limited to `{ "a": [1,2], "b": [3,4] }` since `{ "a": [{ "b": [1,2 ] },{ "b": [3,4] }] }` does indeed account for the same rule. Yet another reason you really should not "nest" arrays. Your data "should" be more "denormalised", and repeating the same values of the "whole array" for each value of `"types"`. It might seem counter-intuitive but it is what is "actually supported". – Neil Lunn Nov 12 '17 at 22:42
@NeilLunn I don't know if that follows from the documentation I read. See my posted answer. – na_ka_na Nov 13 '17 at 00:13
@na_ka_na Your answer is not correct. `$elemMacth` has nothing to do with this. Yes you "should" be using that to match "multiple conditions on an array", but it won't affect the efficiency of the index selection chosen. So you are basically misunderstanding what `"indexBounds"` is actually telling you. It still breaks the rule. – Neil Lunn Nov 13 '17 at 00:17
@NeilLunn, you may be right but can you point me to the documentation? Are you basically saying that in your example above { "a": [{ "b": [1,2 ] },{ "b": [3,4] }] }, Mongodb does not allow index on "a.b"? Also why do you claim that what "indexBounds" is saying is not representative? Feel free to write a new answer. – na_ka_na Nov 13 '17 at 00:36
@na_ka_na You already have been pointed to the documentation. I gave you an example to "clarify" that this is in fact "the same thing". You seem to think this applies to something different **only**. What you're being told is that "it does not". No point going around in circles. What you "think" solves it, does not actually "solve" anything. You still have the compound limitation. But note the limitation is `2` and you presently have `2` and not `3`. – Neil Lunn Nov 13 '17 at 00:48
@NeilLunn all I'm asking is, where is your proof? You're claiming something, but not giving any proof. At least, with my answer "indexBounds" changed. You say it doesn't mean what I think it means. But where is your proof? – na_ka_na Nov 13 '17 at 00:56
@NeilLunn, I contacted Mongodb support. See my edit. – na_ka_na Nov 27 '17 at 23:39

Why is mongodb not using full index

2 Answers2