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

Count query does not consider compound index including _id

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Query Optimization

      Given a compound index on all the fields involved in a query, I would expect us to be able to use the index for that query. But, it seems like this is not always true:

      > db.foo.drop() 
      > db.foo.createIndex({a: 1, _id: 1})
      
      // Generates a covered IXSCAN using the compound index.
      > db.foo.find({a: 5, _id: 5}, {_id: 1})
      
      // Generates an IXSCAN + FETCH plan using _id index. Compound index is not considered (does not appear in rejected plans)
      > db.foo.count({a: 5, _id: 5})

      It's strange to me that we can use the index for the find, but not for the count. The equivalent agg command ($match + $count) has the same issue. It seems to only happen with compound indexes including _id; the same issue doesn't show up with index {a: 1, b: 1} and query on "a" and "b".  Interestingly, in some quick tests on clustered collections, we seem to be able to use the compound index.

      There may be room for improvement here. We should see if count + agg can make use of this index.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            hana.pearlman@mongodb.com Hana Pearlman
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated: