title: MongoDB - Index not being used when sorting and limiting on ranged query
body: <p>I'm trying to get a sorted list of items using a ranged query on a collection containing bulletin-board data. The data structure of a "thread" document is:</p>
  
  <pre><code>{
      "_id" : ObjectId("5a779b47f4fa72412126526a"),
      "title" : "necessitatibus tincidunt libris assueverit",
      "content" : "Corrumpitvenenatis cubilia adipiscing sollicitudin",
      "flagged" : false,
      "locked" : false,
      "sticky" : false,
      "lastPostAt" : ISODate("2018-02-05T06:35:24.656Z"),
      "postCount" : 42,
      "user" : ObjectId("5a779b46f4fa72412126525a"),
      "category" : ObjectId("5a779b31f4fa724121265164"),
      "createdAt" : ISODate("2018-02-04T23:46:15.852Z"),
      "updatedAt" : ISODate("2018-02-05T06:35:24.656Z")
  }
  </code></pre>
  
  <p>The query is:</p>
  
  <pre><code>db.threads.find({
      category: ObjectId('5a779b31f4fa724121265142'), 
      _id : { $gt: ObjectId('5a779b5cf4fa724121269be8') }
  }).sort({ sticky: -1, lastPostAt: -1, _id: 1 }).limit(25)
  </code></pre>
  
  <p>I set up the following indexes to support it:</p>
  
  <pre><code>{ category: 1, _id: 1 }
  { category: 1, _id: 1, sticky: 1, lastPostAt: 1 }
  { sticky: 1, lastPostAt: 1, _id: 1 }
  </code></pre>
  
  <p>In spite of this, it's still scanning hundreds of documents/keys according to execution stats:</p>
  
  <pre><code>{
      "executionStats" : {
      "executionSuccess" : true,
      "nReturned" : 772,
      "executionTimeMillis" : 17,
      "totalKeysExamined" : 772,
      "totalDocsExamined" : 772,
      "executionStages" : {
          "stage" : "SORT",
          "nReturned" : 772,
          "executionTimeMillisEstimate" : 0,
          "works" : 1547,
          "advanced" : 772,
          "needTime" : 774,
          "needYield" : 0,
          "saveState" : 33,
          "restoreState" : 33,
          "isEOF" : 1,
          "invalidates" : 0,
          "sortPattern" : {
              "sticky" : -1,
              "lastPostAt" : -1,
              "_id" : 1
          },
          "memUsage" : 1482601,
          "memLimit" : 33554432,
          "inputStage" : {
              "stage" : "SORT_KEY_GENERATOR",
              "nReturned" : 772,
              "executionTimeMillisEstimate" : 0,
              "works" : 774,
              "advanced" : 772,
              "needTime" : 1,
              "needYield" : 0,
              "saveState" : 33,
              "restoreState" : 33,
              "isEOF" : 1,
              "invalidates" : 0,
              "inputStage" : {
                  "stage" : "FETCH",
                  "nReturned" : 772,
                  "executionTimeMillisEstimate" : 0,
                  "works" : 773,
                  "advanced" : 772,
                  "needTime" : 0,
                  "needYield" : 0,
                  "saveState" : 33,
                  "restoreState" : 33,
                  "isEOF" : 1,
                  "invalidates" : 0,
                  "docsExamined" : 772,
                  "alreadyHasObj" : 0,
                  "inputStage" : {
                      "stage" : "IXSCAN",
                      "nReturned" : 772,
                      "executionTimeMillisEstimate" : 0,
                      "works" : 773,
                      "advanced" : 772,
                      "needTime" : 0,
                      "needYield" : 0,
                      "saveState" : 33,
                      "restoreState" : 33,
                      "isEOF" : 1,
                      "invalidates" : 0,
                      "keyPattern" : {
                          "category" : 1,
                          "_id" : 1,
                          "sticky" : 1,
                          "lastPostAt" : 1
                      },
                      "indexName" : "category_1__id_1_sticky_1_lastPostAt_1",
                      "isMultiKey" : false,
                      "multiKeyPaths" : {
                          "category" : [ ],
                          "_id" : [ ],
                          "sticky" : [ ],
                          "lastPostAt" : [ ]
                      },
                      "isUnique" : false,
                      "isSparse" : false,
                      "isPartial" : false,
                      "indexVersion" : 2,
                      "direction" : "forward",
                      "indexBounds" : {
                          "category" : [
                              "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                          ],
                          "_id" : [
                              "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                          ],
                          "sticky" : [
                              "[MinKey, MaxKey]"
                          ],
                          "lastPostAt" : [
                              "[MinKey, MaxKey]"
                          ]
                      },
                      "keysExamined" : 772,
                      "seeks" : 1,
                      "dupsTested" : 0,
                      "dupsDropped" : 0,
                      "seenInvalidated" : 0
                  }
              }
          }
      }
  }
  </code></pre>
  
  <p>When I take out the sorting stage, it correctly scans only 25 documents. And the keys examined (772) remains the same no matter which fields I place in the sort function.</p>
  
  <p>Here is the full <code>explain()</code> for the sorted query:</p>
  
  <pre><code>{
      "queryPlanner" : {
          "plannerVersion" : 1,
          "namespace" : "database.threads",
          "indexFilterSet" : false,
          "parsedQuery" : {
              "$and" : [
                  {
                      "category" : {
                          "$eq" : ObjectId("5a779b31f4fa724121265142")
                      }
                  },
                  {
                      "_id" : {
                          "$gt" : ObjectId("5a779b5cf4fa724121269be8")
                      }
                  }
              ]
          },
          "winningPlan" : {
              "stage" : "SORT",
              "sortPattern" : {
                  "sticky" : -1,
                  "lastPostAt" : -1,
                  "_id" : 1
              },
              "limitAmount" : 25,
              "inputStage" : {
                  "stage" : "SORT_KEY_GENERATOR",
                  "inputStage" : {
                      "stage" : "FETCH",
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "keyPattern" : {
                              "category" : 1,
                              "_id" : 1,
                              "sticky" : 1,
                              "lastPostAt" : 1
                          },
                          "indexName" : "category_1__id_1_sticky_1_lastPostAt_1",
                          "isMultiKey" : false,
                          "multiKeyPaths" : {
                              "category" : [ ],
                              "_id" : [ ],
                              "sticky" : [ ],
                              "lastPostAt" : [ ]
                          },
                          "isUnique" : false,
                          "isSparse" : false,
                          "isPartial" : false,
                          "indexVersion" : 2,
                          "direction" : "forward",
                          "indexBounds" : {
                              "category" : [
                                  "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                              ],
                              "_id" : [
                                  "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                              ],
                              "sticky" : [
                                  "[MinKey, MaxKey]"
                              ],
                              "lastPostAt" : [
                                  "[MinKey, MaxKey]"
                              ]
                          }
                      }
                  }
              }
          },
          "rejectedPlans" : [
              {
                  "stage" : "SORT",
                  "sortPattern" : {
                      "sticky" : -1,
                      "lastPostAt" : -1,
                      "_id" : 1
                  },
                  "limitAmount" : 25,
                  "inputStage" : {
                      "stage" : "SORT_KEY_GENERATOR",
                      "inputStage" : {
                          "stage" : "FETCH",
                          "filter" : {
                              "_id" : {
                                  "$gt" : ObjectId("5a779b5cf4fa724121269be8")
                              }
                          },
                          "inputStage" : {
                              "stage" : "IXSCAN",
                              "keyPattern" : {
                                  "category" : 1
                              },
                              "indexName" : "category_1",
                              "isMultiKey" : false,
                              "multiKeyPaths" : {
                                  "category" : [ ]
                              },
                              "isUnique" : false,
                              "isSparse" : false,
                              "isPartial" : false,
                              "indexVersion" : 2,
                              "direction" : "forward",
                              "indexBounds" : {
                                  "category" : [
                                      "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                                  ]
                              }
                          }
                      }
                  }
              },
              {
                  "stage" : "SORT",
                  "sortPattern" : {
                      "sticky" : -1,
                      "lastPostAt" : -1,
                      "_id" : 1
                  },
                  "limitAmount" : 25,
                  "inputStage" : {
                      "stage" : "SORT_KEY_GENERATOR",
                      "inputStage" : {
                          "stage" : "FETCH",
                          "inputStage" : {
                              "stage" : "IXSCAN",
                              "keyPattern" : {
                                  "category" : 1,
                                  "_id" : 1
                              },
                              "indexName" : "category_1__id_1",
                              "isMultiKey" : false,
                              "multiKeyPaths" : {
                                  "category" : [ ],
                                  "_id" : [ ]
                              },
                              "isUnique" : false,
                              "isSparse" : false,
                              "isPartial" : false,
                              "indexVersion" : 2,
                              "direction" : "forward",
                              "indexBounds" : {
                                  "category" : [
                                      "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                                  ],
                                  "_id" : [
                                      "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                                  ]
                              }
                          }
                      }
                  }
              },
              {
                  "stage" : "SORT",
                  "sortPattern" : {
                      "sticky" : -1,
                      "lastPostAt" : -1,
                      "_id" : 1
                  },
                  "limitAmount" : 25,
                  "inputStage" : {
                      "stage" : "SORT_KEY_GENERATOR",
                      "inputStage" : {
                          "stage" : "FETCH",
                          "filter" : {
                              "category" : {
                                  "$eq" : ObjectId("5a779b31f4fa724121265142")
                              }
                          },
                          "inputStage" : {
                              "stage" : "IXSCAN",
                              "keyPattern" : {
                                  "_id" : 1
                              },
                              "indexName" : "_id_",
                              "isMultiKey" : false,
                              "multiKeyPaths" : {
                                  "_id" : [ ]
                              },
                              "isUnique" : true,
                              "isSparse" : false,
                              "isPartial" : false,
                              "indexVersion" : 2,
                              "direction" : "forward",
                              "indexBounds" : {
                                  "_id" : [
                                      "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                                  ]
                              }
                          }
                      }
                  }
              }
          ]
      },
      "serverInfo" : {
          "host" : "CRF-MBP.local",
          "port" : 27017,
          "version" : "3.6.2",
          "gitVersion" : "489d177dbd0f0420a8ca04d39fd78d0a2c539420"
      },
      "ok" : 1
  }
  </code></pre>
  
  <p>And here is the full <code>explain()</code> for the non-sorted query:</p>
  
  <pre><code>{
      "queryPlanner" : {
          "plannerVersion" : 1,
          "namespace" : "database.threads",
          "indexFilterSet" : false,
          "parsedQuery" : {
              "$and" : [
                  {
                      "category" : {
                          "$eq" : ObjectId("5a779b31f4fa724121265142")
                      }
                  },
                  {
                      "_id" : {
                          "$gt" : ObjectId("5a779b5cf4fa724121269be8")
                      }
                  }
              ]
          },
          "winningPlan" : {
              "stage" : "LIMIT",
              "limitAmount" : 25,
              "inputStage" : {
                  "stage" : "FETCH",
                  "inputStage" : {
                      "stage" : "IXSCAN",
                      "keyPattern" : {
                          "category" : 1,
                          "_id" : 1,
                          "sticky" : 1,
                          "lastPostAt" : 1
                      },
                      "indexName" : "category_1__id_1_sticky_1_lastPostAt_1",
                      "isMultiKey" : false,
                      "multiKeyPaths" : {
                          "category" : [ ],
                          "_id" : [ ],
                          "sticky" : [ ],
                          "lastPostAt" : [ ]
                      },
                      "isUnique" : false,
                      "isSparse" : false,
                      "isPartial" : false,
                      "indexVersion" : 2,
                      "direction" : "forward",
                      "indexBounds" : {
                          "category" : [
                              "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                          ],
                          "_id" : [
                              "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                          ],
                          "sticky" : [
                              "[MinKey, MaxKey]"
                          ],
                          "lastPostAt" : [
                              "[MinKey, MaxKey]"
                          ]
                      }
                  }
              }
          },
          "rejectedPlans" : [
              {
                  "stage" : "LIMIT",
                  "limitAmount" : 25,
                  "inputStage" : {
                      "stage" : "FETCH",
                      "filter" : {
                          "_id" : {
                              "$gt" : ObjectId("5a779b5cf4fa724121269be8")
                          }
                      },
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "keyPattern" : {
                              "category" : 1
                          },
                          "indexName" : "category_1",
                          "isMultiKey" : false,
                          "multiKeyPaths" : {
                              "category" : [ ]
                          },
                          "isUnique" : false,
                          "isSparse" : false,
                          "isPartial" : false,
                          "indexVersion" : 2,
                          "direction" : "forward",
                          "indexBounds" : {
                              "category" : [
                                  "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                              ]
                          }
                      }
                  }
              },
              {
                  "stage" : "LIMIT",
                  "limitAmount" : 25,
                  "inputStage" : {
                      "stage" : "FETCH",
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "keyPattern" : {
                              "category" : 1,
                              "_id" : 1
                          },
                          "indexName" : "category_1__id_1",
                          "isMultiKey" : false,
                          "multiKeyPaths" : {
                              "category" : [ ],
                              "_id" : [ ]
                          },
                          "isUnique" : false,
                          "isSparse" : false,
                          "isPartial" : false,
                          "indexVersion" : 2,
                          "direction" : "forward",
                          "indexBounds" : {
                              "category" : [
                                  "[ObjectId('5a779b31f4fa724121265142'), ObjectId('5a779b31f4fa724121265142')]"
                              ],
                              "_id" : [
                                  "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                              ]
                          }
                      }
                  }
              },
              {
                  "stage" : "LIMIT",
                  "limitAmount" : 25,
                  "inputStage" : {
                      "stage" : "FETCH",
                      "filter" : {
                          "category" : {
                              "$eq" : ObjectId("5a779b31f4fa724121265142")
                          }
                      },
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "keyPattern" : {
                              "_id" : 1
                          },
                          "indexName" : "_id_",
                          "isMultiKey" : false,
                          "multiKeyPaths" : {
                              "_id" : [ ]
                          },
                          "isUnique" : true,
                          "isSparse" : false,
                          "isPartial" : false,
                          "indexVersion" : 2,
                          "direction" : "forward",
                          "indexBounds" : {
                              "_id" : [
                                  "(ObjectId('5a779b5cf4fa724121269be8'), ObjectId('ffffffffffffffffffffffff')]"
                              ]
                          }
                      }
                  }
              }
          ]
      },
      "serverInfo" : {
          "host" : "CRF-MBP.local",
          "port" : 27017,
          "version" : "3.6.2",
          "gitVersion" : "489d177dbd0f0420a8ca04d39fd78d0a2c539420"
      },
      "ok" : 1
  }
  </code></pre>
  
  <p>Does anyone have any idea why this might not fully use an index?</p>