[SERVER-31072] reorder $limit before $lookup in pipeline Created: 13/Sep/17  Updated: 30/Oct/23  Resolved: 17/Dec/19

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

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Katherine Wu (Inactive)
Resolution: Fixed Votes: 1
Labels: QFB, asya, neweng, optimization, performance, qopt-team, storch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-26442 Push $sort before $project and $addFi... Open
related to SERVER-43297 Inefficient query on view due to $lim... Closed
is related to SERVER-27829 Reduce performance impact of Document... Backlog
Backwards Compatibility: Fully Compatible
Sprint: Query 2019-12-16, Query 2019-12-30
Participants:

 Description   

In pipeline:

[ ... {$lookup:{}}, {$addFields:{}}, {$lookup:{}}, {$limit:10}, ... ]

moving $limit before the first $lookup stage will significantly reduce the amount of work that lookup and addFields have to do. We currently don't reorder $limit with $lookup, it appears.



 Comments   
Comment by Githook User [ 17/Dec/19 ]

Author:

{'name': 'Katherine Wu', 'email': 'katherine.wu@mongodb.com', 'username': 'kaywux'}

Message: SERVER-31072 reorder $limit before $lookup in pipeline if no $unwind is present
Branch: master
https://github.com/mongodb/mongo/commit/ae6b595845dd9975af01774678d52e93043a0d27

Comment by David Storch [ 09/Oct/19 ]

This came up in SERVER-43297. Flagging for consideration as a quick win. This change would enhance our ability to push $limit down into the PlanStage layer, beneath DocumentSourceCursor and beneath the MULTI_PLAN stage. This can reduce the amount of data examined by applying the limit prior to DocumentSourceCursor batching and prior to any buffering of partial result sets during multi-planning.

Comment by David Storch [ 06/Oct/17 ]

This is probably better solved by eliminating batching behavior in DocumentSourceCursor. This is tracked by SERVER-27829, but is complex due to the changes required in locking behavior.

Comment by Asya Kamsky [ 27/Sep/17 ]

I see there's a comment about that here:

Related ticket: https://jira.mongodb.org/browse/SERVER-26442?focusedCommentId=1471207&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-1471207

So maybe the sort reorder before $lookup should be handled there.

Comment by Asya Kamsky [ 27/Sep/17 ]

Not sure if this ticket should also cover moving $sort (on top document field) before $lookup or if that should be a separate ticket (since limit sometimes gets absorbed into $sort I think they are related).

Comment by Asya Kamsky [ 13/Sep/17 ]

Obviously this optimization can only be safely applied to a $lookup without an $unwind.

Generated at Thu Feb 08 04:25:54 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.