[SERVER-34926] allow $expr with comparison bounded on both sides with same type to use a non-multikey index Created: 10/May/18  Updated: 14/Sep/22  Resolved: 14/Sep/22

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

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: James Wahlin
Resolution: Duplicate Votes: 4
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-69680 allow $expr with comparison bounded b... Backlog
duplicates SERVER-39943 Create match expressions for aggregat... Closed
is duplicated by SERVER-40636 $expr inside $match does not use inde... Closed
Related
related to SERVER-39938 aggregation $match before $lookup opt... Closed
related to SERVER-39943 Create match expressions for aggregat... Closed
is related to SERVER-34687 Aggregation Index Performance Closed
Sprint: QO 2022-07-11, QO 2022-07-25, QO 2022-08-08, QO 2022-08-22, QO 2022-09-05, QO 2022-09-19
Participants:
Case:

 Description   

Similarly to SERVER-31760 it's possible to rewrite $expr with $gt/$lt expression to an indexable MatchExpression.

I believe the two clear cases of this are:

  • bounded on both ends comparison to same type, i.e. {$and:[{$gte:["$x",0]}, {$lt:["$x",100]}]}  can be rewritten to {x:{$gte:0, $lt:100}
  • comparison to type and'ed with $type comparison to same type, i.e. {$and:[{$lt:["$sys_created_on",ISODate("2012-02-28T03:04:49Z")]},{$eq:\\{$type:"$sys_created_on"},"date"}]}

I think the first case would be more common in "normal use" the second can be used to force the type bracketing semantics in aggregation comparisons.



 Comments   
Comment by James Wahlin [ 14/Sep/22 ]

The first/primary case described by this ticket, which asks to create tight index bounds for an explicit range was addressed by SERVER-39943 and available as of MongoDB 5.0. 

The second request for a one sided comparison plus $type operator is still outstanding. A new ticket (SERVER-69680) has been created to track this feature request. The current ticket will be closed as a duplicate of SERVER-39943.

Comment by David Storch [ 28/Jun/22 ]

asya.kamsky@mongodb.com yeah, it does look like the basic request tracked by this ticket of allowing inequalities expressed with $expr to use a non-multikey index has been completed.

That said, your initial description also discusses the case of an inequality expressed with $expr that is AND'ed with a $type expression. Here's an example:

MongoDB Enterprise > db.c.find({$expr: {$and: [{$lt: ["$x", 20]}, {$eq: [{$type: "$x"}, "double"]}]}}).explain()
{
	"explainVersion" : "1",
	"queryPlanner" : {
		"namespace" : "test.c",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"$expr" : {
						"$and" : [
							{
								"$lt" : [
									"$x",
									{
										"$const" : 20
									}
								]
							},
							{
								"$eq" : [
									{
										"$type" : [
											"$x"
										]
									},
									{
										"$const" : "double"
									}
								]
							}
						]
					}
				},
				{
					"x" : {
						"$_internalExprLt" : 20
					}
				}
			]
		},
		"queryHash" : "000A7A57",
		"planCacheKey" : "2ED7753C",
		"maxIndexedOrSolutionsReached" : false,
		"maxIndexedAndSolutionsReached" : false,
		"maxScansToExplodeReached" : false,
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"$expr" : {
					"$and" : [
						{
							"$lt" : [
								"$x",
								{
									"$const" : 20
								}
							]
						},
						{
							"$eq" : [
								{
									"$type" : [
										"$x"
									]
								},
								{
									"$const" : "double"
								}
							]
						}
					]
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"x" : 1
				},
				"indexName" : "x_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"x" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"x" : [
						"[MinKey, 20.0)"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
        ...
	"ok" : 1
}

Notably, the index bounds are [MinKey, 20) rather than [Infinity, 20). So I think there is still an opportunity for improvement here. You also brought up the point that we are unable to generate covered plans for predicates expressed this way, which is yet another potential improvement.

Since the main request here has been completed, however, I'm wondering if we should close this ticket and open new ones to track tightening the bounds when a $type expression is used and the ability to generate covered plans with $expr. I'll return this ticket to the triage queue in the meantime.

Comment by Asya Kamsky [ 20/May/22 ]

If the index is multikey I think it can still be used in cases where type is bounded on both sides like in this ticket's example, but it cannot be covered, obviously.

Comment by Asya Kamsky [ 20/May/22 ]

With SERVER-39943 fixed for 5.0 is there anything left to do in this ticket?

Just tested my example and it works:

        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "$expr" : {
                    "$and" : [
                        {
                            "$lt" : [
                                "$A",
                                {
                                    "$const" : 20
                                }
                            ]
                        },
                        {
                            "$gt" : [
                                "$A",
                                {
                                    "$const" : 0
                                }
                            ]
                        }
                    ]
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "A" : 1
                },
                "indexName" : "A_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "A" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "A" : [
                        "(0.0, 20.0)"
                    ]
                }
            }
        }, 

The only thing I can think of is this ticket describes scenario where we could use a COVERED (non-multikey) index plan, which we currently do not.

Comment by Asya Kamsky [ 18/Aug/20 ]

rangaraj.vignesh@gmail.com variables or not are expected to behave the same inside $expr

 

Comment by Vignesh Rangaraj [ 18/Aug/20 ]

Doesn't work on variables either.

 $and: [{ $gte: ["$DATE", { "$subtract": ['$$date', 30 * 1000]}] },{ $lte: ["$DATE", { "$add": ['$$date', 30 * 1000]}] }

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