Hi,
Data in someCollection : 100000 records
someCollection size : 7GB
Using atlas : M30 (8 GB RAM, 52 GB Storage) 3,000 IOPS, Encrypted, Auto-expand Storage
I am running a find query like following
db.someCollection.find({someId :{$in: [ <260 objectIds> ] }}).sort({_id:-1}).
Index applied : {someId:1,_id:-1}
Atlas is hosted in ca-central. I making the query from India. The query take more than 5 mins to run even though the index is applied. What is the possible issue ? Is the filter too large for the data ? Doesn’t mongo support large filter value? What are the possible solutions?
Primary node query execution plan:
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "someDb.someCollection",
"indexFilterSet": false,
"parsedQuery": {
"someId": {
"$in": [
]
}
},
"queryHash": "937709B1",
"planCacheKey": "8A586950",
"winningPlan": {
"stage": "FETCH",
"inputStage": {
"stage": "SORT",
"sortPattern": {
"_id": -1
},
"memLimit": 104857600,
"type": "default",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"someId": 1,
"_id": -1
},
"indexName": "someId_1__id_-1",
"isMultiKey": false,
"multiKeyPaths": {
"someId": [],
"_id": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"someId": [
],
"_id": [
"[MaxKey, MinKey]"
]
}
}
}
},
"rejectedPlans": [
{
"stage": "SORT",
"sortPattern": {
"_id": -1
},
"memLimit": 104857600,
"type": "simple",
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"someId": 1
},
"indexName": "someId_1",
"isMultiKey": false,
"multiKeyPaths": {
"someId": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"someId": [
]
}
}
}
},
{
"stage": "FETCH",
"filter": {
"someId": {
"$in": [
]
}
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"_id": 1
},
"indexName": "_id_",
"isMultiKey": false,
"multiKeyPaths": {
"_id": []
},
"isUnique": true,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "backward",
"indexBounds": {
"_id": [
"[MaxKey, MinKey]"
]
}
}
},
{
"stage": "FETCH",
"filter": {
"someId": {
"$in": [
]
}
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"_id": -1,
"someId": 1
},
"indexName": "_id_-1_someId_1",
"isMultiKey": false,
"multiKeyPaths": {
"_id": [],
"someId": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"_id": [
"[MaxKey, MinKey]"
],
"someId": [
"[MinKey, MaxKey]"
]
}
}
}
]
},
"serverInfo": {
"host": "some.mongodb.net",
"port": 27017,
"version": "4.4.25",
"gitVersion": ""
},
"ok": 1,
"$clusterTime": {
"clusterTime": "10/9/2023 20:37:07.000 (#1)",
"signature": {
"hash": "BinData(0,\"//VVuk=\")",
"keyId": "someIddd"
}
},
"operationTime": "10/9/2023 20:37:07.000 (#1)"
}