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

MongoDB is not selecting fastest query plan.

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.3.2
    • Component/s: Performance, Querying
    • Environment:
      OSX, 10GB RAM, 512GB SSD, 2.5GH dual core
    • ALL
    • 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

      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.

        1. explain_for_fast_collection.txt
          18 kB
          Saleem
        2. explain_for_slow_collection.txt
          6 kB
          Saleem

            Assignee:
            Unassigned Unassigned
            Reporter:
            sixthsense Saleem
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: