-
Type: Improvement
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
Fully Compatible
-
QO 2024-02-05, QO 2024-02-19, QO 2024-03-04
The logic to swap a given aggregation stage with a subsequent $match has a very conservative check to consider "array-matching" expressions as basically fully dependent on the input document. This has the effect of preventing any sort of swapping/pushdown of such stages.
As an example, consider the following query with a sort squeezed between two $match stages:
[{$match: {a: '07'}}, {$sort: {a: 1}}, {"$match":{"arrayField":{"$elemMatch":{"label":{"$eq":"abc"}}}}}]
And assuming we have an index on {a: 1, arrayField.label: 1}. With the current planner, the $elemMatch ends up as a residual predicate after an IXSCAN:
{ "queryPlanner" : { "namespace" : "test.test", "indexFilterSet" : false, "parsedQuery" : { "a" : { "$eq" : "07" } }, "queryHash" : "57073E11", "planCacheKey" : "D0FC46D6", "maxIndexedOrSolutionsReached" : false, "maxIndexedAndSolutionsReached" : false, "maxScansToExplodeReached" : false, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : 1, "arrayField.label" : 1 }, "indexName" : "a_1_arrayField.label_1", "isMultiKey" : true, "multiKeyPaths" : { "a" : [ ], "arrayField.label" : [ "arrayField" ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[\"07\", \"07\"]" ], "arrayField.label" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] } }
I don't see any particular reason to not use dependency analysis and at least move the $elemMatch ahead of the $sort if it's path is not a prefix or exact match of the dependencies for the preceding stage. In this example, it would allow us to get tighter bounds on the `arrayField.label` component of the index scan.