2 / 2
Jul 2024

I have 25 million records in one collection. But when one queries our db it shows many results about documents return.
Query profiler:

{ "type": "command", "ns": "Lumin_v2.documents", "command": { "find": "documents", "filter": { "$and": [ { "_id": { "$in": [ /// large array. Around 300 elements ] } } ] } }, "planSummary": "IXSCAN { _id: 1 }", "planningTimeMicros": 282294, "keysExamined": 5820, "docsExamined": 2910, "hasSortStage": true, "fromMultiPlanner": true, "replanned": true, "replanReason": "cached plan was less efficient than expected: expected trial execution to take 7 works but it took at least 70 works", "nBatches": 1, "cursorExhausted": true, "numYields": 22, "nreturned": 61, "queryHash": "8BC5D288", "planCacheKey": "07C75EDD", "queryFramework": "classic", "reslen": 35744, "locks": { "FeatureCompatibilityVersion": { "acquireCount": { "r": 23 } }, "Global": { "acquireCount": { "r": 23 } } }, "readConcern": { "level": "local", "provenance": "implicitDefault" }, "storage": { "data": { "bytesRead": 161122834, "timeReadingMicros": 25809 } }, "cpuNanos": 282727748, "remote": "172.31.157.96:44937", "protocol": "op_msg", "durationMillis": 283, "v": "7.0.11", "isTruncated": true }

Image attached to the result of documents return

MongoDB Atlas version: 7.0.11

14 days later

Hi @Nguyen_Nhat_Tan thank you for your question and welcome to the MongoDB community!

Can you provide a bit more information on the question? It looks like you have multiple queries here that are using an index, but it looks like some of these queries might not be the most efficient as they are scanning 18.2k docs yet only returning a few. Do you see any index recommendations on the “Lumin_v2.documents” namespace?

Thanks!
Frank