title: MongoDB find() query scans documents twice (duplicate cursor used) when using limit() + sort()?
body: <p>I'm fairly new to MongoDB, though I haven't been able to find an explanation for what I'm seeing.</p>
  
  <p>I have a small dataset of about 200 documents, when I run the following query:
  <code>db.tweets.find({user:22438186})</code>
  I get <em>n</em> / <em>nscannedObjects</em> / <em>nscanned</em> / <em>nscannedObjectsAllPlans</em> / <em>nscannedAllPlans</em> all at <strong>9</strong>. The cursor is <em>BtreeCursor user_1</em>. All good.</p>
  
  <hr>
  
  <h2>Introducting Sort()</h2>
  
  <p>If I append a sort to the query:
  <code>db.tweets.find({user:22438186}).sort({created_at:1})</code>
  <em>nscannedObjectsAllPlans</em> / <em>nscannedAllPlans</em> have increased to <strong>30</strong>. I can see under the <em>allPlans</em> field:</p>
  
  <pre><code>[
  {
      "cursor" : "BtreeCursor user_1",
      "isMultiKey" : false,
      "n" : 9,
      "nscannedObjects" : 9,
      "nscanned" : 9,
      "scanAndOrder" : true,
      "indexOnly" : false,
      "nChunkSkips" : 0,
      "indexBounds" : {
          "user" : [ 
              [ 
                  22438186, 
                  22438186
              ]
          ]
      }
  },
  {
      "cursor" : "BtreeCursor created_at_1",
      "isMultiKey" : false,
      "n" : 2,
      "nscannedObjects" : 21,
      "nscanned" : 21,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nChunkSkips" : 0,
      "indexBounds" : {
          "created_at" : [ 
              [ 
                  {
                      "$minElement" : 1
                  }, 
                  {
                      "$maxElement" : 1
                  }
              ]
          ]
      }
  }
  ]
  </code></pre>
  
  <p><em>BtreeCursor created_at_1</em> scanned 21 documents and matched 2? I'm not sure what is going on here as I thought <code>sort()</code> was applied to the documents returned by <code>find()</code>, which appears to be 9 from the <em>user_1</em> index. In writing this up I'm gathering from the <em>allPlans</em> field that it's also using my <em>created_at_1</em> index for some reason.</p>
  
  <hr>
  
  <h2>Limit(>n) combined with Sort() == duplicate cursor &amp; document scans?</h2>
  
  <p>When I append <code>limit(10)</code> or higher, <em>n</em> remains at 9, <em>nscannedObjects</em> / <em>nscanned</em> are both at <strong>18</strong> and <em>nscannedObjectsAllPlans</em> / <em>nscannedAllPlans</em> now return <strong>60</strong>. <strong>Why have all but <em>n</em> doubled?</strong> The <em>cursor</em> is now <em>QueryOptimizerCursor</em>, There is a <em>clauses</em> field in my <code>explain(true)</code> results, both child objects are exactly the same, the same cursor was used twice causing the duplication? Is this behaviour normal?</p>
  
  <pre><code>{
  "cursor" : "BtreeCursor user_1",
  "isMultiKey" : false,
  "n" : 9,
  "nscannedObjects" : 9,
  "nscanned" : 9,
  "scanAndOrder" : true,
  "indexOnly" : false,
  "nChunkSkips" : 0,
  "indexBounds" : {
      "user" : [ 
          [ 
              22438186, 
              22438186
          ]
      ]
  }
  }
  </code></pre>
  
  <p>I've tried a few different limit values and noticed that using a limit of 9, <em>nscannedObjects</em> / <em>nscanned</em> both return back to values of 9 and <em>nscannedObjectsAllPlans</em> / <em>nscannedAllPlans</em> drop down to <strong>29</strong>, decrementing by 1 as I decrement the limit. </p>
  
  <p>Under clauses however the 2nd child object is not the same as limit queries of 10 and higher. The <em>cursor</em> field now displays <em>BtreeCursor</em> omitting <em>user_1</em> for some reason, all the <em>n</em> fields have a value of <strong>0</strong> instead of 9, besides that the rest of the object is the same. For all of these limit queries <em>allPlans</em> field lists the <em>clauses</em> field and another for <em>BtreeCursor created_at_1</em> (which is used as the cursor for a query with limit of 1).</p>
  
  <hr>
  
  <h1>Actual Question</h1>
  
  <p>So what exactly is causing my documents to be scanned twice when <code>limit()</code> and <code>sort()</code> are both used in a <code>find()</code>? The issue only seems to happen if the limit exceeds either <em>nscannedObjects</em> or <em>nscanned</em>. When querying with only <code>limit()</code> or <code>sort()</code> documents are not scanned twice.</p>
  
  <h2>Update</h2>
  
  <p>Sorry for the confusion, the first code block shows cursor data under the <strong>allPlans</strong> field. The actual cursor used was <code>*BtreeCursor user_1*</code>.</p>
  
  <p>The 2nd code block is from a query with <code>limit()</code> and <code>sort()</code>. I am providing cursor data listed under <strong>clauses</strong>, the <em>clauses</em> field lists the same cursor information twice (duplicate). The actual <em>cursor</em> field for that query was <code>*QueryOptimizerCursor*</code>. The duplicate cursors under <em>clauses</em> are <code>*BtreeCursor user_1*</code>.</p>
  
  <p>I've since added a compound index {user:1, created_at:1}, The results for <em>n</em> fields is 9, and <em>nAllPlans</em> 18. Regardless of <code>limit()</code> value or usage with <code>sort()</code>. For some reason under <em>allPlans</em> my original <em>user_id_1</em> index is still being run alongside the new compound index. If a limit is applied to the query instead of the index <em>user_id_1</em>/<em>BtreeCursor user_1</em> being used, <em>QueryOptimizerCursor</em> with the two cursors in <em>clauses</em> is being used. </p>
  
  <p>I've been looking into this further and it seems to be the Query Planner uses other indexes in parallel and selecting the optimal index result? I'm not sure if each time I perform this query this 'competition' occurs again or if it is cached.</p>
  
  <p><code>db.tweets.find({user:22438186}).sort({created_at:1}).limit(10)</code>
  Running the query without the compound index produces the following:</p>
  
  <pre><code>{
  "clauses" : [ 
      {
          "cursor" : "BtreeCursor user_1",
          "isMultiKey" : false,
          "n" : 9,
          "nscannedObjects" : 9,
          "nscanned" : 9,
          "scanAndOrder" : true,
          "indexOnly" : false,
          "nChunkSkips" : 0,
          "indexBounds" : {
              "user" : [ 
                  [ 
                      22438186, 
                      22438186
                  ]
              ]
          }
      }, 
      {
          "cursor" : "BtreeCursor user_1",
          "isMultiKey" : false,
          "n" : 9,
          "nscannedObjects" : 9,
          "nscanned" : 9,
          "scanAndOrder" : true,
          "indexOnly" : false,
          "nChunkSkips" : 0,
          "indexBounds" : {
              "user" : [ 
                  [ 
                      22438186, 
                      22438186
                  ]
              ]
          }
      }
  ],
  "cursor" : "QueryOptimizerCursor",
  "n" : 9,
  "nscannedObjects" : 18,
  "nscanned" : 18,
  "nscannedObjectsAllPlans" : 60,
  "nscannedAllPlans" : 60,
  "scanAndOrder" : false,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "millis" : 0,
  "allPlans" : [ 
      {
          "clauses" : [ 
              {
                  "cursor" : "BtreeCursor user_1",
                  "isMultiKey" : false,
                  "n" : 9,
                  "nscannedObjects" : 9,
                  "nscanned" : 9,
                  "scanAndOrder" : true,
                  "indexOnly" : false,
                  "nChunkSkips" : 0,
                  "indexBounds" : {
                      "user" : [ 
                          [ 
                              22438186, 
                              22438186
                          ]
                      ]
                  }
              }, 
              {
                  "cursor" : "BtreeCursor user_1",
                  "isMultiKey" : false,
                  "n" : 9,
                  "nscannedObjects" : 9,
                  "nscanned" : 9,
                  "scanAndOrder" : true,
                  "indexOnly" : false,
                  "nChunkSkips" : 0,
                  "indexBounds" : {
                      "user" : [ 
                          [ 
                              22438186, 
                              22438186
                          ]
                      ]
                  }
              }
          ],
          "cursor" : "QueryOptimizerCursor",
          "n" : 9,
          "nscannedObjects" : 18,
          "nscanned" : 18,
          "scanAndOrder" : false,
          "nChunkSkips" : 0
      }, 
      {
          "cursor" : "BtreeCursor created_at_1",
          "isMultiKey" : false,
          "n" : 3,
          "nscannedObjects" : 42,
          "nscanned" : 42,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nChunkSkips" : 0,
          "indexBounds" : {
              "created_at" : [ 
                  [ 
                      {
                          "$minElement" : 1
                      }, 
                      {
                          "$maxElement" : 1
                      }
                  ]
              ]
          }
      }
  ],
  "server" : "HOME-PC:27017",
  "filterSet" : false,
  "stats" : {
      "type" : "KEEP_MUTATIONS",
      "works" : 43,
      "yields" : 0,
      "unyields" : 0,
      "invalidates" : 0,
      "advanced" : 9,
      "needTime" : 32,
      "needFetch" : 0,
      "isEOF" : 1,
      "children" : [ 
          {
              "type" : "OR",
              "works" : 42,
              "yields" : 0,
              "unyields" : 0,
              "invalidates" : 0,
              "advanced" : 9,
              "needTime" : 32,
              "needFetch" : 0,
              "isEOF" : 1,
              "dupsTested" : 18,
              "dupsDropped" : 9,
              "locsForgotten" : 0,
              "matchTested_0" : 0,
              "matchTested_1" : 0,
              "children" : [ 
                  {
                      "type" : "SORT",
                      "works" : 21,
                      "yields" : 0,
                      "unyields" : 0,
                      "invalidates" : 0,
                      "advanced" : 9,
                      "needTime" : 10,
                      "needFetch" : 0,
                      "isEOF" : 1,
                      "forcedFetches" : 0,
                      "memUsage" : 6273,
                      "memLimit" : 33554432,
                      "children" : [ 
                          {
                              "type" : "FETCH",
                              "works" : 10,
                              "yields" : 0,
                              "unyields" : 0,
                              "invalidates" : 0,
                              "advanced" : 9,
                              "needTime" : 0,
                              "needFetch" : 0,
                              "isEOF" : 1,
                              "alreadyHasObj" : 0,
                              "forcedFetches" : 0,
                              "matchTested" : 0,
                              "children" : [ 
                                  {
                                      "type" : "IXSCAN",
                                      "works" : 10,
                                      "yields" : 0,
                                      "unyields" : 0,
                                      "invalidates" : 0,
                                      "advanced" : 9,
                                      "needTime" : 0,
                                      "needFetch" : 0,
                                      "isEOF" : 1,
                                      "keyPattern" : "{ user: 1 }",
                                      "isMultiKey" : 0,
                                      "boundsVerbose" : "field #0['user']: [22438186.0, 22438186.0]",
                                      "yieldMovedCursor" : 0,
                                      "dupsTested" : 0,
                                      "dupsDropped" : 0,
                                      "seenInvalidated" : 0,
                                      "matchTested" : 0,
                                      "keysExamined" : 9,
                                      "children" : []
                                  }
                              ]
                          }
                      ]
                  }, 
                  {
                      "type" : "SORT",
                      "works" : 21,
                      "yields" : 0,
                      "unyields" : 0,
                      "invalidates" : 0,
                      "advanced" : 9,
                      "needTime" : 10,
                      "needFetch" : 0,
                      "isEOF" : 1,
                      "forcedFetches" : 0,
                      "memUsage" : 6273,
                      "memLimit" : 33554432,
                      "children" : [ 
                          {
                              "type" : "FETCH",
                              "works" : 10,
                              "yields" : 0,
                              "unyields" : 0,
                              "invalidates" : 0,
                              "advanced" : 9,
                              "needTime" : 0,
                              "needFetch" : 0,
                              "isEOF" : 1,
                              "alreadyHasObj" : 0,
                              "forcedFetches" : 0,
                              "matchTested" : 0,
                              "children" : [ 
                                  {
                                      "type" : "IXSCAN",
                                      "works" : 10,
                                      "yields" : 0,
                                      "unyields" : 0,
                                      "invalidates" : 0,
                                      "advanced" : 9,
                                      "needTime" : 0,
                                      "needFetch" : 0,
                                      "isEOF" : 1,
                                      "keyPattern" : "{ user: 1 }",
                                      "isMultiKey" : 0,
                                      "boundsVerbose" : "field #0['user']: [22438186.0, 22438186.0]",
                                      "yieldMovedCursor" : 0,
                                      "dupsTested" : 0,
                                      "dupsDropped" : 0,
                                      "seenInvalidated" : 0,
                                      "matchTested" : 0,
                                      "keysExamined" : 9,
                                      "children" : []
                                  }
                              ]
                          }
                      ]
                  }
              ]
          }
      ]
  }
  }
  </code></pre>
  
  <p>With the compound index:</p>
  
  <pre><code>{
  "cursor" : "BtreeCursor user_1_created_at_1",
  "isMultiKey" : false,
  "n" : 9,
  "nscannedObjects" : 9,
  "nscanned" : 9,
  "nscannedObjectsAllPlans" : 18,
  "nscannedAllPlans" : 18,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "millis" : 0,
  "indexBounds" : {
      "user" : [ 
          [ 
              22438186, 
              22438186
          ]
      ],
      "created_at" : [ 
          [ 
              {
                  "$minElement" : 1
              }, 
              {
                  "$maxElement" : 1
              }
          ]
      ]
  },
  "allPlans" : [ 
      {
          "cursor" : "BtreeCursor user_1_created_at_1",
          "isMultiKey" : false,
          "n" : 9,
          "nscannedObjects" : 9,
          "nscanned" : 9,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nChunkSkips" : 0,
          "indexBounds" : {
              "user" : [ 
                  [ 
                      22438186, 
                      22438186
                  ]
              ],
              "created_at" : [ 
                  [ 
                      {
                          "$minElement" : 1
                      }, 
                      {
                          "$maxElement" : 1
                      }
                  ]
              ]
          }
      }, 
      {
          "clauses" : [ 
              {
                  "cursor" : "BtreeCursor user_1",
                  "isMultiKey" : false,
                  "n" : 0,
                  "nscannedObjects" : 9,
                  "nscanned" : 9,
                  "scanAndOrder" : true,
                  "indexOnly" : false,
                  "nChunkSkips" : 0,
                  "indexBounds" : {
                      "user" : [ 
                          [ 
                              22438186, 
                              22438186
                          ]
                      ]
                  }
              }, 
              {
                  "cursor" : "BtreeCursor ",
                  "isMultiKey" : false,
                  "n" : 0,
                  "nscannedObjects" : 0,
                  "nscanned" : 0,
                  "scanAndOrder" : true,
                  "indexOnly" : false,
                  "nChunkSkips" : 0,
                  "indexBounds" : {
                      "user" : [ 
                          [ 
                              22438186, 
                              22438186
                          ]
                      ]
                  }
              }
          ],
          "cursor" : "QueryOptimizerCursor",
          "n" : 0,
          "nscannedObjects" : 9,
          "nscanned" : 9,
          "scanAndOrder" : false,
          "nChunkSkips" : 0
      }
  ],
  "server" : "HOME-PC:27017",
  "filterSet" : false,
  "stats" : {
      "type" : "LIMIT",
      "works" : 11,
      "yields" : 0,
      "unyields" : 0,
      "invalidates" : 0,
      "advanced" : 9,
      "needTime" : 0,
      "needFetch" : 0,
      "isEOF" : 1,
      "children" : [ 
          {
              "type" : "FETCH",
              "works" : 11,
              "yields" : 0,
              "unyields" : 0,
              "invalidates" : 0,
              "advanced" : 9,
              "needTime" : 0,
              "needFetch" : 0,
              "isEOF" : 1,
              "alreadyHasObj" : 0,
              "forcedFetches" : 0,
              "matchTested" : 0,
              "children" : [ 
                  {
                      "type" : "IXSCAN",
                      "works" : 10,
                      "yields" : 0,
                      "unyields" : 0,
                      "invalidates" : 0,
                      "advanced" : 9,
                      "needTime" : 0,
                      "needFetch" : 0,
                      "isEOF" : 1,
                      "keyPattern" : "{ user: 1, created_at: 1 }",
                      "isMultiKey" : 0,
                      "boundsVerbose" : "field #0['user']: [22438186.0, 22438186.0], field #1['created_at']: [MinKey, MaxKey]",
                      "yieldMovedCursor" : 0,
                      "dupsTested" : 0,
                      "dupsDropped" : 0,
                      "seenInvalidated" : 0,
                      "matchTested" : 0,
                      "keysExamined" : 9,
                      "children" : []
                  }
              ]
          }
      ]
  }
  }
  </code></pre>
  
  <p>Hope that clears up the confusion.</p>