[SERVER-22442] Allow hashed indexes to support $ne queries Created: 02/Feb/16  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Nir Mardiks Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

When performing a $ne query such as:

db.getCollection('users').find({ 'name.middle': { '$ne': null } }).hint({'name.middle': 'hashed'}).explain()

I can see a full table scan is being performed before returning the result and the hash index is being ignored.
In our case, most of the records would have a null value and thus using the index to quickly reach out to the few relevant documents would have been much more efficient.

To workaround this, we had to resort to using a regular index on top of the more efficient/small hashed index.



 Comments   
Comment by Daniel Pasette (Inactive) [ 03/Feb/16 ]

nmardiks, It may be useful for your use case to use a partial index if you want to index only a subset of your documents. https://docs.mongodb.org/manual/core/index-partial/

Comment by Kelsey Schubert [ 02/Feb/16 ]

Hi nmardiks,

Thank you opening this ticket. Hashed indexes do not support range based queries, so I am marking it as a new feature to be scheduled during the next round of planning.

Kind regards,
Thomas

Generated at Thu Feb 08 04:00:25 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.