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

Mongo 2.4 version: Can't select the correct Index to Use !

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Won't Fix
    • Icon: Major - P3 Major - P3
    • None
    • 2.4.10
    • Querying
    • None
    • ALL

    Description

      Hello,

      Can't select the correct Index to Use !

      Actually on forcing the use of a compounf Index that I have created : « PTY_OK_DS_DSL_PF_PT » with hint :

      db.OperationalData.find(
      {"PeriodType":"hour","OperatorKey":"DE-TSO-0004",    
              "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"},
          "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"},    
          "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}
      ).hint('PTY_OK_DS_DSL_PF_PT').explain(true)

      Results:

          "cursor" : "BtreeCursor PTY_OK_DS_DSL_PF_PT multi",
          "isMultiKey" : false,
          "n" : 53879,
          "nscannedObjects" : 53879,
          "nscanned" : 62632,
          "nscannedObjectsAllPlans" : 53879,
          "nscannedAllPlans" : 62632,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nYields" : 0,
          "nChunkSkips" : 0,
          "millis" : 254,

      If we leave the choise to Mongo to select the optimal index (we remove the « hint ») then :

      db.OperationalData.find(
      {"PeriodType":"hour","OperatorKey":"DE-TSO-0004",    
              "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"},
          "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"},    
          "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}
      ).explain(true)
       
          "cursor" : "BtreeCursor PeriodTo_1",
          "isMultiKey" : false,
          "n" : 53879,
          "nscannedObjects" : 2792389,
          "nscanned" : 2792389,
          "nscannedObjectsAllPlans" : 2801318,
          "nscannedAllPlans" : 2803528,
          "scanAndOrder" : false,
          "indexOnly" : false,
         "nYields" : 10,
          "nChunkSkips" : 0,
          "millis" : 11815,   !!!!!!

      The difference is very Big !

      Does this issue is fixed in later version?
      It is clearly that Monogo have problems with calculating the correct index to use ...

      This can produce very critical performance problem.....

      Can you please check.
      This is urgent.

      Thanks to all the team.
      BR,

      Attachments

        Activity

          People

            ramon.fernandez@mongodb.com Ramon Fernandez Marina
            safwen Safwen MARZOUGUI
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: