CODEWITHSUNDEEP
mongodbindexingmongodb-query
Michael
Michael

Reputation: 25

MongoDB why is a compound index including 2dsphere not used

I've created a compound index:

db.lightningStrikes.createIndex({ datetime: -1, location: "2dsphere" })

But when I run the query below the MongoDB doesn't consider the index, making a COLLSCAN.

db.lightningStrikes.find({ datetime: { $gte: new Date('2017-10-15T00:00:00Z') } }).explain(true).executionStats

The full result is bellow:

{
    "executionSuccess" : true,
    "nReturned" : 2,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 0,
    "totalDocsExamined" : 4,
    "executionStages" : {
        "stage" : "COLLSCAN",
        "filter" : {
            "datetime" : {
                "$gte" : ISODate("2017-10-115T00:00:00Z")
            }
        },
        "nReturned" : 2,
        "executionTimeMillisEstimate" : 0,
        "works" : 6,
        "advanced" : 2,
        "needTime" : 3,
        "needYield" : 0,
        "saveState" : 0,
        "restoreState" : 0,
        "isEOF" : 1,
        "invalidates" : 0,
        "direction" : "forward",
        "docsExamined" : 4
    },
    "allPlansExecution" : [ ]
}

Ps. I just have 4 documents inserted.

Why is it happen?

db.lightningStrikes.find({ datetime: { $gte: new Date('2017-10-11T23:59:56Z'), $lte: new Date('2017-10-11T23:59:57Z') } }).explain(true)

Result from query above:
https://gist.github.com/anonymous/8dc084132016a1dfe0efb150201f04c7

db.lightningStrikes.find({ datetime: { $gte: new Date('2017-10-11T23:59:56Z'), $lte: new Date('2017-10-11T23:59:57Z') } }).hint("datetime_-1_location_2dsphere").explain(true)

Result from query above: https://gist.github.com/anonymous/2b76c5a7b4b348ea7206d8b544c7d455

Upvotes: 1

Views: 1104

Answers (1)

glytching
glytching

Reputation: 47875

To help understand what MongoDB is doing here you could:

  • Run explain with allPlansExecution mode and have a look at the rejected plans to see why MongoDB rejected your index
  • Run the find with .hint(_your_index_name_) and compare the explain output with the output you got for your original (non hinted) find.

Both of these are intended to get at the same thing, namely; comparative explain plans for (1) a find with COLLSCAN and (2) a find which uses your index. By comparing these explain plans you'll likely see some difference which explains MongoDB's decision not to use your index.

More details on analysing explain plans in the docs.

You could even update your OP with the comparative plans if you need help identifying why MongoDB chose the COLLSCAN.

Update 1: looking at the explain plans you provided ...

This plan uses your index but the explain plan output ...

        "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 4,
            "executionTimeMillisEstimate" : 0,
            "works" : 5,
            "advanced" : 4,
            ...,
            "keyPattern" : {
                "datetime" : -1,
                "location" : "2dsphere"
            },
            "indexName" : "datetime_-1_location_2dsphere",
            ...,
            "indexVersion" : 2,
            ...,
            "keysExamined" : 4,
            ...
        }

... shows that it used the index to examine 4 index keys and then return 4 documents to the FETCH stage. This tells us that the index did not provide any selectivity and the selectivity was provided by the FETCH stage which followed the IXSCAN. This is effectively what the COLLSCAN does but without the redundant IXSCAN. This might expain why MongoDB preferred a COLLSCAN but why did the IXSCAN do nothing? I suspect this is because the 2dsphere index cannot be used to answer queries which are missing a geo predicate over the 2dsphere field. Your query has a predicate over datetime but does not have a geo predicate over location. I think this means that MongoDB cannot use the 2dsphere index in order to answer the predicates over datetime. More information on the background to this in the docs. Briefly; the use of a sparse index means that there isn't necessarily an entry in the index for every document in your collection so if you search without the location attribute then MongoDB cannot rely on the index to satisfy the query.

You could test whether this assertion is correct by ...

  • updating your query to include a predicates on each of the datetime and location attributes

  • updating uur query to include a predicate on the location attibute only

... and for each of these run the query and then examine the explain plan output to see whether the IXSCAN stage actually selected anything. If the IXSCAN stage is selective then you should see keys examined > nReturned in the explain plan output (assuming that the criteria you pass in does actually select < 4 documents!).

Upvotes: 1

Related Questions