Major - P3 Test data:
db.test.insert({
point: { "type": "MultiPolygon",
"coordinates": [
[
[[30, 20], [45, 40], [10, 40], [30, 20]]
],
[
[[15, 5], [40, 10], [10, 20], [5, 10], [15, 5]]
]
]
},
array: [
{
value1: 1,
value2: 2
},
{
value1: 5,
value2: 10
}
]
})
Test index:
db.test.ensureIndex({'array.value1':1,'array.value2':1,point:"2dsphere"})
Test query:
db.test.find({ point: { $geoWithin: { $geometry: { "type": "MultiPolygon", "coordinates": [ [ [[30, 20], [45, 40], [10, 40], [30, 20]] ], [ [[15, 5], [40, 10], [10, 20], [5, 10], [15, 5]] ] ] } } }, array: {$elemMatch:{value1:1, value2:2}} }).hint("array.value1_1_array.value2_1_point_2dsphere").explain()
The explain() output has very bad index bounds, which renders the index useless:
"indexBounds" : {
"array.value1" : [
"[MinKey, MaxKey]"
],
"array.value2" : [
"[MinKey, MaxKey]"
],
"point" : [
"[MinKey, MaxKey]"
]
}
If I don't use $elemMatch, the results are a bit better, but array.value2 bounds are not set:
db.test.find({ point: { $geoWithin: { $geometry: { "type": "MultiPolygon", "coordinates": [ [ [[30, 20], [45, 40], [10, 40], [30, 20]] ], [ [[15, 5], [40, 10], [10, 20], [5, 10], [15, 5]] ] ] } } }, 'array.value1':1, 'array.value2':2 }).hint("array.value1_1_array.value2_1_point_2dsphere").explain()
....
"indexBounds" : {
"array.value1" : [
"[1.0, 1.0]"
],
"array.value2" : [
"[MinKey, MaxKey]"
],
"point" : [
"[\"0f200\", \"0f201\")",
"[\"0f2012\", \"0f2013\")",
"[\"0f2013\", \"0f2014\")",
"[\"0f202\", \"0f203\")",
"[\"0f2030\", \"0f2031\")",
"[\"0f2031\", \"0f2032\")",
"[\"0f2112333\", \"0f2112334\")",
"[\"0f212\", \"0f213\")",
"[\"0f2130\", \"0f2131\")",
"[\"0f220\", \"0f221\")",
"[\"0f221\", \"0f222\")",
"[\"0f222\", \"0f223\")",
"[\"0f22311\", \"0f22312\")",
"[\"0f230\", \"0f231\")",
"[\"0f231\", \"0f232\")",
"[\"0f232\", \"0f233\")",
"[\"2f0000\", \"2f0001\")",
"[\"2f0003\", \"2f0004\")",
"[\"2f0010\", \"2f0011\")",
"[\"2f0011000\", \"2f0011000\"]",
"[\"2f00110000\", \"2f00110001\")"
]
}
...
- duplicates
-
SERVER-23065 Geo predicate beneath $elemMatch object causes planner to ignore valid index with 2dsphereIndexVersion > 1
-
- Closed
-