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

Consider a collection scan even if indexed plans are available

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: 2.4.9, 2.6.0-rc0
    • Fix Version/s: Backlog
    • Component/s: Indexing
    • Labels:
      None

      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

              • Votes:
                0 Vote for this issue
                Watchers:
                11 Start watching this issue

                Dates

                • Created:
                  Updated: