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

$eq should count as a subset of $exists for partialFilterExpression index selection

XMLWordPrintableJSON

    • Type: Icon: Improvement Improvement
    • Resolution: Duplicate
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 4.0.11
    • Component/s: Index Maintenance
    • None

      When selecting an index, and the index is sparse (or partialFilterExpression is used), the documentation describes that the query predicate must include the partialFilteredExpression. See https://docs.mongodb.com/manual/core/index-partial/#query-coverage

       

      However, if an index is defined as sparse like so:

      db.test.createIndex({sparseField: 1}, {
  background: true,
  partialFilterExpression: {sparseField: {$exists: true}}
});

       

      Then queries against test do not always select this index when using the $eq operator. Example: 

      use test;
db.test.drop();
for (var i = 1; i <= 10000; i++) {
  db.test.insert( { name: "name"+i } );
}
db.test.insert({name: 'test record', sparseField: 'sparseTest'});
db.test.createIndex({sparseField: 1}, {
  background: true,
  partialFilterExpression: {sparseField: {$exists: true}}
});

# uses index
db.test.find({
  sparseField: 'sparseTest'
}).explain();

# DOES NOT USE INDEX!!!
db.test.find({
  $expr: {$eq: ['$sparseField', 'sparseTest']}
}).explain();

# uses index
db.test.find({
  sparseField: {$exists: true},
  $expr: {$eq: ['$sparseField', 'sparseTest']}
}).explain();

       

      I would think that the $eq operator should be considered a subset of $exists for purposes of index selection, right?

       

      FYI, I originally filed my findings on this google user group:

      https://groups.google.com/forum/?nomobile=true#!topic/mongodb-user/KxvqiQX17UY

       

       

            Assignee:
            daniel.hatcher@mongodb.com Danny Hatcher (Inactive)
            Reporter:
            ben@ethika.com Ben Rotz
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: