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

$in with limit() scans more than necessary

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 2.6.0
    • Component/s: Querying
    • None
    • ALL
    • Hide 
      db.short.drop();
db.short.ensureIndex({a:1});
for(i=0; i < 100; i++) db.short.insert({a:i});

function check(exp, limit, _case) {
	//print(exp.nscanned);
	assert.eq(exp.n, limit, "n is " + exp.n + ", should be " + limit + " " + _case + " " + tojson(exp));
	assert.eq(exp.nscanned, limit, "nscanned is " + exp.nscanned + ", should be " + limit + " " + _case + " " + tojson(exp));
}

function check_limit(limit) {
	var query = {a: { $in: [ 4, 2, 6, 5, 3, 1, 10, 15, 22 ] } };

	check(db.short.find(query).limit(limit).explain(), limit, "(positive limit and no sort)");
	check(db.short.find(query).limit(-limit).explain(), limit, "(negative limit and no sort)");
	check(db.short.find(query).sort({a:1}).limit(limit).explain(), limit, "(positive limit and positive sort)");
	check(db.short.find(query).sort({a:1}).limit(-limit).explain(), limit, "(negative limit and positive sort)");
	check(db.short.find(query).sort({a:-1}).limit(limit).explain(), limit, "(positive limit and negative sort)");
	check(db.short.find(query).sort({a:-1}).limit(-limit).explain(), limit, "(negative limit and negative sort)");
}

check_limit(1);
//check_limit(2);
//check_limit(3);
//check_limit(4);
      Show
      db. short .drop(); db. short .ensureIndex({a:1}); for (i=0; i < 100; i++) db. short .insert({a:i}); function check(exp, limit, _case) { //print(exp.nscanned); assert.eq(exp.n, limit, "n is " + exp.n + ", should be " + limit + " " + _case + " " + tojson(exp)); assert.eq(exp.nscanned, limit, "nscanned is " + exp.nscanned + ", should be " + limit + " " + _case + " " + tojson(exp)); } function check_limit(limit) { var query = {a: { $ in : [ 4, 2, 6, 5, 3, 1, 10, 15, 22 ] } }; check(db. short .find(query).limit(limit).explain(), limit, "(positive limit and no sort)" ); check(db. short .find(query).limit(-limit).explain(), limit, "(negative limit and no sort)" ); check(db. short .find(query).sort({a:1}).limit(limit).explain(), limit, "(positive limit and positive sort)" ); check(db. short .find(query).sort({a:1}).limit(-limit).explain(), limit, "(negative limit and positive sort)" ); check(db. short .find(query).sort({a:-1}).limit(limit).explain(), limit, "(positive limit and negative sort)" ); check(db. short .find(query).sort({a:-1}).limit(-limit).explain(), limit, "(negative limit and negative sort)" ); } check_limit(1); //check_limit(2); //check_limit(3); //check_limit(4);
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      When using an $in clause which has more entries than specified by limit() (with an index), the server can stop scanning the index and return the results as soon as the desired number of results have been obtained. This is particularly useful when using $in with limit(1), ie. "just find me a document that matches one of these".

      The limit(1) case works (ie. nscanned == limit) in version 2.4, but not 2.6. For limits > 1, both versions 2.4 and 2.6 scan slightly more than necessary (nscanned > limit), but 2.4 is a little better than 2.6.

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            kevin.pulo@mongodb.com Kevin Pulo
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: