[SERVER-41171] MongoDB $lookup using "$expr" is slow compared to simple $lookup Created: 16/May/19 Updated: 07/Apr/23 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 4.0.8 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Daniele Tassone | Assignee: | Backlog - Query Execution |
| Resolution: | Unresolved | Votes: | 8 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Assigned Teams: |
Query Execution
|
||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
| Comments |
| Comment by Asya Kamsky [ 10/Sep/21 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I changed the name of the ticket since the issue is not additional pipeline stages but rather using $expr instead of localField/foreignField. Note that as of 5.0 due to | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Baskar K [ 05/Mar/21 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Facing the same issue in version 4.4.2. Could you please share the status of this issue (whether its planned in upcoming release or not). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Annie Black [ 23/Feb/21 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Sorry!! | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dennis Hoefakker [ 18/Sep/20 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
How can we give this more priority. I think it's very important. The a big reason to use uncorrelated lookups is you can speedup the aggregation (a $projects results in lesser data out of the $lookup, lesser data that needs to go through the pipeline). Currently the "old" $lookup is way faster, but requires extra steps which takes time and results in "code" solutions.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Pablo Guerrero [ 13/Dec/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I have the same problem as @Daniele Tassone had. I'm trying to use a projection after making the relationship between two collections but It is really slower compare with normal $lookup. Could you please tell us about what is happening with $lookup with embedded pipeline?
Thanks so much for your work! | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Eric Sedor [ 23/May/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
daniele.tassone@tomadaproductions.it I've made some edits to the reproduction steps you provided, including removing extraneous pipeline stages and adding a timer. We can see the difference in performance you are pointing to. While some performance change may be expected when using lookup with a pipeline, we are going to look into whether this magnitude of difference is expected given the data set you've assembled. Importantly though, I believe removing the $count stage isn't a helpful signal for this test. Doing so causes a test to time how quickly the client receives the first page of cursor results, which is very fast compared to the time it takes for the server to consume all results for count purposes. In the MongoDB shell... To build the data set:
To test performance:
Output (a local reproduction):
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Eric Sedor [ 17/May/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thanks daniele.tassone@tomadaproductions.it; We will review this report and let you know if we have any questions to help clarify. |