[SERVER-85856] Count query does not consider compound index including _id Created: 29/Jan/24  Updated: 30/Jan/24

Status: Needs Scheduling
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Hana Pearlman Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

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.


Generated at Thu Feb 08 06:58:47 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.