MongoDB Index usage differs for same query on different hosts

user_863192 0
Viewed 44

I am running a very simple query: db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25), where teamIds is an array and has an ascending index on it. If I run this query on a secondary, explain shows that 25 keys are looked at, and 25 documents are looked at (as expected). If I run, the EXACT same query on the primary, explain shows that 50K+ keys and documents are looked at. I've checked that both the primary and secondary have the same indexes, and that both are being used. Below is the output of explain in both cases.

What could possibly cause this behaviour?

Is it possible that on the primary, the index is not fully loaded into memory? The primary is using about 65% of memory, the secondary around 55% (both of 8GB).

Edit:

I've just discovered that the primary is on 3.6.8 and the secondary in question (which is behaving the way I want) is on 3.6.7 - could this be the cause?

Primary

rs0:PRIMARY> db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25).explain({verbosity: "executionStats"})
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbName.users",
        "indexFilterSet" : false,
        "parsedQuery" : {

        },
        "winningPlan" : {
            "stage" : "PROJECTION",
            "transformBy" : {
                "_id" : 1
            },
            "inputStage" : {
                "stage" : "SORT",
                "sortPattern" : {
                    "teamIds" : 1
                },
                "limitAmount" : 25,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "teamIds" : 1
                            },
                            "indexName" : "teamIds_1",
                            "isMultiKey" : true,
                            "multiKeyPaths" : {
                                "teamIds" : [
                                    "teamIds"
                                ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "teamIds" : [
                                    "[MinKey, MaxKey]"
                                ]
                            }
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 25,
        "executionTimeMillis" : 182,
        "totalKeysExamined" : 52320,
        "totalDocsExamined" : 51412,
        "executionStages" : {
            "stage" : "PROJECTION",
            "nReturned" : 25,
            "executionTimeMillisEstimate" : 180,
            "works" : 52348,
            "advanced" : 25,
            "needTime" : 52322,
            "needYield" : 0,
            "saveState" : 408,
            "restoreState" : 408,
            "isEOF" : 1,
            "invalidates" : 0,
            "transformBy" : {
                "_id" : 1
            },
            "inputStage" : {
                "stage" : "SORT",
                "nReturned" : 25,
                "executionTimeMillisEstimate" : 170,
                "works" : 52348,
                "advanced" : 25,
                "needTime" : 52322,
                "needYield" : 0,
                "saveState" : 408,
                "restoreState" : 408,
                "isEOF" : 1,
                "invalidates" : 0,
                "sortPattern" : {
                    "teamIds" : 1
                },
                "memUsage" : 25242,
                "memLimit" : 33554432,
                "limitAmount" : 25,
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "nReturned" : 51412,
                    "executionTimeMillisEstimate" : 140,
                    "works" : 52322,
                    "advanced" : 51412,
                    "needTime" : 909,
                    "needYield" : 0,
                    "saveState" : 408,
                    "restoreState" : 408,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "nReturned" : 51412,
                        "executionTimeMillisEstimate" : 100,
                        "works" : 52321,
                        "advanced" : 51412,
                        "needTime" : 908,
                        "needYield" : 0,
                        "saveState" : 408,
                        "restoreState" : 408,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 51412,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 51412,
                            "executionTimeMillisEstimate" : 40,
                            "works" : 52321,
                            "advanced" : 51412,
                            "needTime" : 908,
                            "needYield" : 0,
                            "saveState" : 408,
                            "restoreState" : 408,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "teamIds" : 1
                            },
                            "indexName" : "teamIds_1",
                            "isMultiKey" : true,
                            "multiKeyPaths" : {
                                "teamIds" : [
                                    "teamIds"
                                ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "teamIds" : [
                                    "[MinKey, MaxKey]"
                                ]
                            },
                            "keysExamined" : 52320,
                            "seeks" : 1,
                            "dupsTested" : 52320,
                            "dupsDropped" : 908,
                            "seenInvalidated" : 0
                        }
                    }
                }
            }
        },
        "allPlansExecution" : [ ]
    },
    "serverInfo" : {
        "host" : "mongo1.justplayss.com",
        "port" : 27017,
        "version" : "3.6.8",
        "gitVersion" : "6bc9ed599c3fa164703346a22bad17e33fa913e4"
    },
    "ok" : 1
}

Secondary

rs0:SECONDARY> db.users.find({}, { _id: 1 }).sort({teamIds: 1}).limit(25).explain({verbosity: "executionStats"})
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbName.users",
        "indexFilterSet" : false,
        "parsedQuery" : {

        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 25,
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "_id" : 1
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "teamIds" : 1
                        },
                        "indexName" : "teamIds_1",
                        "isMultiKey" : true,
                        "multiKeyPaths" : {
                            "teamIds" : [
                                "teamIds"
                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 1,
                        "direction" : "forward",
                        "indexBounds" : {
                            "teamIds" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 25,
        "executionTimeMillis" : 6,
        "totalKeysExamined" : 25,
        "totalDocsExamined" : 25,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 25,
            "executionTimeMillisEstimate" : 0,
            "works" : 26,
            "advanced" : 25,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "limitAmount" : 25,
            "inputStage" : {
                "stage" : "PROJECTION",
                "nReturned" : 25,
                "executionTimeMillisEstimate" : 0,
                "works" : 25,
                "advanced" : 25,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 0,
                "invalidates" : 0,
                "transformBy" : {
                    "_id" : 1
                },
                "inputStage" : {
                    "stage" : "FETCH",
                    "nReturned" : 25,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 25,
                    "advanced" : 25,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "docsExamined" : 25,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "nReturned" : 25,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 25,
                        "advanced" : 25,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 0,
                        "invalidates" : 0,
                        "keyPattern" : {
                            "teamIds" : 1
                        },
                        "indexName" : "teamIds_1",
                        "isMultiKey" : true,
                        "multiKeyPaths" : {
                            "teamIds" : [
                                "teamIds"
                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 1,
                        "direction" : "forward",
                        "indexBounds" : {
                            "teamIds" : [
                                "[MinKey, MaxKey]"
                            ]
                        },
                        "keysExamined" : 25,
                        "seeks" : 1,
                        "dupsTested" : 25,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0
                    }
                }
            }
        },
        "allPlansExecution" : [ ]
    },
    "serverInfo" : {
        "host" : "ip-172-31-6-96",
        "port" : 27017,
        "version" : "3.4.15",
        "gitVersion" : "52e5b5fbaa3a2a5b1a217f5e647b5061817475f9"
    },
    "ok" : 1
}
mongodb
1 Answers

This comes down to: "What does it mean to sort documents by an a field containing an array?"

Consider 3 documents:

{a: [ 3, 4, 5 ]}
{a: [ 2, 1, 4 ]}
{a: [ 1, 3, 2 ]}

If we sort on {a:1} there are a couple different ways they might come back:

Sorting by the binary representation of the array, which in this case gives the same result as comparing the first element of each, gives:

{a: [ 1, 3, 2 ]}
{a: [ 2, 1, 4 ]}
{a: [ 3, 4, 5 ]}

An index on a field containing an array has a separate key for each element of the array, not a key for the array value itself.

If we create an index on field a, it would contain 9 keys:

1 - record 2
1 - record 3
2 - record 2
2 - record 3
3 - record 1
3 - record 3
4 - record 1
5 - record 1

So if we have use that index used for the sort, we the documents would be ordered:

{a: [ 2, 1, 4 ]}
{a: [ 1, 3, 2 ]}
{a: [ 3, 4, 5 ]}

This means that if we use a limit(1) to find just the first document, our result changes depending on the available indexes and the index chosen by the planner, which can vary depending on the query part.

The sort order also varies between find and aggregate.

This problem was identified and fixed in MongoDB 3.6 by restricting when a multikey index can be used for sorting. The result is that most sorts on array fields are now blocking in-memory operations, which represents a significant reduction in performance in return for consistent sorts and results.

The explain output you included shows that the secondary node is using the index for the sort, while the primary node is loading all of the documents and performing an in-memory sort.

user_22826340
Related