Hello 
I have a collection with ~1 million documents and my documents have some geographic data (coordinates).
The field containing the coordinates has a 2D sphere index, but when I use a large range in my query, the performance decreases significantly.
Is there something I can do to improve my query?
{
"explainVersion": "1",
"stages": [
{
"$geoNearCursor": {
"queryPlanner": {
"namespace": "myDatabase.myCollection",
"indexFilterSet": false,
"parsedQuery": {
"address.coordinates": {
"$nearSphere": {
"type": "Point",
"coordinates": [
-39.99999999999996,
31.985213484470865
]
},
"$maxDistance": 7738757
}
},
"queryHash": "EEE5089B",
"planCacheKey": "D2C8378B",
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "GEO_NEAR_2DSPHERE",
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"indexVersion": 2,
"inputStages": [
{
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-8484781697966014464, -8484781697966014464]",
...
"[5458362748373041152, 5458362748373041152]"
]
}
}
},
{
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-8863084066665136128, -8863084066665136128]",
...
"[5980780305148018688, 5980780305148018688]"
]
}
}
},
{
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-9223372036854775807, -9079256848778919937]",
...
"[6557241057451442176, 6557241057451442176]"
]
}
}
},
{
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-9079256848778919935, -8935141660703064065]",
...
"[6557241057451442177, 6593269854470406143]"
]
}
}
}
]
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 1038653,
"executionTimeMillis": 12643,
"totalKeysExamined": 1038900,
"totalDocsExamined": 1038862,
"executionStages": {
"stage": "GEO_NEAR_2DSPHERE",
"nReturned": 1038653,
"executionTimeMillisEstimate": 3730,
"works": 2077585,
"advanced": 1038653,
"needTime": 1038931,
"needYield": 0,
"saveState": 2695,
"restoreState": 2695,
"isEOF": 1,
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"indexVersion": 2,
"searchIntervals": [
{
"minDistance": 0,
"maxDistance": 1745064.5992172293,
"maxInclusive": false,
"nBuffered": 970,
"nReturned": 254
},
{
"minDistance": 1745064.5992172293,
"maxDistance": 5235193.797651688,
"maxInclusive": false,
"nBuffered": 1005485,
"nReturned": 929329
},
{
"minDistance": 5235193.797651688,
"maxDistance": 6980258.396868917,
"maxInclusive": false,
"nBuffered": 32344,
"nReturned": 93204
},
{
"minDistance": 6980258.396868917,
"maxDistance": 7738757,
"maxInclusive": true,
"nBuffered": 63,
"nReturned": 15866
}
],
"inputStages": [
{
"stage": "FETCH",
"nReturned": 970,
"executionTimeMillisEstimate": 2,
"works": 976,
"advanced": 970,
"needTime": 5,
"needYield": 0,
"saveState": 2694,
"restoreState": 2694,
"isEOF": 1,
"docsExamined": 970,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 970,
"executionTimeMillisEstimate": 2,
"works": 976,
"advanced": 970,
"needTime": 5,
"needYield": 0,
"saveState": 2694,
"restoreState": 2694,
"isEOF": 1,
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-8484781697966014464, -8484781697966014464]",
...
"[5458362748373041152, 5458362748373041152]"
]
},
"keysExamined": 976,
"seeks": 6,
"dupsTested": 0,
"dupsDropped": 0
}
},
{
"stage": "FETCH",
"nReturned": 1005485,
"executionTimeMillisEstimate": 1970,
"works": 1005498,
"advanced": 1005485,
"needTime": 12,
"needYield": 0,
"saveState": 2693,
"restoreState": 2693,
"isEOF": 1,
"docsExamined": 1005485,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 1005485,
"executionTimeMillisEstimate": 825,
"works": 1005498,
"advanced": 1005485,
"needTime": 12,
"needYield": 0,
"saveState": 2693,
"restoreState": 2693,
"isEOF": 1,
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-8863084066665136128, -8863084066665136128]",
...
"[5980780305148018688, 5980780305148018688]"
]
},
"keysExamined": 1005498,
"seeks": 13,
"dupsTested": 0,
"dupsDropped": 0
}
},
{
"stage": "FETCH",
"nReturned": 32344,
"executionTimeMillisEstimate": 42,
"works": 32355,
"advanced": 32344,
"needTime": 10,
"needYield": 0,
"saveState": 205,
"restoreState": 205,
"isEOF": 1,
"docsExamined": 32344,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 32344,
"executionTimeMillisEstimate": 21,
"works": 32355,
"advanced": 32344,
"needTime": 10,
"needYield": 0,
"saveState": 205,
"restoreState": 205,
"isEOF": 1,
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-9223372036854775807, -9079256848778919937]",
...
"[6557241057451442176, 6557241057451442176]"
]
},
"keysExamined": 32355,
"seeks": 11,
"dupsTested": 0,
"dupsDropped": 0
}
},
{
"stage": "FETCH",
"nReturned": 63,
"executionTimeMillisEstimate": 0,
"works": 71,
"advanced": 63,
"needTime": 7,
"needYield": 0,
"saveState": 25,
"restoreState": 25,
"isEOF": 1,
"docsExamined": 63,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 63,
"executionTimeMillisEstimate": 0,
"works": 71,
"advanced": 63,
"needTime": 7,
"needYield": 0,
"saveState": 25,
"restoreState": 25,
"isEOF": 1,
"keyPattern": {
"address.coordinates": "2dsphere"
},
"indexName": "address.coordinates",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 2,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"address.coordinates": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"address.coordinates": [
"[-9079256848778919935, -8935141660703064065]",
...
"[6557241057451442177, 6593269854470406143]"
]
},
"keysExamined": 71,
"seeks": 8,
"dupsTested": 0,
"dupsDropped": 0
}
}
]
},
"allPlansExecution": []
}
},
"nReturned": 1038653,
"executionTimeMillisEstimate": 11423
}
],
"serverInfo": {
"host": "atlas-lo6otd-shard-00-02.cjrqe.mongodb.net",
"port": 27017,
"version": "6.0.10",
"gitVersion": "8e4b5670df9b9fe814e57cb5f3f8ee9407237b5a"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
},
"command": {
"aggregate": "myCollection",
"pipeline": [
{
"$geoNear": {
"near": {
"type": "Point",
"coordinates": [
-39.99999999999996,
31.985213484470865
]
},
"distanceField": "address.distCalculated",
"maxDistance": 7738757,
"spherical": true
}
}
],
"allowDiskUse": true,
"cursor": {},
"maxTimeMS": 60000,
"$db": "myDatabase"
},
"ok": 1,
"$clusterTime": {
"clusterTime": {
"$timestamp": {
"t": 1696259256,
"i": 1
}
},
"signature": {
"hash": {
"$binary": {
"base64": "UKqfJI4ISmrPawnwhP2nqP+kq1c=",
"subType": "00"
}
},
"keyId": {
"$numberLong": "7234179876700291074"
}
}
},
"operationTime": {
"$timestamp": {
"t": 1696259256,
"i": 1
}
}
}
Thank you in advance!
