[SERVER-21963] Index range scan inaccurate when compound index has an array field Created: 19/Dec/15  Updated: 22/Dec/15  Resolved: 21/Dec/15

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

Type: Bug Priority: Major - P3
Reporter: Dharshan Rangegowda Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-6720 Range query with compound multikey in... Closed
Related
is related to SERVER-15086 Allow for efficient range queries ove... Closed
Operating System: ALL
Participants:

 Description   

Hi,

I have the following document structure

{
  ReceivedDate: <Date>
  toy:[
    {maker:{ _id: <objectid>,...} },
    maker:{ _id: <objectid>,...} }
  ]		      
}

I have added the compound index {toy.maker._id:1, ReceivedDate:1}.

I am running the

db.<coll>.count({toy.maker._id: 'blah', 
'ReceivedDate': {  $gte: ISODate('2015-11-16T00:00:00+0000'), $lte: ISODate('2015-11-16T23:59:59+0000') } }

when I look at the explain I see the following bounds for the date index scan - "(true, new Date(1447718399000)]". Due to this it is causing some objects to be retrieved into memory instead of just using the index. Is there a reason why the index ranges are not being used?

 "executionStats" : {
         "executionSuccess" : true,
         "nReturned" : 1351,
         "executionTimeMillis" : 313,
         "totalKeysExamined" : 10396,
         "totalDocsExamined" : 10396,
         "executionStages" : {
                 "stage" : "FETCH",
                 "filter" : {
                         "ReceivedDate" : {
                                 "$gte" : ISODate("2015-11-16T00:00:00Z")
                         }
                 },
                 "nReturned" : 1351,
                 "executionTimeMillisEstimate" : 300,
                 "works" : 10397,
                 "advanced" : 1351,
                 "needTime" : 9045,
                 "needFetch" : 0,
                 "saveState" : 82,
                 "restoreState" : 82,
                 "isEOF" : 1,
                 "invalidates" : 0,
                 "docsExamined" : 10396,
                 "alreadyHasObj" : 0,
                 "inputStage" : {
                         "stage" : "IXSCAN",
                         "nReturned" : 10396,
                         "executionTimeMillisEstimate" : 10,
                         "works" : 10397,
                         "advanced" : 10396,
                         "needTime" : 0,
                         "needFetch" : 0,
                         "saveState" : 82,
                         "restoreState" : 82,
                         "isEOF" : 1,
                         "invalidates" : 0,
                         "keyPattern" : {
                                 "toy.maker._id" : 1,
                                 "ReceivedDate" : 1
                         },
                         "indexName" : "toy.maker._id_1_ReceivedDate_1",
                         "isMultiKey" : true,
                         "direction" : "forward",
                         "indexBounds" : {
                                 "Route.Vendor._id" : [
                                         "[83.0, 83.0]"
                                 ],
                                 "ReceivedDate" : [
                                         "(true, new Date(1447718399000)]"
                                 ]
                         },
                         "keysExamined" : 10396,
                         "dupsTested" : 10396,
                         "dupsDropped" : 0,
                         "seenInvalidated" : 0,
                         "matchTested" : 0
                 }
         },
         "allPlansExecution" : [ ]
 },



 Comments   
Comment by David Storch [ 22/Dec/15 ]

dharshanr@scalegrid.net, also see SERVER-15086. This is an improvement request which would allow MongoDB to provide tight index bounds for queries like yours. There is some further context and discussion in that ticket which may be useful.

Comment by Kelsey Schubert [ 21/Dec/15 ]

Hi dharshanr@scalegrid.net,

Thank you for the well documented example.

This behavior is expected, and has been discussed in SERVER-6720: multikey indexes do not intersect index bounds unless $elemMatch is included. In particular, I would like to point you to Dave's comment, which explains this behavior in more detail and provides a possible workaround.

I am closing this ticket as a duplicate of SERVER-6720. For supplemental information, please see our documentation on Multikey Index Bounds.

Thanks again,
Thomas

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