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

Query planner does not consider $elemMatch criteria to select partial index

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.2.9, 3.3.15
    • Component/s: Querying
    • None
    • ALL

      It appears that when querying with $elemMatch a partial index that could satisfy the query is not being considered, resulting in a collection scan.

      If a hint is added to use the partial index the execution stats confirm that the index can be used efficiently.

      Also if the query is amended to include the $elemMatch predicate covered by the partial index the same efficient plan is selected.

      Consider the following test data and partial index:

      db.foo.insert({ state: 'active', properties : [{ substatus : 'scheduled', date : ISODate("2016-09-15T19:47:35.255Z") }] })
      db.foo.insert({ state: 'active', properties : [{ substatus : 'unknown', date : ISODate("2016-09-15T19:47:35.255Z") }] })
      db.foo.insert({ state: 'active', properties : [{ substatus : 'inprogress', date : ISODate("2016-09-15T19:47:35.255Z") }] })
      db.foo.insert({ state: 'completed', properties : [{ substatus : 'processed', date : ISODate("2016-09-15T19:47:35.255Z") }] })
      db.foo.insert({ state: 'completed', properties : [{ substatus : 'returned', date : ISODate("2016-09-15T19:47:35.255Z") }] })
      db.foo.insert({ state: 'completed', properties : [{ substatus : 'failed', date : ISODate("2016-09-15T19:47:35.255Z") }] })
       
      db.foo.createIndex({
      	state : 1,
      	'properties.substatus' : 1
      }, {
      	partialFilterExpression : {
      		state : 'active',
      		'properties.substatus' : 'scheduled'
      	}
      })
      

      The following query results in a plan with a collection scan, even though the query can be satisfied by the partial index:

      db.foo.find({
      	state : 'active',
      	properties : {
      		$elemMatch : {
      			substatus : 'scheduled',
      			date : { $lte : new ISODate() }
      		}
      	}
      })
      

      However if an index hint is added to use the partial index the correct plan is chosen:

      db.foo.find({
      	state : 'active',
      	properties : {
      		$elemMatch : {
      			substatus : 'scheduled',
      			date : {
      				$lte : new ISODate()
      			}
      		}
      	}
      }).hint({
      	state : 1,
      	'properties.substatus' : 1
      })
      
      "stage" : "IXSCAN",
      "totalKeysExamined" : 1
      "totalDocsExamined" : 1
      

      Also note that if the top-level of the query is modified to include the 'properties.substatus' predicate this same plan is chosen:

      db.foo.find({
       	state : 'active',
       	'properties.substatus' : 'scheduled',
       	properties : {
       		$elemMatch : {
       			substatus : 'scheduled',
       			date : {
       				$lte : new ISODate()
       			}
       		}
       	}
      })
      

      Should the winning plan be one that uses the partial index?

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            john.murphy@mongodb.com John Murphy
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: