[SERVER-72037] Allow $replace(With|Root) to participate more in $match optimizations Created: 12/Dec/22  Updated: 31/Oct/23

Status: Open
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Charlie Swanson Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: query-product-scope-1, query-product-urgency-2, query-product-value-2
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-72038 Better projection analysis for $repla... Open
related to SERVER-73253 Better path tracking when renaming ne... Closed
is related to SERVER-27115 Track fields renamed by $project in a... Closed
Assigned Teams:
Query Optimization
Sprint: QO 2022-12-26, QO 2023-01-09, QO 2023-01-23
Participants:

 Description   

There are a couple optimizations which can analyze projections and move a $match stage around, I'm thinking primarily of the work done in SERVER-27115, but there are probably others since then. We often skip $replaceRoot and leave it for future work. My understanding is that the thinking was that the stage is probably not that common and when it is used, is could (often?) be used in a way that is not optimizable (for instance, replacing with the result of some complex expression tree).

I have seen a couple cases recently where folks are using it to promote a sub-document to the top level, which does seem pretty optimizable for a query like

> db.example.aggregate([{$replaceWith: "$subDocument"}, {$match: {x: 2}}])

Could be replaced with

> db.example.aggregate([{$match: {"subDocument.x": 2}}, {$replaceWith: "$subDocument"}])

This may be particularly useful in a view definition, or in combination with an $unwind where you are looking at array entries.

It's worth noting that there are definitely some edge cases to consider and possibly some bugs and caveats hiding, but I think this optimization is safe in at least many cases.


Generated at Thu Feb 08 06:20:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.