MongoDB find query very slow even with index

Question

I don't understand why the following query is so slow, the explain shows it will take 74+ seconds to finish, in spite of the presence of index.

both dev_id and _id are indexed I can assure you, it seems just not helpful at all.

db.DeviceLoginLog.find({"dev_id": "xxx"}).skip(0).limit(10).sort({"_id": -1}).explain("executionStats");

Can anyone help me to interpret explain output and advise me how to speed up?

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "example.DeviceLoginLog",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "dev_id" : {
                                "$eq" : "xxx"
                        }
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "_id" : -1
                        },
                        "limitAmount" : 10,
                        "inputStage" : {
                                "stage" : "KEEP_MUTATIONS",
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "dev_id" : 1
                                                },
                                                "indexName" : "dev_id_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "dev_id" : [
                                                                "[\"xxx\", \"xxx\"]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "LIMIT",
                                "limitAmount" : 4,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "dev_id" : {
                                                        "$eq" : "xxx"
                                                }
                                        },
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "_id" : 1
                                                },
                                                "indexName" : "_id_",
                                                "isMultiKey" : false,
                                                "direction" : "backward",
                                                "indexBounds" : {
                                                        "_id" : [
                                                                "[MaxKey, MinKey]"
                                                        ]
                                                }
                                        }
                                }
                        }
                ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 10,
                "executionTimeMillis" : 74867,
                "totalKeysExamined" : 9203,
                "totalDocsExamined" : 9203,
                "executionStages" : {
                        "stage" : "SORT",
                        "nReturned" : 10,
                        "executionTimeMillisEstimate" : 49860,
                        "works" : 9537,
                        "advanced" : 10,
                        "needTime" : 9204,
                        "needFetch" : 321,
                        "saveState" : 1864,
                        "restoreState" : 1864,
                        "isEOF" : 1,
                        "invalidates" : 1064,
                        "sortPattern" : {
                                "_id" : -1
                        },
                        "memUsage" : 1890,
                        "memLimit" : 33554432,
                        "limitAmount" : 10,
                        "inputStage" : {
                                "stage" : "KEEP_MUTATIONS",
                                "nReturned" : 9203,
                                "executionTimeMillisEstimate" : 49820,
                                "works" : 9525,
                                "advanced" : 9203,
                                "needTime" : 0,
                                "needFetch" : 321,
                                "saveState" : 1864,
                                "restoreState" : 1864,
                                "isEOF" : 1,
                                "invalidates" : 1064,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "nReturned" : 9203,
                                        "executionTimeMillisEstimate" : 49820,
                                        "works" : 9525,
                                        "advanced" : 9203,
                                        "needTime" : 0,
                                        "needFetch" : 321,
                                        "saveState" : 1864,
                                        "restoreState" : 1864,
                                        "isEOF" : 1,
                                        "invalidates" : 1064,
                                        "docsExamined" : 9203,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 9203,
                                                "executionTimeMillisEstimate" : 10,
                                                "works" : 9204,
                                                "advanced" : 9203,
                                                "needTime" : 0,
                                                "needFetch" : 0,
                                                "saveState" : 1864,
                                                "restoreState" : 1864,
                                                "isEOF" : 1,
                                                "invalidates" : 1064,
                                                "keyPattern" : {
                                                        "dev_id" : 1
                                                },
                                                "indexName" : "dev_id_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "dev_id" : [
                                                                "[\"xxx\", \"xxx\"]"
                                                        ]
                                                },
                                                "keysExamined" : 9203,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0,
                                                "matchTested" : 0
                                        }
                                }
                        }
                }
        },
        "serverInfo" : {
                "host" : "iZ231ear7c9Z",
                "port" : 27017,
                "version" : "3.0.3",
                "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105"
        },
        "ok" : 1
}

Thanks in advance！

Answer 1

The query used index {dev_id: 1} to find and then had to fetch matched docs into memory to sort without using an index. Create a compound index as {dev_id: 1, _id: -1} will improve performance.

See sort-and-non-prefix-subset-of-an-index.

Answer 2

"totalKeysExamined" : 9203,
"totalDocsExamined" : 9203,

That shows that the query had to examine each individual document. You aren't using an index on the _id field. Also depending on which fields you are returning, consider creating an index that allows for a covered query.

A covered query is a query that can be satisfied entirely using an index and does not have to examine any documents. An index covers a query when both of the following apply:

• all the fields in the query are part of an index, and
• all the fields returned in the results are in the same index.

https://docs.mongodb.com/manual/core/query-optimization/