[SERVER-21612] Combine post $lookup $match on looked up field Created: 22/Nov/15  Updated: 04/Nov/20  Resolved: 25/Apr/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.3.6

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Benjamin Murphy
Resolution: Done Votes: 1
Labels: bi-performance, optimization, performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
Duplicate
is duplicated by SERVER-4506 aggregation: optimize by pushing mat... Closed
Related
related to SERVER-51303 Lookup stage followed by $match on ty... Closed
related to SERVER-29618 $geoWithin in aggregation pipeline af... Closed
related to SERVER-27213 Two $match stages combine incorrectly... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 11 (03/14/16), Query 12 (04/04/16), Query 13 (04/22/16), Query 14 (05/13/16)
Participants:
Case:

 Description   

In the scenario where the agg pipeline is:

{$lookup:{from:"c2",localField:"foo",foreignField:"bar",as:"c2s"}},
{$unwind:"$c2s"},
{$match:{"c2s.f2":someValue}}

we should be able to merge the query for f2:someValue into the query into "c2" as find:{bar:"$foo", f2:someValue} as a performance optimization.



 Comments   
Comment by Charlie Swanson [ 26/Oct/16 ]

I'd like to add a note to benjamin.murphy's comment above:

2. A $match that consists entirely of predicates upon the "as" field of the $lookup can be moved inside the $lookup and executed as part of the query on the foreign collection.

There are some caveats here, namely:

  1. We can only absorb the predicates on the "as" field if the $lookup stage has already absorbed an $unwind. i.e. if the original pipeline had a shape like this:

    [{$lookup: {..., as: 'results'}}, {$unwind: "$results"}, {$match: {results.foo: "bar"}}]
    

    I think this code comment sums it up well.

  2. We cannot absorb predicates on the "as" field if the $unwind stage is including the array index or if the $unwind stage is preserving null and empty arrays (see the includeArrayIndex and preserveNullAndEmptyArrays options). This is similarly described in the comment following the one above.
Comment by Benjamin Murphy [ 25/Apr/16 ]

This ticket introduces a new optimization to the aggregation pipeline, enabling $lookup to swap with, or absorb, a $match, with the following semantics:

1. A $match that is entirely independent of the $lookup can be moved before the $lookup.

2. A $match that consists entirely of predicates upon the "as" field of the $lookup can be moved inside the $lookup and executed as part of the query on the foreign collection.

Comment by Githook User [ 25/Apr/16 ]

Author:

{u'username': u'benjaminmurphy', u'name': u'Benjamin Murphy', u'email': u'benjamin_murphy@me.com'}

Message: SERVER-21612 Reorder lookup and match.
Branch: master
https://github.com/mongodb/mongo/commit/3e9149a2db5a6d2e2ecf262340dc052d4dfaaea8

Generated at Thu Feb 08 03:57:52 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.