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

Query Optimizer not using index correctly

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Duplicate
    • Icon: Major - P3 Major - P3
    • None
    • 3.2.10
    • Aggregation Framework
    • None
    • ALL

    Description

      Hi !

      We have a collection which is roughly based on a schema similar to :

      {
        _id : GUID
        StartTime: DateTime
        // Other Information
        Counters: [  
          {
               Name : string,
              // Others informations...
          }
         ]
      }
      

      The majority of our aggregate query starts with a $match phase which is always similar to:

      db.collections.aggregate([
      {
        $match : 
        {
            "Counters.Name" : 'Name',
            "StartTime": {$gte : ISODate(....), $lte: ISODate(....)}
        }
      }
      ])
      

      And I have the two following indexes on the collection :

      {'StartTime: 1}
      {'Counters.Name' : 1, 'StartTime' : 1}
      

      I've noticed two different behavior depending on the filters that are applied to the date.

      1) If I'm only using $gte: ISODate() the Counters.Name_1_StartTime_1 index gets used.
      2) If I'm using the $gte and $lte the StartTime_1 index get used.

      Logically, it should use the Counters.Name_1_StartTime_1 one as it would restrict much more the scope of documents.

      I've also tried with an $elemMatch and can't get the bounded query to use the one containing the Counters.Name and StartDate

      Attachments

        Activity

          People

            kelsey.schubert@mongodb.com Kelsey Schubert
            smarquis Stephane Marquis
            Votes:
            3 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: