Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-12925

Queries with projections limiting returned fields can result in full collection scans

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.0-rc1
    • Affects Version/s: 2.6.0-rc0
    • Component/s: None
    • None
    • ALL
    • Hide
      > db.version()
      2.6.0-rc0
      
      > db.foo.ensureIndex({ id1:1 })
      WriteResult({ "nInserted" : 1 })
      > db.foo.ensureIndex({ id2:1 })
      WriteResult({ "nInserted" : 1 })
      > for( var i=1; i<=500000; i++ ){db.foo.save({ id1:i, id2:i })}
      WriteResult({ "nInserted" : 1 })
      > db.foo.find({ id1:{ $lte:10 }, id2:1 }).explain()
      {
              "cursor" : "BtreeCursor id1_1",
              "isMultiKey" : false,
              "n" : 1,
              "nscannedObjects" : 10,
              "nscanned" : 10,
              "nscannedObjectsAllPlans" : 12,
              "nscannedAllPlans" : 15,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 1,
              "indexBounds" : {
                      "id1" : [
                              [
                                      -Infinity,
                                      10
                              ]
                      ]
              },
              "server" : "Jeffs-MacBook-Air.local:27017",
              "filterSet" : false
      }
      > db.foo.find({ id1:{ $lte:10 }, id2:1 }, { _id:0, id1:1, id2:1 }).explain()
      {
              "cursor" : "BasicCursor",
              "isMultiKey" : false,
              "n" : 1,
              "nscannedObjects" : 500000,
              "nscanned" : 500000,
              "nscannedObjectsAllPlans" : 500003,
              "nscannedAllPlans" : 500007,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 3906,
              "nChunkSkips" : 0,
              "millis" : 303,
              "server" : "Jeffs-MacBook-Air.local:27017",
              "filterSet" : false
      }
      
      Show
      > db.version() 2.6.0-rc0 > db.foo.ensureIndex({ id1:1 }) WriteResult({ "nInserted" : 1 }) > db.foo.ensureIndex({ id2:1 }) WriteResult({ "nInserted" : 1 }) > for ( var i=1; i<=500000; i++ ){db.foo.save({ id1:i, id2:i })} WriteResult({ "nInserted" : 1 }) > db.foo.find({ id1:{ $lte:10 }, id2:1 }).explain() { "cursor" : "BtreeCursor id1_1" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 10, "nscanned" : 10, "nscannedObjectsAllPlans" : 12, "nscannedAllPlans" : 15, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 1, "indexBounds" : { "id1" : [ [ -Infinity, 10 ] ] }, "server" : "Jeffs-MacBook-Air.local:27017" , "filterSet" : false } > db.foo.find({ id1:{ $lte:10 }, id2:1 }, { _id:0, id1:1, id2:1 }).explain() { "cursor" : "BasicCursor" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 500000, "nscanned" : 500000, "nscannedObjectsAllPlans" : 500003, "nscannedAllPlans" : 500007, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 3906, "nChunkSkips" : 0, "millis" : 303, "server" : "Jeffs-MacBook-Air.local:27017" , "filterSet" : false }

      Hi there,

      It seems that if you specify fields to return in a projection, the query can end up doing a full collection scan even if there are indexes that should support the query predicates.

      This doesn't appear to be an explain bug from what I can tell.

            Assignee:
            hari.khalsa@10gen.com hari.khalsa@10gen.com
            Reporter:
            jlee Jeff lee
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: