aggregation $match before $lookup optimization doesn't happen when $expr: $eq is used

XMLWordPrintableJSON

    • Fully Compatible
    • ALL
    • Query 2019-12-30, Query Optimization 2021-02-22, Query Optimization 2021-03-08, Query Optimization 2021-03-22, Query Optimization 2021-04-05
    • 120
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      We check if $match has an expression that isn't on "as" and move it before $lookup - if it has an expression on "as.x" we move it into $lookup.

      Expected behavior:

      db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z
":10,x:{$eq:5}}})
{
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
                                        "x" : {
                                                "$eq" : 5
                                        }
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "tpcds10.foo",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "x" : {
                                                        "$eq" : 5
                                                }
                                        },
                                        "winningPlan" : {
                                                "stage" : "EOF"
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        }
                },
                {
                        "$lookup" : {
                                "from" : "b",
                                "as" : "a",
                                "localField" : "x",
                                "foreignField" : "y",
                                "unwinding" : {
                                        "preserveNullAndEmptyArrays" : false
                                },
                                "matching" : {
                                        "z" : {
                                                "$eq" : 10
                                        }
                                }
                        }
                }
        ],
        "ok" : 1
}

      Using $expr for x:5 breaks the $lookup matching optimization though equality for x:5 still gets moved before $lookup but does not get pushed into cursor:

      db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z
":10,$expr:{$eq:["$x",5]}}})
{
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "tpcds10.foo",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                        },
                                        "winningPlan" : {
                                                "stage" : "EOF"
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        }
                },
                {
                        "$match" : {
                                "x" : {
                                        "$_internalExprEq" : 5
                                }
                        }
                },
                {
                        "$lookup" : {
                                "from" : "b",
                                "as" : "a",
                                "localField" : "x",
                                "foreignField" : "y",
                                "unwinding" : {
                                        "preserveNullAndEmptyArrays" : false
                                }
                        }
                },
                {
                        "$match" : {
                                "$and" : [
                                        {
                                                "a.z" : {
                                                        "$eq" : 10
                                                }
                                        },
                                        {
                                                "x" : {
                                                        "$_internalExprEq" : 5
                                                }
                                        },
                                        {
                                                "$expr" : {
                                                        "$eq" : [
                                                                "$x",
                                                                {
                                                                        "$const" : 5
                                                                }
                                                        ]
                                                }
                                        }
                                ]
                        }
                }
        ],
        "ok" : 1

            Assignee:
            Alya Berciu
            Reporter:
            Asya Kamsky
            Votes:
            1 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved: