MongoDB multikey index performance

Question

Background

I have a collection of users with structure of documents like this:

{
    "_id" : ObjectId("54e61137cca5d2ff0a8b4567"),
    "login" : "test1",
    "emails" : [
        {
            "email" : "test1@example.com",
            "is_primary" : true,
            "_id" : ObjectId("57baf3e97323afb2688e639c")
        },
        {
            "email" : "test1_1@example.com",
            "is_primary" : false,
            "_id" : ObjectId("57baf3e97323afb2688e639d")
        }
    ]
}

Indexes:

{
    "v" : 1,
    "key" : {
        "login" : 1
    },
    "name" : "login_1",
    "ns" : "mydb.users",
    "background" : true
},
{
    "v" : 1,
    "key" : {
        "emails.email" : 1
    },
    "name" : "emails.email_1",
    "ns" : "mydb.users"
}

Count of documents is ~700000

Scenario

To explain the search of users by login, I make this:

rs0:PRIMARY> db.users.explain('executionStats').find({'login' : /test123123123/})
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "mydb.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "login" : /test123123123/
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "filter" : {
                    "login" : /test123123123/
                },
                "keyPattern" : {
                    "login" : 1
                },
                "indexName" : "login_1",
                "isMultiKey" : false,
                "direction" : "forward",
                "indexBounds" : {
                    "login" : [
                        "[\"\", {})",
                        "[/test123123123/, /test123123123/]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 1040,
        "totalKeysExamined" : 698993,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 930,
            "works" : 698994,
            "advanced" : 0,
            "needTime" : 698993,
            "needFetch" : 0,
            "saveState" : 5460,
            "restoreState" : 5460,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 0,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "filter" : {
                    "login" : /test123123123/
                },
                "nReturned" : 0,
                "executionTimeMillisEstimate" : 920,
                "works" : 698993,
                "advanced" : 0,
                "needTime" : 698993,
                "needFetch" : 0,
                "saveState" : 5460,
                "restoreState" : 5460,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "login" : 1
                },
                "indexName" : "login_1",
                "isMultiKey" : false,
                "direction" : "forward",
                "indexBounds" : {
                    "login" : [
                        "[\"\", {})",
                        "[/test123123123/, /test123123123/]"
                    ]
                },
                "keysExamined" : 698993,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "myhost",
        "port" : 27017,
        "version" : "3.0.12",
        "gitVersion" : "33934938e0e95d534cebbaff656cde916b9c3573"
    },
    "ok" : 1
}

As you can see executionStats.executionStages.inputStage.nReturned is 0 and executionStats.totalDocsExamined is so 0. It's ok, I guess there is no documents with login like entered. But if I want search users by email I'll do next:

rs0:PRIMARY> db.users.explain('executionStats').find({'emails.email' : /test123123123/})
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "mydb.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "emails.email" : /test123123123/
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "emails.email" : /test123123123/
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "emails.email" : 1
                },
                "indexName" : "emails.email_1",
                "isMultiKey" : true,
                "direction" : "forward",
                "indexBounds" : {
                    "emails.email" : [
                        "[\"\", {})",
                        "[/test123123123/, /test123123123/]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 7666,
        "totalKeysExamined" : 699016,
        "totalDocsExamined" : 698993,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "emails.email" : /test123123123/
            },
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 7355,
            "works" : 699017,
            "advanced" : 0,
            "needTime" : 699016,
            "needFetch" : 0,
            "saveState" : 5462,
            "restoreState" : 5462,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 698993,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 698993,
                "executionTimeMillisEstimate" : 1630,
                "works" : 699016,
                "advanced" : 698993,
                "needTime" : 23,
                "needFetch" : 0,
                "saveState" : 5462,
                "restoreState" : 5462,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "emails.email" : 1
                },
                "indexName" : "emails.email_1",
                "isMultiKey" : true,
                "direction" : "forward",
                "indexBounds" : {
                    "emails.email" : [
                        "[\"\", {})",
                        "[/test123123123/, /test123123123/]"
                    ]
                },
                "keysExamined" : 699016,
                "dupsTested" : 699016,
                "dupsDropped" : 23,
                "seenInvalidated" : 0,
                "matchTested" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "myhost",
        "port" : 27017,
        "version" : "3.0.12",
        "gitVersion" : "33934938e0e95d534cebbaff656cde916b9c3573"
    },
    "ok" : 1
}

And here executionStats.executionStages.inputStage.nReturned (and executionStats.totalDocsExamined) is equal 698993 (executionStats.nReturned is 0 like in first query)

Question

Why when I use search with multikey index (users.user) on the ixscan stage returns all my collection and fetch stage occurs all collection. But If I use search by non-multikey index (login) ixscan stage scans expected values and on the fetch stage I give what I want.

UPD: when I use regular expression not like /smth/, but /^smth/ then scan by emails.email field returns also 0 elements. Why multikey and ordinary index give me different results for regular expression like /smth/ ?

score 1 · Answer 1 · answered Oct 28 '16 at 05:23

Because it is multikey index. explained here

When a query filter specifies an exact match for an array as a whole, MongoDB can use the multikey index to look up the first element of the query array but cannot use the multikey index scan to find the whole array. Instead, after using the multikey index to look up the first element of the query array, MongoDB retrieves the associated documents and filters for documents whose array matches the array in the query.

MongoDB multikey index performance

1 Answers1