[SERVER-60417] $elemMatch doesn't fully use compound index Created: 04/Oct/21  Updated: 14/Oct/21  Resolved: 14/Oct/21

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

Type: Bug Priority: Major - P3
Reporter: Denys Isaiev Assignee: Edwin Zhou
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

I might miss something but when I create compound index for nested array of documents, when I filter by fields from those index, only one of them is filtered inside INDEX_SCAN stage, and another field is filtered in FETCH stage

Sample script:

db.test.drop()
for(var i = 0; i < 100; i++){    
   db.test.insert({units: [{jobs: [{jobType: i % 3, status: i % 10 }]}]})
}
db.test.createIndex({"units.jobs.status": 1, "units.jobs.jobType": 1})
db.test
   .find({ "units" : { "$elemMatch" : { "jobs" : { "$elemMatch" : { "jobType" : 0, "status" : 3 } } } }})
   .explain('executionStats')

Last query end up with following INDEX_SCAN:

"indexBounds" : { "indexBounds" :

{ "units.jobs.status" : [ "[3.0, 3.0]" ], "units.jobs.jobType" : [ "[MinKey, MaxKey]" ] }

,

and result set is filtered with FETCH phase.

 

Is this expected behavious and if so, what is possible workaround?

 

Thanks in advance

 



 Comments   
Comment by Edwin Zhou [ 14/Oct/21 ]

Hi denys.isaiev@synergysports.com,

Thank you for your report. The SERVER project is reserved for bugs that exist on the MongoDB server. For this issue we'd like to encourage you to start by asking our community for help by posting on the MongoDB Developer Community Forums.

If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project.

Best,
Edwin

Generated at Thu Feb 08 05:49:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.