-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
None
-
3
-
TBD
-
None
-
None
-
None
-
None
-
None
-
None
-
0
Imagine a pipeline that computes a new field and then matches on the result. We've recently seen an example of a customer query which involves something like this:
[
{
$project: {
computed: {
$switch: {
branches: [
{
case: {
$eq: [
"$comparison_field",
"foo"
]
},
then: "$field1"
},
{
case: {
$eq: [
"$comparison_field",
"bar"
]
},
then: "$field2"
}
],
default: { $const: null }
}
}
}
},
{
$match: {
computed: 42
}
}
]
At the moment, the system is unable to push the $match down past the projection. Since the field is computed, this predicate is not sargable and would not help to tighten index bounds for access path selection. However, this pushdown could still be quite valuable. We saw it in the context of a relational migration in which the $match was a predicate applied on top of a complex view. This pushdown would enable the match to be pushed all the way down to the base collections of the view, and by filtering the result set early any subsequent $lookups have much less work to do.
Pushing the $match down would require expressing a predicate on top of some computation. Theoretically this could look something like the example below:
[
{
$match: {
$expr: {
$eq: [
42,
{ // Inline the $switch!
$switch: {
branches: [
{
case: {
$eq: [
"$comparison_field",
"foo"
]
},
then: "$field1"
},
{
case: {
$eq: [
"$comparison_field",
"bar"
]
},
then: "$field2"
}
],
default: null
}
}
]
}
}
}
]
In practice, this would require correct conversion of a MatchExpression into an Expression. I don't believe this is something our system currently can do, and we will have to be careful about things like array traversal semantics and null semantics to make such a rewrite actually correct in today's engine.
- related to
-
SERVER-103724 $sort-$project query on a sharded collection can assert on merging node
-
- In Progress
-