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

Distinct command does not use covering multi-key indexes.

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.4.2
    • Component/s: Querying
    • None
    • ALL

      > use banana
      switched to db banana
      > db.dropDatabase();
      { "dropped" : "banana", "ok" : 1 }
      > use banana
      switched to db banana
      > db.c.ensureIndex({'name' : 1} )
      > db.c.ensureIndex({'addresses.state' : 1} )
      > db.c.insert({name: 'abcd', addresses: [{state:'OR'}, {state:'WA'}] });
      > db.c.insert({name: 'qwer', addresses: [{state:'AZ'}, {state:'CA'}] });
      > db.runCommand({ distinct: 'c', key: 'name'} )
      {
              "values" : [
                      "abcd",
                      "qwer"
              ],
              "stats" : {
                      "n" : 2,
                      "nscanned" : 2,
                      "nscannedObjects" : 0,
                      "timems" : 0,
                      "cursor" : "BtreeCursor name_1"
              },
              "ok" : 1
      }
      

      As expected with a non-nested distinct query the nscannedObjects is 0.

      > db.runCommand({ distinct: 'c', key: 'addresses.state'} )
      {
              "values" : [
                      "OR",
                      "WA",
                      "AZ",
                      "CA"
              ],
              "stats" : {
                      "n" : 2,
                      "nscanned" : 2,
                      "nscannedObjects" : 2,
                      "timems" : 0,
                      "cursor" : "BasicCursor"
              },
              "ok" : 1
      }
      

      As you can see, the nscannedObjects is 2 for the second query, where it would be expected to be 1. Doing a find on this data uses the index as expected:

      > db.c.find({'addresses.state':'CA'}).explain()
      {
              "cursor" : "BtreeCursor addresses.state_1",
              "isMultiKey" : true,
              "n" : 1,
              "nscannedObjects" : 1,
              "nscanned" : 1,
              "nscannedObjectsAllPlans" : 1,
              "nscannedAllPlans" : 1,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 0,
              "indexBounds" : {
                      "addresses.state" : [
                              [
                                      "CA",
                                      "CA"
                              ]
                      ]
              },
              "server" : "dellm4500ws:27017"
      }
      

            Assignee:
            Unassigned Unassigned
            Reporter:
            wshaver Will Shaver
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: