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

MongoDB is not selecting fastest query plan.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Works as Designed
    • Affects Version/s: 3.3.2
    • Fix Version/s: None
    • Component/s: Performance, Querying
    • Labels:
    • Environment:
      OSX, 10GB RAM, 512GB SSD, 2.5GH dual core
    • Operating System:
      ALL
    • Steps To Reproduce:
      Hide

      1. Create two collections, fast and slow and fill data with help of following script

      var a = 0;
      while(a++ < 4096*2){
        var randomnumber=Math.ceil(Math.random()*1000000)
       
        // create string in length of 3
        var name = Math.random().toString(36).substring(2,5);
       
        db.fast.insert({name:name, email:name + ".email@example.com", age:randomnumber});
        db.slow.insert({name:name, email:name + ".email@example.com", age:randomnumber});
       
      }
      

      On collection fast, create two indexes.

         db.createIndex({age:1});
         db.createIndex({age:1, name:1, email:1})
      

      Now run following query on fast

      pageNumber=18;
      nPerPage=20; 
      db.fast.find({
        age:{$gt:200, $lt:777217}, 
        name:{$gt:"1234", $lt:"z"}, 
        email:{$gt:"bdnsa28831283d", $lt:"z"}
      }).skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).explain("allPlansExecution")
      
      

      and on collection slow

      pageNumber=18;
      nPerPage=20; 
      db.slow.find({
        age:{$gt:200, $lt:777217}, 
        name:{$gt:"1234", $lt:"z"}, 
        email:{$gt:"bdnsa28831283d", $lt:"z"}
      }).skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).explain("allPlansExecution")
      
      

      Please compare executionTimeMillis of both queries.

      I have also noticed that query plan of fast is ignoring fastest plan.

      I'm attaching full explain of both queries. Looking forward

      Show
      1. Create two collections, fast and slow and fill data with help of following script var a = 0; while(a++ < 4096*2){ var randomnumber=Math.ceil(Math.random()*1000000)   // create string in length of 3 var name = Math.random().toString(36).substring(2,5);   db.fast.insert({name:name, email:name + ".email@example.com", age:randomnumber}); db.slow.insert({name:name, email:name + ".email@example.com", age:randomnumber});   } On collection fast , create two indexes. db.createIndex({age:1}); db.createIndex({age:1, name:1, email:1}) Now run following query on fast pageNumber=18; nPerPage=20; db.fast.find({ age:{$gt:200, $lt:777217}, name:{$gt:"1234", $lt:"z"}, email:{$gt:"bdnsa28831283d", $lt:"z"} }).skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).explain("allPlansExecution") and on collection slow pageNumber=18; nPerPage=20; db.slow.find({ age:{$gt:200, $lt:777217}, name:{$gt:"1234", $lt:"z"}, email:{$gt:"bdnsa28831283d", $lt:"z"} }).skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).explain("allPlansExecution") Please compare executionTimeMillis of both queries. I have also noticed that query plan of fast is ignoring fastest plan. I'm attaching full explain of both queries. Looking forward

      Description

      Someone brought in my notice that MongoDB is not selecting fast query plan. During a test found that a fully covered index query is many fold slower than one with default index on _id field.

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: