I have a user with a collection of documents with points and timestamps. He performs a geo query on the points and a sort on the timestamps.
I thought it would be useful to build a compound index with timestamp first and position second (timestamp_-1_position_2dsphere) and use that in the sort. However, the explain shows that the sort does not recognize that the timestamp is first in the index and instead goes straight to an S2Cursor.
I recreated the user environment with the following javascript:
for(var i = 0; i < 100000; i++){ xrand = Math.random()*179+1; yrand = Math.random()*89+1; x = xrand.toFixed(2); y = yrand.toFixed(2); t = new Date(); doc = { position: { type: "Point", coordinates: [parseFloat(x), parseFloat(y)] }, timestamp: t }; db.randomcoordinates.insert(doc); } db.randomcoordinates.ensureIndex({timestamp:-1,position:'2dsphere'})
and executed the following query and explain:
localhost(mongod-2.4.6) test> db.randomcoordinates.find({position: {$geoWithin: {$geometry: {type: "Polygon", coordinates: [[[1, 1], [1, 90], [180, 90], [180, 1], [1, 1]]]}}}}).sort({timestamp: -1}).limit(5).hint("timestamp_-1_position_2dsphere").explain()
{
"cursor": "S2Cursor",
"isMultiKey": true,
"n": 5,
"nscannedObjects": 47285,
"nscanned": 114006,
"nscannedObjectsAllPlans": 47285,
"nscannedAllPlans": 114006,
"scanAndOrder": true,
"indexOnly": false,
"nYields": 2,
"nChunkSkips": 0,
"millis": 1272,
"indexBounds": {
},
"nscanned": 114006,
"matchTested": NumberLong("66721"),
"geoTested": NumberLong("66721"),
"cellsInCover": NumberLong("14"),
"server": "localhost:27017"
}
As you can see an S2Cursor was used, scanAndOrder is true, and it does not appear to use the timestamp index. It is perfectly happy however to use timestamp as a single index.
- is duplicated by
-
SERVER-10971 Mongodb compound 2dsphere index dosen't work as expected
-
- Closed
-
-
-
- Closed
-
- is related to
-
SERVER-13899 "Whole index scan" query solutions can use incompatible indexes, return incorrect results
-
- Closed
-
-
SERVER-13908 Whole index scan on sparse index should be able to provide a sort
-
- Backlog
-