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

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

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor - P4
    • Resolution: Duplicate
    • Affects Version/s: 4.0.11
    • Fix Version/s: None
    • Component/s: Indexing
    • Labels:
      None

      Description

      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

       

       

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: