mongodb 3.2.7 sorting not using compound index

Question

I'm using mongodb 3.2.7, docs like:

{
    "finished" : true,
    "buildNo": 1,
    "tryTimes" : 1,
    "createdTime" : ISODate("2019-05-16T19:00:50.604+08:00"),
    "modifiedTime" : ISODate("2019-12-27T18:33:25.682+08:00"),
}

I created my index using:

db.getCollection('builds').createIndex({"createdTime": 1})
db.getCollection('builds').createIndex({"tryTimes": 1, "createdTime": -1})

and my query is :

db.getCollection('builds').find({
"createdTime": {$lte: new Date("2020-04-15T00:00:00.000Z"), $gte: new Date("2020-04-01T00:00:00.000Z")},
"buildNo": 1, 
"finished": {$ne: true}
})
.sort({"tryTimes": 1, "createdTime": -1})
.limit(200)

But this query doesn't use the index I created before. the explain() output:

{
"queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "builds",
    "indexFilterSet" : false,
    "parsedQuery" : {
        "$and" : [ 
            {
                "buildNo" : {
                    "$eq" : 1.0
                }
            }, 
            {
                "createdTime" : {
                    "$lte" : ISODate("2020-04-15T08:00:00.000+08:00")
                }
            }, 
            {
                "createdTime" : {
                    "$gte" : ISODate("2020-04-01T08:00:00.000+08:00")
                }
            }, 
            {
                "$not" : {
                    "finished" : {
                        "$eq" : true
                    }
                }
            }
        ]
    },
    "winningPlan" : {
        "stage" : "SORT",
        "sortPattern" : {
            "tryTimes" : 1.0,
            "createdTime" : -1.0
        },
        "limitAmount" : 200,
        "inputStage" : {
            "stage" : "SORT_KEY_GENERATOR",
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "buildNo" : {
                                "$eq" : 1.0
                            }
                        }, 
                        {
                            "$not" : {
                                "finished" : {
                                    "$eq" : true
                                }
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "createdTime" : 1.0
                    },
                    "indexName" : "createdTime_1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "createdTime" : [ 
                            "[new Date(1585699200000), new Date(1586908800000)]"
                        ]
                    }
                }
            }
        }
    },
    "rejectedPlans" : [ 
        {
            "stage" : "LIMIT",
            "limitAmount" : 200,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "buildNo" : {
                                "$eq" : 1.0
                            }
                        }, 
                        {
                            "createdTime" : {
                                "$lte" : ISODate("2020-04-15T08:00:00.000+08:00")
                            }
                        }, 
                        {
                            "createdTime" : {
                                "$gte" : ISODate("2020-04-01T08:00:00.000+08:00")
                            }
                        }, 
                        {
                            "$not" : {
                                "finished" : {
                                    "$eq" : true
                                }
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "tryTimes" : 1.0,
                        "createdTime" : -1.0
                    },
                    "indexName" : "tryTimes_1_createdTime_-1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "tryTimes" : [ 
                            "[MinKey, MaxKey]"
                        ],
                        "createdTime" : [ 
                            "[MaxKey, MinKey]"
                        ]
                    }
                }
            }
        }
    ]
},
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 200,
    "executionTimeMillis" : 352,
    "totalKeysExamined" : 48256,
    "totalDocsExamined" : 48256,
    "executionStages" : {
        "stage" : "SORT",
        "nReturned" : 200,
        "executionTimeMillisEstimate" : 200,
        "works" : 48459,
        "advanced" : 200,
        "needTime" : 48258,
        "needYield" : 0,
        "saveState" : 757,
        "restoreState" : 757,
        "isEOF" : 1,
        "invalidates" : 0,
        "sortPattern" : {
            "tryTimes" : 1.0,
            "createdTime" : -1.0
        },
        "memUsage" : 198706,
        "memLimit" : 33554432,
        "limitAmount" : 200,
        "inputStage" : {
            "stage" : "SORT_KEY_GENERATOR",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 180,
            "works" : 48258,
            "advanced" : 0,
            "needTime" : 24539,
            "needYield" : 0,
            "saveState" : 757,
            "restoreState" : 757,
            "isEOF" : 1,
            "invalidates" : 0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "buildNo" : {
                                "$eq" : 1.0
                            }
                        }, 
                        {
                            "$not" : {
                                "finished" : {
                                    "$eq" : true
                                }
                            }
                        }
                    ]
                },
                "nReturned" : 23718,
                "executionTimeMillisEstimate" : 160,
                "works" : 48257,
                "advanced" : 23718,
                "needTime" : 24538,
                "needYield" : 0,
                "saveState" : 757,
                "restoreState" : 757,
                "isEOF" : 1,
                "invalidates" : 0,
                "docsExamined" : 48256,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 48256,
                    "executionTimeMillisEstimate" : 20,
                    "works" : 48257,
                    "advanced" : 48256,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 757,
                    "restoreState" : 757,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "keyPattern" : {
                        "createdTime" : 1.0
                    },
                    "indexName" : "createdTime_1",
                    "isMultiKey" : false,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "createdTime" : [ 
                            "[new Date(1585699200000), new Date(1586908800000)]"
                        ]
                    },
                    "keysExamined" : 48256,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "seenInvalidated" : 0
                }
            }
        }
    }
},
"serverInfo" : {
    "host" : "...",
    "port" : 9999,
    "version" : "3.2.7",
    "gitVersion" : "4249c1d2b5999ebbf1fdf3bc0e0e3b3ff5c0aaf2"
},
"ok" : 1.0}

even if I try to simplify the query to

db.getCollection('builds')
.find({ "buildNo": 1,  "finished": true })
.sort({"tryTimes": 1, "createdTime": -1})
.limit(200)

, the index still doesn't work.

The wierd things is: I use the same index and query on another mongondb ver. 3.4.14.3. The index works...

Any ideas is welcomed and appreciated.

Thanks a lot!

Answer 1

If a query can be satisfied by multiple indexes (satisfied is used losely as Mongo actually chooses all possibly relevant indexes) defined in the collection, MongoDB will then test all the applicable indexes in parallel (meaning Mongo's performs a "race"). The first index that can returns 101 results will be selected by the query planner.

Meaning that for that certain query your using the "wrong" index wins.

What can you do?:

You can use $hint, hint basically forces Mongo to use a specific index, however Mongo this is not recommended because if changes occur Mongo will not adapt to those.

Like so:

db.getCollection('builds')
.find({ "buildNo": 1,  "finished": true })
.sort({"tryTimes": 1, "createdTime": -1})
.limit(200)
.hint({tryTimes: 1, createdTime: -1})