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

Distinct command should use index when distinct field is array sub-field and query is used

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 6.0.15, 7.0.10
    • Component/s: Query Planning
    • None
    • Query Optimization
    • ALL
    • Hide 
      db.foo.drop()
db.foo.ensureIndex({'a':1, 'b.c':1})
db.foo.insertOne({'a':1, 'b': {c: ['A']}})
db.runCommand({explain: {distinct: 'foo', key: 'b.c', query: {'a':1}}})

      The explain shows that the database is using a FETCH/IXSCAN when it should be using PROJECTION_DEFAULT/DISTINCT_SCAN

      If the b.c field is a scalar value instead of an array it will use the index correctly.

      db.foo.drop()
db.foo.ensureIndex({'a':1, 'b.c':1})
db.foo.insertOne({'a':1, 'b': {c: 'A'}})
db.runCommand({explain: {distinct: 'foo', key: 'b.c', query: {'a':1}}})

       

      Show
      db.foo.drop() db.foo.ensureIndex({ 'a' :1, 'b.c' :1}) db.foo.insertOne({ 'a' :1, 'b' : {c: [ 'A' ]}}) db.runCommand({explain: {distinct: 'foo' , key: 'b.c' , query: { 'a' :1}}}) The explain shows that the database is using a FETCH/IXSCAN when it should be using PROJECTION_DEFAULT/DISTINCT_SCAN If the b.c field is a scalar value instead of an array it will use the index correctly. db.foo.drop() db.foo.ensureIndex({ 'a' :1, 'b.c' :1}) db.foo.insertOne({ 'a' :1, 'b' : {c: 'A' }}) db.runCommand({explain: {distinct: 'foo' , key: 'b.c' , query: { 'a' :1}}})  

      When running a distinct command together with a query, the index is not used efficiently if the distinct field is an array. This can cause serious performance issues on large datasets.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            jm088658@gmail.com John Mark
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: