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
-