[SERVER-70113] Allow swapping $match stages that contain an $elemMatch predicate Created: 29/Sep/22  Updated: 02/Feb/24

Status: In Progress
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Nicholas Zolnierz Assignee: Milena Ivanova
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Query Optimization
Sprint: QO 2024-02-05, QO 2024-02-19
Participants:

 Description   

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.


Generated at Thu Feb 08 06:15:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.