If there are multiple $match stages in a subpipeline, this prevents join optimization from kicking in:
db.many_rows.aggregate([{"$lookup":{"from":"many_rows","as":"right","pipeline":[{"$match":{foo: 1}}, {$match: {bar: 1}}]}},{"$unwind":"$right"}]).explain()
The following cases all work and allow join optimization:
- multiple $match stages at the top-level (they are fused)
- $and of two predicates inside a single $match
- two predicates inside a single $match with implicit $and
Could this be to a missing rewrite where $match stages in subpipelines are not fused?