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

{$exists: false} will not use index

    • Fully Compatible
    • OS X
    • Hide

      Here's an example collection's index (besides _id):
      {
      "v" : 1,
      "key" :

      { "c" : 1 }

      ,
      "ns" : "test.test",
      "name" : "c_1",
      "background" : true,
      "sparse" : true
      }

      There are 6 items:
      db.test.insert(

      {c: 5, b: 1}

      );
      db.test.insert(

      {c: 5}

      );
      db.test.insert(

      {c: 4}

      );
      db.test.insert(

      {c: 3}

      );
      db.test.insert(

      {c: 5}

      );
      db.test.insert(

      {c: 1}

      );

      db.test.find({c: 5, b: {$exists: false}}).explain()
      {
      "cursor" : "BasicCursor",
      "isMultiKey" : false,
      "n" : 2,
      "nscannedObjects" : 6,
      "nscanned" : 6,
      "nscannedObjectsAllPlans" : 6,
      "nscannedAllPlans" : 6,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 0,
      "indexBounds" : {

      },
      "server" : "test:27017"
      }

      Even if I add an index on

      {c: 1, b: 1}

      it won't use that index either.

      With hint (where did the "b": 1 go on the first item?):
      db.test.find({c: 5, b: {$exists: false}}).hint(

      {c: 1}

      )

      { "_id" : ObjectId("5216bcf5f2aa2ebe3c6ed298"), "c" : 5 } { "_id" : ObjectId("5216bcf5f2aa2ebe3c6ed295"), "c" : 5 }
      Show
      Here's an example collection's index (besides _id): { "v" : 1, "key" : { "c" : 1 } , "ns" : "test.test", "name" : "c_1", "background" : true, "sparse" : true } There are 6 items: db.test.insert( {c: 5, b: 1} ); db.test.insert( {c: 5} ); db.test.insert( {c: 4} ); db.test.insert( {c: 3} ); db.test.insert( {c: 5} ); db.test.insert( {c: 1} ); db.test.find({c: 5, b: {$exists: false}}).explain() { "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 2, "nscannedObjects" : 6, "nscanned" : 6, "nscannedObjectsAllPlans" : 6, "nscannedAllPlans" : 6, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { }, "server" : "test:27017" } Even if I add an index on {c: 1, b: 1} it won't use that index either. With hint (where did the "b": 1 go on the first item?): db.test.find({c: 5, b: {$exists: false}}).hint( {c: 1} ) { "_id" : ObjectId("5216bcf5f2aa2ebe3c6ed298"), "c" : 5 } { "_id" : ObjectId("5216bcf5f2aa2ebe3c6ed295"), "c" : 5 }

      If you're doing a query with exists, it will not use the index. SERVER-393 was supposed to fix this afaik, but it is still forcing queries to not use the index, even if there is a more suitable one (including the exists field). If I use $hint to force it to use an index, then it just ignores the $exists.

      Additionally, if I use $hint, then the documents returned do NOT contain that field even if they should've. It's like I added a projection with that field being excluded. This might be a different bug though.

      See steps to reproduce for a test case.

        1. existsindex.js
          0.6 kB
        2. server10608.js
          0.9 kB

            Assignee:
            benety.goh@mongodb.com Benety Goh
            Reporter:
            fastest963 James Hartig
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: