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

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Done
    • Priority: 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 }
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      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
            Reporter:
            Jeff lee
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: