I have a compound index in a 2d and normal field:
"key" : {
"_meta.loc.coordinates" : "2d",
"_meta.unifiedAt" : 1
},
Then I want to execute the following query with sorting:
db.static_booking
.find({ "_meta.loc.coordinates": { "$geoWithin": { "$box": [ [ -0.70724367, 51.13817111 ], [ 0.45524367, 51.86182889 ] ] } } }, {itemId: 1})
.sort({'_meta.unifiedAt': 1})
.limit(100)
The problem I have is that it takes a very long time to return the results. Checking the explain, I see that it does not use the compound index created:
> db.static_booking.find({ "_meta.loc.coordinates": { "$geoWithin": { "$box": [ [ -0.70724367, 51.13817111 ], [ 0.45524367, 51.86182889 ] ] } } }, {itemId: 1}).sort({'_meta.unifiedAt': 1}).limit(100).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "seetransparent.static_booking",
"indexFilterSet" : false,
"parsedQuery" : {
"_meta.loc.coordinates" : {
"$geoWithin" : {
"$box" : [
[
-0.70724367,
51.13817111
],
[
0.45524367,
51.86182889
]
]
}
}
},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 100,
"inputStage" : {
"stage" : "PROJECTION",
"transformBy" : {
"itemId" : 1
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_meta.loc.coordinates" : {
"$geoWithin" : {
"$box" : [
[
-0.70724367,
51.13817111
],
[
0.45524367,
51.86182889
]
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_meta.unifiedAt" : 1
},
"indexName" : "_meta.unifiedAt_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"_meta.unifiedAt" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_meta.unifiedAt" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION",
"transformBy" : {
"itemId" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"_meta.unifiedAt" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_meta.loc.coordinates" : {
"$geoWithin" : {
"$box" : [
[
-0.70724367,
51.13817111
],
[
0.45524367,
51.86182889
]
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_meta.loc.coordinates" : "2d"
},
"indexName" : "_meta.loc.coordinates_2d",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_meta.loc.coordinates" : [
"[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
"[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
"[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
"[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
"[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
"[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
"[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
"[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
"[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
"[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
"[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
"[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
"[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
"[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
"[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
"[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
]
}
}
}
}
}
},
{
"stage" : "PROJECTION",
"transformBy" : {
"itemId" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"_meta.unifiedAt" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_meta.loc.coordinates" : {
"$geoWithin" : {
"$box" : [
[
-0.70724367,
51.13817111
],
[
0.45524367,
51.86182889
]
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_meta.loc.coordinates" : "2d",
"_meta.delisted" : 1
},
"indexName" : "_meta.loc.coordinates_2d__meta.delisted_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_meta.loc.coordinates" : [
"[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
"[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
"[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
"[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
"[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
"[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
"[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
"[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
"[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
"[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
"[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
"[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
"[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
"[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
"[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
"[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
],
"_meta.delisted" : [
"[MinKey, MaxKey]"
]
}
}
}
}
}
},
{
"stage" : "PROJECTION",
"transformBy" : {
"itemId" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"_meta.unifiedAt" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_meta.loc.coordinates" : {
"$geoWithin" : {
"$box" : [
[
-0.70724367,
51.13817111
],
[
0.45524367,
51.86182889
]
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_meta.loc.coordinates" : "2d",
"_meta.delisted" : 1,
"_id" : 1
},
"indexName" : "_meta.loc.coordinates_2d__meta.delisted_1__id_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_meta.loc.coordinates" : [
"[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
"[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
"[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
"[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
"[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
"[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
"[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
"[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
"[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
"[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
"[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
"[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
"[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
"[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
"[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
"[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
],
"_meta.delisted" : [
"[MinKey, MaxKey]"
],
"_id" : [
"[MinKey, MaxKey]"
]
}
}
}
}
}
},
{
"stage" : "PROJECTION",
"transformBy" : {
"itemId" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"_meta.unifiedAt" : 1
},
"limitAmount" : 100,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_meta.loc.coordinates" : {
"$geoWithin" : {
"$box" : [
[
-0.70724367,
51.13817111
],
[
0.45524367,
51.86182889
]
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_meta.loc.coordinates" : "2d",
"_meta.unifiedAt" : 1
},
"indexName" : "_meta.loc.coordinates_2d__meta.unifiedAt_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"_meta.loc.coordinates" : [
"[BinData(128, 6EBA300000000000), BinData(128, 6EBA3FFFFFFFFFFF)]",
"[BinData(128, 6EBA680000000000), BinData(128, 6EBA6BFFFFFFFFFF)]",
"[BinData(128, 6EBA6C0000000000), BinData(128, 6EBA6FFFFFFFFFFF)]",
"[BinData(128, 6EBA700000000000), BinData(128, 6EBA7FFFFFFFFFFF)]",
"[BinData(128, 6EBA900000000000), BinData(128, 6EBA9FFFFFFFFFFF)]",
"[BinData(128, 6EBAB00000000000), BinData(128, 6EBABFFFFFFFFFFF)]",
"[BinData(128, 6EBAC00000000000), BinData(128, 6EBACFFFFFFFFFFF)]",
"[BinData(128, 6EBAD00000000000), BinData(128, 6EBADFFFFFFFFFFF)]",
"[BinData(128, 6EBAE00000000000), BinData(128, 6EBAEFFFFFFFFFFF)]",
"[BinData(128, 6EBAF00000000000), BinData(128, 6EBAFFFFFFFFFFFF)]",
"[BinData(128, C410100000000000), BinData(128, C4101FFFFFFFFFFF)]",
"[BinData(128, C410300000000000), BinData(128, C4103FFFFFFFFFFF)]",
"[BinData(128, C410400000000000), BinData(128, C4104FFFFFFFFFFF)]",
"[BinData(128, C410500000000000), BinData(128, C4105FFFFFFFFFFF)]",
"[BinData(128, C410600000000000), BinData(128, C4106FFFFFFFFFFF)]",
"[BinData(128, C410700000000000), BinData(128, C4107FFFFFFFFFFF)]"
],
"_meta.unifiedAt" : [
"[MinKey, MaxKey]"
]
}
}
}
}
}
}
]
},
"serverInfo" : {
"host" : "production",
"port" : 27017,
"version" : "3.4.3",
"gitVersion" : "f07437fb5a6cca07c10bafa78365456eb1d6d5e1"
},
"ok" : 1
}
As it is not using the index that I created, I try to use the hint method to force the index, but then I get an error in the result:
> db.static_booking.find({ "_meta.loc.coordinates": { "$geoWithin": { "$box": [ [ -0.70724367, 51.13817111 ], [ 0.45524367, 51.86182889 ] ] } } }, {itemId: 1}).sort({'_meta.unifiedAt': 1}).limit(100).hint({'_meta.loc.coordinates': '2d', '_meta.unifiedAt': 1})
Error: error: {
"ok" : 0,
"errmsg" : "Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.",
"code" : 96,
"codeName" : "OperationFailed"
}
As long as I use a limit larger than 2, then the error will appear. How can I solve this issue? Thanks.
Best Answer
You are hitting the limit of in memory sort which is 32MB.
Sort Operations
You can add an index with leading column
_meta.unifiedAt.