[SERVER-17454] Logical NOT predicates should be able to be assigned to indexes with special types Created: 04/Mar/15  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Geo, Querying, Text Search
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: J Rassi Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

It is currently the case that the query planner cannot assign logical NOT predicates to indexes with special types (e.g. 2d/2dsphere/text). As a result, the query planner will not consider these indexes when planning queries which specify a NOT predicate on the first field of the index key pattern, and will not assign bounds for NOT predicates when performing access planning for these indexes. Note also that {$exists: false} is considered a NOT predicate.

For example:

> db.foo.ensureIndex({a: 1, b: "2dsphere"})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.find({a: {$ne: 1}, b: {$nearSphere: {$geometry: {type: "Point", coordinates: [0, 0]}}}})
Error: error: {
	"$err" : "Unable to execute query: error processing query: ns=test.foo limit=0 skip=0\nTree: $and\n    $not\n        a == 1.0\n    GEONEAR  field=b maxdist=1.79769e+308 isNearSphere=0\nSort: {}\nProj: {}\n No query solutions",
	"code" : 17007
}

> db.foo.ensureIndex({b: "2dsphere", a: 1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.find({a: {$exists: false}, b: {$nearSphere: {$geometry: {type: "Point", coordinates: [0, 0]}}}}).explain('executionStats').executionStats.executionStages.inputStage.inputStage.inputStage.indexBounds
{
	"b" : [
		"[\"0f00\", \"0f01\")",
		"[\"0f01\", \"0f02\")",
		"[\"0f02\", \"0f03\")",
		"[\"0f03\", \"0f04\")",
        ...
        ...
        ...
		"[\"5f30\", \"5f31\")",
		"[\"5f31\", \"5f32\")",
		"[\"5f32\", \"5f33\")",
		"[\"5f33\", \"5f34\")"
	],
	"a" : [
		"[MinKey, MaxKey]"
	]
}

The query planner should be able to handle these queries.


Generated at Thu Feb 08 03:44:29 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.