[SERVER-83549] Mongodb V 7.02 Atlas not using indexes as expected Created: 22/Nov/23  Updated: 13/Dec/23

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

Type: Bug Priority: Major - P3
Reporter: Ahmed Naser Assignee: Backlog - Triage Team
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File compass-explain.json     File profiler-parsed-query.json     File users-indexes.json    
Assigned Teams:
Server Triage
Operating System: ALL
Participants:

 Description   

When i was checking profiler for slow queries i found a query that is marked as slow and it makes a memory sort and scans +58000 index keys

When i took the same query and i put it inside compass or from the shell it scans only 158 keys and no memory sort! This happens only when the query is coming from nodejs code using mongoose Version 8
i made a new M10 atlas cluster on v6.0.5 and tested it again from the nodejs app and it was used as expected this means the issue in v7 

here is the result for the another query

this is the output of the profiler

profiler-parsed-query.json

and here is the full explain for the same query from compass (match and sort)
compass-explain.json

and here is a list of all indexes on that collection
users-indexes.json

You will notice in profiler report and the explain of compass the same index is used in both of them!
even though there is a big difference in examined keys


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