|
Hi, i have just tested the 2.6 and the problem isn't resolved for my case. In this comment i use the query and index detailed in the description of the jira.
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" : []
|
}
|
]
|
}
|
]
|
}
|
}
|
Should i open a new ticket or re-open this ticket ?
Thanks for the help.
|