-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
None
-
None
-
None
-
None
-
None
-
None
-
None
The pipeline optimiser applies $match pushdown in subpipelines before selecting the $lookup cache boundary. The $lookup cache (DocumentSourceSequentialDocumentCache) caches the longest initial prefix that meets its requirements: stages must support dependency tracking, must not reference variables defined in the lookup scope, and must not require randomness (with the $search and $vectorSearch exceptions to the dependency-tracking check). A correlated $match ends the cacheable prefix, but earlier stages may still be cached.
SERVER-136214 shows the effect of pushdown of a correlated match before $group in a $lookup - it leaves no cacheable prefix. Without a usable index, this can result in a foreign collection scan for each outer row and in that case it resulted in 180s execution instead of <10s without the pushdown.
The following pipeline shows $match pushdown over a cacheable $group stage ($group does not depend on let variable). We do the $match pushdown here since at least 8.0.
db.products.aggregate([
{
$lookup: {
from: "orders",
let: {pid: "$product_id"},
pipeline: [
{$group: {_id: "$product_id", count: {$sum: 1}}},
{$match: {$expr: {$eq: ["$$pid", "$_id"]}}},
],
as: "joined",
},
}
]);
The above becomes a $match, then $group, and since the $match is correlated it cannot use the $lookup cache:
pipeline: [
{$match: {$expr: {$eq: ["$$pid", "$product_id"]}}},
{$group: {_id: "$product_id", count: {$sum: 1}}},
]
There's a few cases to consider, and in some the $match pushdown is preferable, in others $group is preferable to be first. The output size is relevant since the $lookup cache is bounded at internalDocumentSourceLookupCacheSizeBytes (100 MiB) after which it is dropped.
The case in SERVER-136214 is case F, where the $match pushdown is clearly not beneficial.
| Case | Outer rows | Useable Selective Index | Fits cache | $match pushdown preferred? |
|---|---|---|---|---|
| A | One | Yes | - | Yes |
| B | One | No | - | Mixed |
| C | Many | Yes | No | Maybe (likely yes) |
| D | Many | Yes | Yes | Maybe (likely no) |
| E | Many | No | No | Maybe (likely yes) |
| F | Many | No | Yes | No |
| Case | Notes |
|---|---|
| A | 1 outer row means no use for the cache; uses an index scan |
| B | 1 outer row means no use for the cache; overwhelming cost is the collection scan; cannot compare $group-then-$match and $match-then-$group without knowing selectivity and NDV. However a $group with accumulators produces further output, so likely $match-first is safer. A distinct scan-eligible $group could also use an index, however, which makes this in the "mixed" category without further analysis. |
| C | If we $group first, the first scan is likely a collection scan. When we overflow the cache, even if we reoptimise and pushdown the $match and use the index, we would have already paid the cost for the full collection scan, which can be avoided only with $match pushdown priority. This could be a cost-based decision based on the selectivity of the $match versus the output size ($group bucketing). |
| D | Per-row index lookups are not likely to beat reusing the cached output due to IO costs versus using the in-memory cache. We will do more compute on the cached output, but that is small and bounded (<100 MiB) |
| E | If we $group first, and that overflows the cache, even if we reoptimise and pushdown the $match, all scans remain collection scans, we just save compute from filtering before the $group, instead of after. |
| F | $groups once and caches the result; with $match pushdown we repeat the scan for each outer row. |
The goal is to improve at least case F without regressing workloads that benefit from the existing $match pushdown.