[SERVER-9257] 2dsphere compound index cannot be used non-geo search Created: 05/Apr/13  Updated: 18/Oct/17  Resolved: 04/Nov/13

Status: Closed
Project: Core Server
Component/s: Geo
Affects Version/s: 2.4.0
Fix Version/s: 2.5.4

Type: Bug Priority: Major - P3
Reporter: Ulrich VANDENHEKKE Assignee: hari.khalsa@10gen.com
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Related
is related to SERVER-31620 Compound index with datetime and geos... Closed
Operating System: ALL
Participants:

 Description   

Introduction

I have that contains 30,650,965 elements and each elements have an average size of 572 bytes.
The data made 16.3Gb and index 6.9Gb.

On this documents, i have a position in [ lon, lat ] format like this (I have remove field that isn't part of the test):

{
  "_id" : new BinData(3, "6kMGoQ6pfaMmT+4ozRTtpA=="),
  "creationDate" : new Date("12/3/2013 14:25:46"),
  "detection" : {
    "position" : [0.13837920380832269, 45.990779665547429]
  },
}

Until 2.2 i use 2 indexes like that :

{ "detection.position": "2d", "creationDate": 1 }
{ "creationDate": 1 }

Now i am in 2.4 and would like use "2dsphere" index because i can put the creationDate first and keep only one index. The data is one year of document and i request generally only one day on a zone.

Test with old index

With a "2d" indexes i have the following request time :

> db.ntr_tracks.find({creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") }}).sort({ creationDate: 1 }).limit(1000).explain()
{
        "cursor" : "BtreeCursor creationDate_1",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 1000,
        "nscanned" : 1000,
        "nscannedObjectsAllPlans" : 1000,
        "nscannedAllPlans" : 1000,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 7,
        "indexBounds" : {
                "creationDate" : [
                        [
                                ISODate("2012-08-01T00:00:00Z"),
                                ISODate("2012-08-02T00:00:00Z")
                        ]
                ]
        },
        "server" : "pc-uvh-2:27017"
}

> db.ntr_tracks.find({"detection.position": { $within: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()

First time:

{
        "cursor" : "GeoBrowse-box",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 1786,
        "nscanned" : 1786,
        "nscannedObjectsAllPlans" : 1786,
        "nscannedAllPlans" : 1786,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 231,
        "nChunkSkips" : 0,
        "millis" : 30551,
        "indexBounds" : {
                "detection.position" : [ ]
        },
        "lookedAt" : NumberLong(959532),
        "matchesPerfd" : NumberLong(628029),
        "objectsLoaded" : NumberLong(1786),
        "pointsLoaded" : NumberLong(0),
        "pointsSavedForYield" : NumberLong(0),
        "pointsChangedOnYield" : NumberLong(0),
        "pointsRemovedOnYield" : NumberLong(0),
        "server" : "pc-uvh-2:27017"
}

Other time (even when changing period):

{
        "cursor" : "GeoBrowse-box",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 1744,
        "nscanned" : 1744,
        "nscannedObjectsAllPlans" : 1744,
        "nscannedAllPlans" : 1744,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 378,
        "nChunkSkips" : 0,
        "millis" : 8657,
        "indexBounds" : {
                "detection.position" : [ ]
        },
        "lookedAt" : NumberLong(959532),
        "matchesPerfd" : NumberLong(628029),
        "objectsLoaded" : NumberLong(1744),
        "pointsLoaded" : NumberLong(0),
        "pointsSavedForYield" : NumberLong(0),
        "pointsChangedOnYield" : NumberLong(0),
        "pointsRemovedOnYield" : NumberLong(0),
        "server" : "pc-uvh-2:27017"
}

If i use $geoWithin with a $box i have the same result.

Create a new index that replace two old index.

Now i delete the two index and create one:

{ "creationDate": 1, "detection.position": "2dsphere" }

The first query on date only is very long and don't use the index...

> db.ntr_tracks.find({creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") }}).sort({ creationDate: 1 }).limit(1000).explain()
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 30925306,
        "nscanned" : 30925306,
        "nscannedObjectsAllPlans" : 30925306,
        "nscannedAllPlans" : 30925306,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 414,
        "nChunkSkips" : 0,
        "millis" : 451928,
        "indexBounds" : {
 
        },
        "server" : "pc-uvh-2:27017"
}

If i made a geo query like this, mongo don't use index neither on geo query or date:

> db.ntr_tracks.find({"detection.position": { $within: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()
> 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" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 30925306,
        "nscanned" : 30925306,
        "nscannedObjectsAllPlans" : 30925306,
        "nscannedAllPlans" : 30925306,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 520,
        "nChunkSkips" : 0,
        "millis" : 586488,
        "indexBounds" : {
 
        },
        "server" : "pc-uvh-2:27017"
}

Using the following query is better, but always more slower than "2d" index.

> 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("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()
{
        "cursor" : "S2Cursor",
        "isMultiKey" : true,
        "n" : 1000,
        "nscannedObjects" : 1785,
        "nscanned" : 1266126,
        "nscannedObjectsAllPlans" : 1785,
        "nscannedAllPlans" : 1266126,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 1,
        "nChunkSkips" : 0,
        "millis" : 73671,
        "indexBounds" : {
 
        },
        "nscanned" : 1266126,
        "matchTested" : NumberLong(1264341),
        "geoTested" : NumberLong(5664),
        "cellsInCover" : NumberLong(9),
        "server" : "pc-uvh-2:27017"
}

Creating a date index

Creating an index on creationDate only (and keeping the geo index) will give the follwing result:

> db.ntr_tracks.find({creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") }}).sort({ creationDate: 1 }).limit(1000).explain()
{
        "cursor" : "BtreeCursor creationDate_1",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 1000,
        "nscanned" : 1000,
        "nscannedObjectsAllPlans" : 1000,
        "nscannedAllPlans" : 1000,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 28,
        "indexBounds" : {
                "creationDate" : [
                        [
                                ISODate("2012-08-01T00:00:00Z"),
                                ISODate("2012-08-02T00:00:00Z")
                        ]
                ]
        },
        "server" : "pc-uvh-2:27017"
}

> db.ntr_tracks.find({"detection.position": { $within: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()
> 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_1",
        "isMultiKey" : false,
        "n" : 1000,
        "nscannedObjects" : 47625,
        "nscanned" : 47625,
        "nscannedObjectsAllPlans" : 47625,
        "nscannedAllPlans" : 47625,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1,
        "nChunkSkips" : 0,
        "millis" : 240,
        "indexBounds" : {
                "creationDate" : [
                        [
                                ISODate("2012-08-01T00:00:00Z"),
                                ISODate("2012-08-02T00:00:00Z")
                        ]
                ]
        },
        "server" : "pc-uvh-2:27017"
}

In this two case the query use the creationDate. Very quick for the selected period of time. Lesser when the period grow because without index. But if mongo use the coupound index correctly with
creationDate and position, he shouldn't be slower that creationDate only ....

If i force (with hint) the compound index, the query is slow.

> 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("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()
{
        "cursor" : "S2Cursor",
        "isMultiKey" : true,
        "n" : 1000,
        "nscannedObjects" : 1785,
        "nscanned" : 1266126,
        "nscannedObjectsAllPlans" : 1785,
        "nscannedAllPlans" : 1266126,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 1,
        "nChunkSkips" : 0,
        "millis" : 50013,
        "indexBounds" : {
 
        },
        "nscanned" : 1266126,
        "matchTested" : NumberLong(1264341),
        "geoTested" : NumberLong(5664),
        "cellsInCover" : NumberLong(9),
        "server" : "pc-uvh-2:27017"
}

This last query use the S2Cursor is just slow (comparing the 2d index).

Last test

For the last test i replace the geo index by :

{"detection.position": "2dsphere", creationDate:1}

The difference is only for this query:

> 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("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()
{
        "cursor" : "S2Cursor",
        "isMultiKey" : true,
        "n" : 1000,
        "nscannedObjects" : 1747,
        "nscanned" : 614506,
        "nscannedObjectsAllPlans" : 1747,
        "nscannedAllPlans" : 614506,
        "scanAndOrder" : true,
        "indexOnly" : false,
        "nYields" : 9,
        "nChunkSkips" : 0,
        "millis" : 12019,
        "indexBounds" : {
 
        },
        "nscanned" : 614506,
        "matchTested" : NumberLong(612759),
        "geoTested" : NumberLong(3220),
        "cellsInCover" : NumberLong(9),
        "server" : "pc-uvh-2:27017"
}

Question

Removing the sort doen't change the response time.
After some test for the same order of index, S2Cursor is a little slower than old 2d index. Is it normal ?

On 2dsphere index i can't made $box query as before. Is it normal ?

Using a compound index with the creationDate doesn't work as i expect

  • search on creation date only deosn't use the index.
  • search with a geo query is slower than a seach with an index on creation date.....

Is a way to optimize this ?

Thanks for reading, and for mongo.



 Comments   
Comment by Ulrich VANDENHEKKE [ 10/Apr/14 ]

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.

Comment by Ulrich VANDENHEKKE [ 29/Apr/13 ]

Hello,

Thanks,

1. Is the improvement will be for next 2.4 or for the future 2.6 ?

Else is it normal that search with a geo query on

{creationDate: 1, detection.position: "2dsphere"}

is slower than a search with an index on creation date only for the same query ..... It is related on the improvement too ?

2. and 2.5. Ok

3. Ok. Removing sort does not change much because, returning line aren't big.

Thanks for your response. I hope an improvement for the first point will come soon .

Comment by hari.khalsa@10gen.com [ 25/Apr/13 ]

Hello! This is a great ticket. Thanks for putting all the effort into formatting it well and including lots of detail.

There are a handful of issues happening here.

1. The query system doesn't know how to use an index like

{creationDate: 1, detection.position: "2dsphere"}

to answer queries that are only over creationDate. This is a known issue and we are working on improving our query system. Any time you see BasicCursor, it means that we're not using an index, and the entire table is being scanned.

2. 2dsphere and 2d are different indices. When you're asking for points within a box with the 2d index, the box is flat, and the test to see if the point is in the box is rather quick. When you're asking for a point inside of a $geometry of type polygon, the polygon is on the (not flat) earth, and the math for checking if a point is in a polygon is slower.

2.5. You can't use $box with a 2dsphere. $box means a flat box, and 2dsphere is spherical, so we will never use a 2dsphere index with $box.

3. Sorting: If you're indexing by date first, the data is sorted by date by definition. If you're indexing by date second and 2dsphere first, the data is not actually sorted according to the indexDate. This unfortunately is currently a fundamental limitation of our Btree indices.

Generated at Thu Feb 08 03:19:51 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.