CODEWITHSUNDEEP
mongodbindexing
peter.petrov
peter.petrov

Reputation: 39457

MongoDB - interpret particular explain output

I am using MongoDB version 2.4.8.

[test] 2014-03-25 14:42:13.0 >>> db.users.getIndexes();
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "test.users",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "username" : 1,
                        "age" : 1
                },
                "ns" : "test.users",
                "name" : "username_1_age_1"
        },
        {
                "v" : 1,
                "key" : {
                        "age" : 1,
                        "username" : 1
                },
                "ns" : "test.users",
                "name" : "age_1_username_1"
        }
]
[test] 2014-03-25 14:44:36.550 >>>

[test] 2014-03-25 14:33:12.945 >>> db.users.find({"age" : 14, "username" : /.*/}).explain()
{
        "cursor" : "BtreeCursor age_1_username_1 multi",
        "isMultiKey" : false,
        "n" : 16850,
        "nscannedObjects" : 16850,
        "nscanned" : 16850,
        "nscannedObjectsAllPlans" : 16850,
        "nscannedAllPlans" : 16850,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 86,
        "indexBounds" : {
                "age" : [
                        [
                                14,
                                14
                        ]
                ],
                "username" : [
                        [
                                "",
                                {

                                }
                        ],
                        [
                                /.*/,
                                /.*/
                        ]
                ]
        },
        "server" : "server01:27017"
}

What does the explain output mean in its username part?

            "username" : [
                    [
                            "",
                            {

                            }
                    ],
                    [
                            /.*/,
                            /.*/
                    ]
            ]

I have troubles understanding this part no
matter if I look at it formally and informally.

Upvotes: 2

Views: 218

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

The output there is particular to a regex that is not bound to the starting position of the string. So for a regex that is going to scan the index and not the collection (even though it will scan the whole index in this case) there needs to be a set of starting bounds and ending bounds:

Consider the first query with a different regex:

db.collection.find({ "username": /bob/ }).explain()

    "indexBounds" : {
            "username" : [
                    [
                            "",
                            {

                            }
                    ],
                    [
                            /bob/,
                            /bob/
                    ]
            ]
    },

So that signifies there is a the whole string to search through and then the match will end on something that contains "bob" as part of the string. So the first part is the "lexical" match bounds and the second part is the actual regex to be applied:

The following query shows this more clearly:

db.collection.find({ username: /^bob/ }).explain()

    "indexBounds" : {
            "username" : [
                    [
                            "bob",
                            "boc"
                    ],
                    [
                            /^bob/,
                            /^bob/
                    ]
            ]
    },

Since that is anchored to the start of the string, the only entries of the index that need to be tested match "lexically" between "bob" and "boc". The regex is again contained as the second part of the bounds.

The bounds entries are generally described as "two part" elements internally, and there is this case for regular expressions, which in the first part has the string bounds which makes sense for matching the index, and then for the regex to apply to those matching entires.

As a final note then see the following:

db.collection.find({ username: {$gt: ""} }).explain()

    "indexBounds" : {
            "username" : [
                    [
                            "",
                            {

                            }
                    ]
            ]
    },

Which is intrinsically the same as your initial query, that says to match any string.

Upvotes: 1

Related Questions