[SERVER-31172] index not used when it has partialFilterExpression and $exists on a query subset Created: 19/Sep/17  Updated: 11/Mar/19  Resolved: 20/Sep/17

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 3.4.9
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Brad Vogel Assignee: Mark Agarunov
Resolution: Duplicate Votes: 0
Labels: MAREF
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-26580 allow using partial index on query wh... Backlog
Operating System: ALL
Steps To Reproduce:

See above

Participants:
Case:

 Description   

Consider the following:

db.test.insert({a: {b:2}})
db.test.ensureIndex({
    'a.b': 1
  }, {
    name: 'test_index',
    background: true,
    partialFilterExpression: {
      'a': { $exists: true }
    }
  })
 
db.test.find({'a.b': 2}).explain(true)

Actual:
The index is not used (the first execution stage of the explain(true) result is COLLSCAN

Expected:
The index is used and IXSCAN shows up in the explain(true) results. This seems logic since the query `'a.b': 1` implies `'a':

{ $exists: true }

`.



 Comments   
Comment by Mark Agarunov [ 20/Sep/17 ]

Hello brad@mixmax.com,

Thank you for the report. Currently when using a partialFilterExpression with a field that is not the index key, the query must include the index key field, as well as the field used in the filter expression. Additionally, to use the same index with both a and a.b, you would need to include the both in the index key.

As a workaround, you can use the following index:

db.test4.ensureIndex({
   a:1, 'a.b': 1
}, {
   name: 'test_index',
   background: true,
   partialFilterExpression: {
      'a': { $exists: true }
   }
});

and add a match for a to the example queries you provided. Now the index is used for both queries:

db.test4.find({'a':{'$elemMatch':{b:2}}, a: {$exists:true}}).explain(true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.test4",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"a" : {
				"$exists" : true
			}
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"a" : {
					"$exists" : true
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1,
					"a.b" : 1
				},
 
<snip>

db.test4.find({'a.b': 2, a: {$exists:true}}).explain(true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.test4",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"a.b" : {
						"$eq" : 2
					}
				},
				{
					"a" : {
						"$exists" : true
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"a" : {
					"$exists" : true
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"a" : 1,
					"a.b" : 1
				},
<snip>

Looking over the desired behavior you've described, this looks to be the same as the behavior detailed in SERVER-26580 so I've closed this ticket as a duplicate. Please follow SERVER-26580 for updates on this issue.

Thanks,
Mark

Comment by Brad Vogel [ 19/Sep/17 ]

Where this is frustrating is that we have two types of queries:

db.test.find({'a': {'$elemMatch': {b:2}})
db.test.find({'a.b': 2})

In our database, there are very few documents with key `a` in the `test` collection. So we'd like to create a partialFilterExpression that satisfies both queries.

Note that using `sparse: true` (`partialFilterExpression`'s predecessor) handled this correctly.

Generated at Thu Feb 08 04:26:13 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.