[SERVER-38799] $expr does not use sparse or partial indexes Created: 29/Dec/18  Updated: 11/Jan/24

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Bruno Fernandes Bastos Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-42707 $eq should count as a subset of $exis... Closed
Related
Assigned Teams:
Query Optimization
Sprint: QO 2024-01-08
Participants:

 Description   

In the following example:

 



 Comments   
Comment by Asya Kamsky [ 28/Dec/23 ]

Looks like $expr won't use sparse indexes either (they are similar to partial indexes I guess).

With sparse index on fid

db.foreign.explain().find({$expr:{$eq:["$fid",100]}})
{
	"explainVersion" : "2",
	"queryPlanner" : {
		"namespace" : "test.foreign",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"$expr" : {
						"$eq" : [
							"$fid",
							{
								"$const" : 100
							}
						]
					}
				},
				{
					"fid" : {
						"$_internalExprEq" : 100
					}
				}
			]
		},
		"queryHash" : "E854ED70",
		"planCacheKey" : "E5C24B58",
		"maxIndexedOrSolutionsReached" : false,
		"maxIndexedAndSolutionsReached" : false,
		"maxScansToExplodeReached" : false,
		"winningPlan" : {
			"queryPlan" : {
				"stage" : "COLLSCAN",
				"planNodeId" : 1,
				"filter" : {
					"$and" : [
						{
							"$expr" : {
								"$eq" : [
									"$fid",
									{
										"$const" : 100
									}
								]
							}
						},
						{
							"fid" : {
								"$_internalExprEq" : 100
							}
						}
					]
				},
				"direction" : "forward"
			}, // etc.

Comment by Asya Kamsky [ 03/Jun/19 ]

Confirmed (with 4.1.13) that this has to do with $expr syntax specifically as using the matcher syntax I see:

db.getCollection('contacts').find({name:"A"}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.contacts",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"name" : {
				"$eq" : "A"
			}
		},
		"queryHash" : "01AEE5EC",
		"planCacheKey" : "4C5AEA2C",
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"name" : 1,
					"email" : 1
				},
				"indexName" : "name_1_email_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"name" : [ ],
					"email" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : true,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"name" : [
						"[\"A\", \"A\"]"
					],
					"email" : [
						"[MinKey, MaxKey]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "asyas-macbook-pro-3.local",
		"port" : 41130,
		"version" : "4.1.13",
		"gitVersion" : "441714bc4c70699950f3ac51a5cac41dcd413eaa"
	},
	"ok" : 1
}

Comment by Danny Hatcher (Inactive) [ 03/Jan/19 ]

Hello Bruno,

Thank you for your report. I have assigned this to our Query team to take a look.

Danny

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