[SERVER-60010] Count queries matching all of null, missing, and [] should be fully covered by a multikey index Created: 16/Sep/21 Updated: 29/Oct/23 Resolved: 12/Nov/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | 5.2.0 |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Alya Berciu | Assignee: | Hana Pearlman |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||
| Backwards Compatibility: | Fully Compatible | ||||
| Sprint: | QO 2021-11-01, QO 2021-11-15 | ||||
| Participants: | |||||
| Description |
|
Based on discussion in SERVER-18861, it may be possible to avoid adding a fetch + filter stage for a count when the query predicate on an indexed field matches all of these values: null, missing, undefined, and [], since a multikey index can fully cover that case. A multikey index cannot differentiate between these values, so we would still need a FETCH stage if we are returning the value of the indexed field (but not if we're just counting how many documents actually match this predicate). In the case of a compound index, the query could still be covered as long as we are not returning the value indexed by the multikey index. Some examples of queries we may be able to optimize: 1. If we have a multikey index on {a: 1}, the following queries can be fully covered by the index:
2. If we have a compound index {a: 1, b: 1} such that the index is multikey along the “a” path, we should be able to cover the above queries and the following:
|
| Comments |
| Comment by Hana Pearlman [ 18/Nov/22 ] | ||||||||
|
601290552@qq.com This is expected behavior. You can see the same thing for $eq:
Notice that the first aggregate matches the document where "a" is missing, but the second aggregate does not. This is because the "$eq" operator under "$expr" has different behavior. It will not match documents where "a" is missing. | ||||||||
| Comment by jing xu [ 18/Nov/22 ] | ||||||||
|
hello: db.coll.aggregate([ POCDB> db.coll.count({a: {$in: [null, []]}}) ] , , { _id: ObjectId("6377178fc252fad22331c800") }] , { _id: ObjectId("6377178fc252fad22331c7ff"), a: null }] | ||||||||
| Comment by Githook User [ 12/Nov/21 ] | ||||||||
|
Author: {'name': 'Hana Pearlman', 'email': 'hana.pearlman@mongodb.com', 'username': 'HanaPearlman'}Message: |