Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-21963

Index range scan inaccurate when compound index has an array field

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • None
    • ALL

      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" : [ ]
       },
      

            Assignee:
            Unassigned Unassigned
            Reporter:
            dharshanr@scalegrid.net Dharshan Rangegowda
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: