[SERVER-42707] $eq should count as a subset of $exists for partialFilterExpression index selection Created: 08/Aug/19  Updated: 15/Aug/19  Resolved: 15/Aug/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 4.0.11
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Ben Rotz Assignee: Danny Hatcher (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-38799 $expr does not use sparse or partial ... Backlog
Related
Participants:

 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

 

 



 Comments   
Comment by Danny Hatcher (Inactive) [ 15/Aug/19 ]

To clarify, the issue isn't actually $eq. For example, the following command will use a partial index.

db.test.find({   sparseField: {$eq:'sparseTest' }}).explain();
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.test",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"sparseField" : {
				"$eq" : "sparseTest"
			}
		},
		"queryHash" : "A6DCFD43",
		"planCacheKey" : "E33BD9A8",
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"sparseField" : 1
				},
				"indexName" : "sparseField_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"sparseField" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : true,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"sparseField" : [
						"[\"sparseTest\", \"sparseTest\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Hatcher.local",
		"port" : 27017,
		"version" : "4.2.0",
		"gitVersion" : "a4b751dcf51dd249c5865812b390cfd1c0129c30"
	},
	"ok" : 1
}

That is because it is functionally equivalent to:

> db.test.find({   sparseField: 'sparseTest' }).explain();
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.test",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"sparseField" : {
				"$eq" : "sparseTest"
			}
		},
		"queryHash" : "A6DCFD43",
		"planCacheKey" : "E33BD9A8",
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"sparseField" : 1
				},
				"indexName" : "sparseField_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"sparseField" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : true,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"sparseField" : [
						"[\"sparseTest\", \"sparseTest\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Hatcher.local",
		"port" : 27017,
		"version" : "4.2.0",
		"gitVersion" : "a4b751dcf51dd249c5865812b390cfd1c0129c30"
	},
	"ok" : 1
}

The issue is the existence of the $expr operator which triggers the use of expressions. That is the operator that has issues with partial indexes.

Comment by Ben Rotz [ 15/Aug/19 ]

Ah yes, I did not realize it was just a blanket `$eq does nto use partial indexes`. Thank you for the clarification, duplicate merge sounds reasonable.

Thanks.

Comment by Danny Hatcher (Inactive) [ 15/Aug/19 ]

I believe this is more about the $expr instead of the $eq. As mentioned in SERVER-38799, $expr does not utilize partial indexes. I will close this ticket as a duplicate of SERVER-38799 and I recommend you watch it for future updates.

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