Difference in v3.2.0 and 3.2..4 in using an index?

[Olivier Hautecoeur]

Hi,

The database contains documents like this one:

{

        "_id" : {

                "ipix" : 899773,

                "time" : ISODate("2016-02-08T00:07:14.667Z")

        },

        "loc" : {

                "type" : "Point",

                "coordinates" : [

                        -37.5215,

                        48.7531

                ]

        },

        "vd" : 6.9,

        "temp" : 233.4,

        "spd" : 54.9,

        "dir" : 265.2,

        "bias" : -6.2,

        "qix" : 85,

        "qi" : 84,

        "orb" : 17593,

        "pres" : 365.4,

        "ha_m" : 1

}

Three indexes are created

[

        {

                "v" : 1,

                "key" : {

                        "_id" : 1

                },

                "name" : "_id_",

                "ns" : "db.foo"

        },

        {

                "v" : 1,

                "key" : {

                        "_id.time" : 1,

                        "loc" : "2dsphere"

                },

                "name" : "_id.time_1_loc_2dsphere",

                "ns" : "db.foo",

                "2dsphereIndexVersion" : 3

        },

        {

                "v" : 1,

                "key" : {

                        "orb" : 1,

                        "loc.coordinates.1" : 1

                },

                "name" : "orb_1_loc.coordinates.1_1",

                "ns" : "db.foo"

        }

]

On v3.2.0, the query db.foo.find({"_id.time":{$gt:ISODate("20160320")}}).explain() use the second index (_id.time_1_loc_2dsphere) with the stage IXSCAN while on a mongod v3.2.4 instance, the same query on the same collection (replica or standalone) does not use the index (COLLSCAN). Since the database contains tens of millions, the use of idexes is mandatory.

Did I miss something ?

Does anyone understand the problem?

Many thanks

Olivier

[Wan Bachtiar]

Hi Olivier,

What you are observing is a bug described in JIRA ticket SERVER-22448, where the query planner does not filter 2dsphere index version 3 correctly. This bug was fixed in v3.2.3.

Due to the sparse property of 2dsphere index version 3, in order for the query planner to use a compound index that includes a 2dsphere you need to also specify the 2dsphere index field. For example :

db.foo.find({
    "_id.time":{$gt:ISODate("20160320")},
    "loc": { 
        "$near": {
                "$geometry":{
                        

"type":"Point", 
                        "coordinates":[-37.5215, 48.7531

]
                }, 
                "$maxDistance":1000
            }
        }
    }
}).explain()

Since the database contains tens of millions, the use of indexes is mandatory.

I would recommend to create a separate index for _id.time if you perform queries without the loc field often.

Regards,

Wan.

​