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

Consider a collection scan even if indexed plans are available

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.4.9, 2.6.0-rc0
    • Component/s: Index Maintenance
    • Query Optimization

      Restore the database from the attached BSON data (I called it BookStore FWIW).

      Then this query without indexes takes about 300 ms and uses basic cusor as you would expect:

      > db.Book.find({ "Ratings" : { "$elemMatch" : { "Value" : { "$gte" : 0 } } } } )
      

      Running explain we see it is using a cursor and it takes 891 ms.

      {
              "cursor" : "BtreeCursor Ratings.Value_1",
              "isMultiKey" : true,
              "n" : 270171,
              "nscannedObjects" : 270171,
              "nscanned" : 443781,
              "nscannedObjectsAllPlans" : 270270,
              "nscannedAllPlans" : 443880,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 3467,
              "nChunkSkips" : 0,
              "millis" : 891,
              "indexBounds" : {
                      "Ratings.Value" : [
                              [
                                      0,
                                      Infinity
                              ]
                      ]
              },
              "server" : "WIN-7S2IMPQ2TOE:27017",
              "filterSet" : false
      }
      >
      

      Now, remove that index:

      > db.Book.dropIndex("Ratings.Value_1")
      { "nIndexesWas" : 7, "ok" : 1 }
      

      And you will see it runs in 300ms!

      {
              "cursor" : "BasicCursor",
              "isMultiKey" : false,
              "n" : 270171,
              "nscannedObjects" : 271380,
              "nscanned" : 271380,
              "nscannedObjectsAllPlans" : 271380,
              "nscannedAllPlans" : 271380,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 2120,
              "nChunkSkips" : 0,
              "millis" : 317,
              "server" : "WIN-7S2IMPQ2TOE:27017",
              "filterSet" : false
      }
      

      It seems like mongo should either:

      • Not use the index
      • Acheive at least as good of perf

      To make matters much worse, I was actually running just a count operation via the C# driver and go even worse perf. Seems like there should be almost no perf cost for a count operation on an index set.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            mkennedy66996693 Michael Kennedy
            Votes:
            0 Vote for this issue
            Watchers:
            15 Start watching this issue

              Created:
              Updated: