Hi
I have just tested the 2.6 and i always have the problem describe in teh Jira SERVER-9257 for my case. In this comment i use the query and index detailed in the description of the jira SERVER-9257.
Case 1 : old index
With the old "2d" index the query on position+date don't use the 2d index but only the index on creationDate.
db.ntr_tracks.find({"detection.position": { $geoWithin: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain(); |
{
|
"cursor" : "BtreeCursor creationDate", |
"isMultiKey" : false, |
"n" : 1000, |
"nscannedObjects" : 47625, |
"nscanned" : 47626, |
"nscannedObjectsAllPlans" : 52620, |
"nscannedAllPlans" : 52621, |
"scanAndOrder" : false, |
"indexOnly" : false, |
"nYields" : 411, |
"nChunkSkips" : 0, |
"millis" : 216, |
"indexBounds" : { |
"creationDate" : [ |
[
|
ISODate("2012-08-01T00:00:00.000Z"), |
ISODate("2012-08-02T00:00:00.000Z") |
]
|
]
|
},
|
"server" : "fmwd0012:27017", |
"filterSet" : false, |
"stats" : { |
"type" : "LIMIT", |
"works" : 47625, |
"yields" : 411, |
"unyields" : 411, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 46625, |
"needFetch" : 0, |
"isEOF" : 1, |
"children" : [ |
{
|
"type" : "FETCH", |
"works" : 47625, |
"yields" : 411, |
"unyields" : 411, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 46625, |
"needFetch" : 0, |
"isEOF" : 0, |
"alreadyHasObj" : 0, |
"forcedFetches" : 0, |
"matchTested" : 1000, |
"children" : [ |
{
|
"type" : "IXSCAN", |
"works" : 47625, |
"yields" : 411, |
"unyields" : 411, |
"invalidates" : 0, |
"advanced" : 47625, |
"needTime" : 0, |
"needFetch" : 0, |
"isEOF" : 0, |
"keyPattern" : "{ creationDate: 1 }", |
"boundsVerbose" : "field #0['creationDate']: (new Date(1343779200000), new Date(1343865600000))", |
"isMultiKey" : 0, |
"yieldMovedCursor" : 0, |
"dupsTested" : 0, |
"dupsDropped" : 0, |
"seenInvalidated" : 0, |
"matchTested" : 0, |
"keysExamined" : 47626, |
"children" : [] |
}
|
]
|
}
|
]
|
}
|
}
|
If i force using the 2d index with hint the query doesn't respond (i have +16Go of data). I think that the index isn't used.
db.ntr_tracks.find({"detection.position": { $geoWithin: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).hint('detection.position_2d_creationDate_1').limit(1000).explain() |
Edit: i finally have a result:
{
|
"cursor" : "GeoBrowse-box", |
"isMultiKey" : false, |
"n" : 1000, |
"nscannedObjects" : 400694, |
"nscanned" : 400694, |
"nscannedObjectsAllPlans" : 400694, |
"nscannedAllPlans" : 400694, |
"scanAndOrder" : true, |
"indexOnly" : false, |
"nYields" : 6618, |
"nChunkSkips" : 0, |
"millis" : 1903392, |
"indexBounds" : { |
"detection.position" : [ |
[
|
[
|
1.40625,
|
50.625
|
],
|
[
|
2.8125,
|
52.03125
|
]
|
],
|
[
|
[
|
1.0546875,
|
50.2734375
|
],
|
[
|
1.40625,
|
50.625
|
]
|
],
|
[
|
[
|
0.703125,
|
50.2734375
|
],
|
[
|
1.0546875,
|
50.625
|
]
|
],
|
[
|
[
|
0.703125,
|
50.625
|
],
|
[
|
1.40625,
|
51.328125
|
]
|
],
|
[
|
[
|
1.0546875,
|
51.328125
|
],
|
[
|
1.40625,
|
51.6796875
|
]
|
],
|
[
|
[
|
0.703125,
|
51.328125
|
],
|
[
|
1.0546875,
|
51.6796875
|
]
|
],
|
[
|
[
|
2.4609375,
|
50.2734375
|
],
|
[
|
2.8125,
|
50.625
|
]
|
],
|
[
|
[
|
2.109375,
|
50.2734375
|
],
|
[
|
2.4609375,
|
50.625
|
]
|
],
|
[
|
[
|
1.7578125,
|
50.2734375
|
],
|
[
|
2.109375,
|
50.625
|
]
|
],
|
[
|
[
|
1.40625,
|
50.2734375
|
],
|
[
|
1.7578125,
|
50.625
|
]
|
]
|
]
|
},
|
"server" : "fmwd0012:27017", |
"filterSet" : false, |
"stats" : { |
"type" : "SORT", |
"works" : 401697, |
"yields" : 6618, |
"unyields" : 6618, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 400696, |
"needFetch" : 0, |
"isEOF" : 1, |
"forcedFetches" : 0, |
"memUsage" : 580000, |
"memLimit" : 33554432, |
"children" : [ |
{
|
"type" : "KEEP_MUTATIONS", |
"works" : 400696, |
"yields" : 6618, |
"unyields" : 6618, |
"invalidates" : 0, |
"advanced" : 1786, |
"needTime" : 398909, |
"needFetch" : 0, |
"isEOF" : 1, |
"children" : [ |
{
|
"type" : "FETCH", |
"works" : 400696, |
"yields" : 6618, |
"unyields" : 6618, |
"invalidates" : 0, |
"advanced" : 1786, |
"needTime" : 398909, |
"needFetch" : 0, |
"isEOF" : 1, |
"alreadyHasObj" : 400694, |
"forcedFetches" : 0, |
"matchTested" : 1786, |
"children" : [ |
{
|
"type" : "GEO_2D", |
"works" : 400694, |
"yields" : 0, |
"unyields" : 0, |
"invalidates" : 0, |
"advanced" : 400694, |
"needTime" : 0, |
"needFetch" : 0, |
"isEOF" : 1, |
"geometryType" : "box", |
"field" : "detection.position", |
"boundsVerbose" : [ |
"(1.40625,50.625) -->> (2.8125,52.0313)", |
"(1.05469,50.2734) -->> (1.40625,50.625)", |
"(0.703125,50.2734) -->> (1.05469,50.625)", |
"(0.703125,50.625) -->> (1.40625,51.3281)", |
"(1.05469,51.3281) -->> (1.40625,51.6797)", |
"(0.703125,51.3281) -->> (1.05469,51.6797)", |
"(2.46094,50.2734) -->> (2.8125,50.625)", |
"(2.10938,50.2734) -->> (2.46094,50.625)", |
"(1.75781,50.2734) -->> (2.10938,50.625)", |
"(1.40625,50.2734) -->> (1.75781,50.625)" |
],
|
"children" : [] |
}
|
]
|
}
|
]
|
}
|
]
|
}
|
}
|
Case 2 : new index, date first - query on date
With a second test where i have the following index only :
{ "creationDate": 1, "detection.position": "2dsphere" } |
The following query
db.ntr_tracks.find({creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") }}).sort({ creationDate: 1 }).limit(1000).explain() |
Take 1168608 ms .... (but if i create an index on creationDate the query take less than 100ms).
The explain show that we use BtreeCursor on 2dsphere is used :
{
|
"cursor" : "BtreeCursor 2dsphere", |
"isMultiKey" : false, |
"n" : 1000, |
"nscannedObjects" : 11629283, |
"nscanned" : 11629284, |
"nscannedObjectsAllPlans" : 11629283, |
"nscannedAllPlans" : 11629284, |
"scanAndOrder" : false, |
"indexOnly" : false, |
"nYields" : 433063, |
"nChunkSkips" : 0, |
"millis" : 118608, |
"indexBounds" : { |
"creationDate" : [ |
[
|
{
|
"$minElement" : 1 |
},
|
{
|
"$maxElement" : 1 |
}
|
]
|
],
|
"detection.position" : [ |
[
|
{
|
"$minElement" : 1 |
},
|
{
|
"$maxElement" : 1 |
}
|
]
|
]
|
},
|
"server" : "fmwd0012:27017", |
"filterSet" : false, |
"stats" : { |
"type" : "LIMIT", |
"works" : 12062212, |
"yields" : 433063, |
"unyields" : 433063, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 11628283, |
"needFetch" : 432929, |
"isEOF" : 1, |
"children" : [ |
{
|
"type" : "FETCH", |
"works" : 12062212, |
"yields" : 433063, |
"unyields" : 433063, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 11628283, |
"needFetch" : 432929, |
"isEOF" : 0, |
"alreadyHasObj" : 0, |
"forcedFetches" : 0, |
"matchTested" : 1000, |
"children" : [ |
{
|
"type" : "IXSCAN", |
"works" : 11629283, |
"yields" : 433063, |
"unyields" : 433063, |
"invalidates" : 0, |
"advanced" : 11629283, |
"needTime" : 0, |
"needFetch" : 0, |
"isEOF" : 0, |
"keyPattern" : "{ creationDate: 1, detection.position: \"2dsphere\" }", |
"boundsVerbose" : "field #0['creationDate']: [MinKey, MaxKey], field #1['detection.position']: [MinKey, MaxKey]", |
"isMultiKey" : 0, |
"yieldMovedCursor" : 0, |
"dupsTested" : 0, |
"dupsDropped" : 0, |
"seenInvalidated" : 0, |
"matchTested" : 0, |
"keysExamined" : 11629284, |
"children" : [] |
}
|
]
|
}
|
]
|
}
|
}
|
Case 3 : new index, date first - query on date and position
If i make query that search by position, the query is very quick (so no problem, it is better than 2.4)
db.ntr_tracks.find({"detection.position": { $geoWithin: { $geometry: { type: "Polygon", coordinates: [ [ [ 0.7724536441932998, 50.61956018018893 ], [ 0.7724536441932998, 51.42851043057254 ], [ 2.483031806726593, 51.42851043057254 ], [ 2.483031806726593, 50.61956018018893 ], [ 0.7724536441932998, 50.61956018018893 ] ] ] } } }, creationDate: { $gt: new ISODate("20121001T000000"), $lt: ISODate("20121002T000000") } }).sort({ creationDate: 1 }).limit(1000).explain() |
|
|
{
|
"cursor" : "BtreeCursor 2dsphere", |
"isMultiKey" : false, |
"n" : 1000, |
"nscannedObjects" : 1752, |
"nscanned" : 47634, |
"nscannedObjectsAllPlans" : 1752, |
"nscannedAllPlans" : 47634, |
"scanAndOrder" : false, |
"indexOnly" : false, |
"nYields" : 14, |
"nChunkSkips" : 0, |
"millis" : 184, |
"indexBounds" : { |
"creationDate" : [ |
[
|
ISODate("2012-08-01T00:00:00.000Z"), |
ISODate("2012-08-02T00:00:00.000Z") |
]
|
],
|
"detection.position" : [ |
[
|
"2f0332302", |
"2f0332303" |
],
|
[
|
"2f0332312", |
"2f0332312" |
],
|
[
|
"2f03323123", |
"2f03323123" |
],
|
[
|
"2f033231233", |
"2f033231234" |
],
|
[
|
"2f0332313", |
"2f0332314" |
],
|
[
|
"2f0332320", |
"2f0332321" |
],
|
[
|
"2f0332321", |
"2f0332322" |
],
|
[
|
"2f0332322", |
"2f0332322" |
],
|
[
|
"2f03323220", |
"2f03323221" |
],
|
[
|
"2f0332323", |
"2f0332324" |
],
|
[
|
"2f0332330", |
"2f0332331" |
],
|
[
|
"2f0332331", |
"2f0332332" |
]
|
]
|
},
|
"server" : "fmwd0012:27017", |
"filterSet" : false, |
"stats" : { |
"type" : "LIMIT", |
"works" : 1752, |
"yields" : 14, |
"unyields" : 14, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 752, |
"needFetch" : 0, |
"isEOF" : 1, |
"children" : [ |
{
|
"type" : "FETCH", |
"works" : 1752, |
"yields" : 14, |
"unyields" : 14, |
"invalidates" : 0, |
"advanced" : 1000, |
"needTime" : 752, |
"needFetch" : 0, |
"isEOF" : 0, |
"alreadyHasObj" : 0, |
"forcedFetches" : 0, |
"matchTested" : 1000, |
"children" : [ |
{
|
"type" : "IXSCAN", |
"works" : 1752, |
"yields" : 14, |
"unyields" : 14, |
"invalidates" : 0, |
"advanced" : 1752, |
"needTime" : 0, |
"needFetch" : 0, |
"isEOF" : 0, |
"keyPattern" : "{ creationDate: 1, detection.position: \"2dsphere\" }", |
"boundsVerbose" : "field #0['creationDate']: (new Date(1343779200000), new Date(1343865600000)), field #1['detection.position']: [\"2f0332302\", \"2f0332303\"), [\"2f0332312\", \"2f0332312\"], [\"2f03323123\", \"2f03323123\"], [\"2f033231233\", \"2f033231234\"), [\"2f0332313\", \"2f0332314\"), [\"2f0332320\", \"2f0332321\"), [\"2f0332321\", \"2f0332322\"), [\"2f0332322\", \"2f0332322\"], [\"2f03323220\", \"2f03323221\"), [\"2f0332323\", \"2f0332324\"), [\"2f0332330\", \"2f0332331\"), [\"2f0332331\", \"2f0332332\")", |
"isMultiKey" : 0, |
"yieldMovedCursor" : 0, |
"dupsTested" : 0, |
"dupsDropped" : 0, |
"seenInvalidated" : 0, |
"matchTested" : 0, |
"keysExamined" : 47634, |
"children" : [] |
}
|
]
|
}
|
]
|
}
|
}
|
Thanks for the help.
- is related to
-
SERVER-13701 Query using 2d index throws exception when using explain()
-
- Closed
-