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

Consider a collection scan even if indexed plans are available

    XMLWordPrintable

Details

    • Improvement
    • Status: Backlog
    • Major - P3
    • Resolution: Unresolved
    • 2.4.9, 2.6.0-rc0
    • None
    • Index Maintenance

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              backlog-query-optimization Backlog - Query Optimization
              mkennedy66996693 Michael Kennedy
              Votes:
              0 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated: