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

Covered query with empty find() without specifying hint

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 3.2.12
    • Component/s: Querying
    • None
    • ALL
    • Hide
      db.test.ensureIndex({ x : 1, y : 1 });
      for(var i = 0; i < 100; i++) { db.test.insert({ a : i, x : i, y : i }); }
      db.test.find({}, { x : 1, y : 1, _id : 0 }).explain(true)
      db.test.find({}, { x : 1, y : 1, _id : 0 }).hint("x_1_y_1").explain(true)
      
      Show
      db.test.ensureIndex({ x : 1, y : 1 }); for(var i = 0; i < 100; i++) { db.test.insert({ a : i, x : i, y : i }); } db.test.find({}, { x : 1, y : 1, _id : 0 }).explain(true) db.test.find({}, { x : 1, y : 1, _id : 0 }).hint("x_1_y_1").explain(true)

      From my tests MongoDB does not cover queries well when the query is empty. A simple example of this is as follows:

      db.test.ensureIndex({ x : 1, y : 1 });
      for(var i = 0; i < 100; i++) { db.test.insert({ a : i, x : i, y : i }); }
      
      db.test.find({}, { x : 1, y : 1, _id : 0 }).explain(true)
      

      In my eyes the above code should be a covered query. According to my tests it does not use the index at all, the stages returned are "PROJECTION" -> "COLLSCAN". If I pass a hint, it does return a covered query.

      db.test.find({}, { x : 1, y : 1, _id : 0 }).hint("x_1_y_1").explain(true);
      

      That returns "PROJECTION" -> "IXSCAN" and the totalDocsExamined is 0.

      If I pass have a predicate for the query (

      { x : 10 }

      ) such that it selects that index, it returns covered. If I attempt to do a sort on the index, it still does not consistently find it. Some of my tests it does, some of them it does not.

      To me it feels possible to perform an optimization where if the query is empty, and the requested fields directly match to an index, it should be able to perform a covered query without the need to manually specify hint().

            Assignee:
            mark.agarunov Mark Agarunov
            Reporter:
            owenallenaz Owen Allen
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: