[SERVER-83456] Skip Filter->Sargable rewrite for collscan plans Created: 20/Nov/23  Updated: 08/Jan/24  Resolved: 08/Jan/24

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.3.0-rc0

Type: Task Priority: Major - P3
Reporter: Anton Korshunov Assignee: Timour Katchaounov
Resolution: Fixed Votes: 0
Labels: M7
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-83442 Reorder predicates in a Filter node b... Backlog
depends on SERVER-83937 Pushdown top-level fields from Evalua... Backlog
depends on SERVER-81973 Implement generic logic to configure ... Closed
depends on SERVER-83441 Pushdown top-level fields from Filter... Closed
depends on SERVER-83574 Rewrite single-field disjunctions to ... Closed
depends on SERVER-83839 Implement query-knob guarded logic to... Closed
depends on SERVER-84378 Limit the number of pushed down field... Closed
Related
related to SERVER-84366 Incorrect selectivity in explain for ... Backlog
is related to SERVER-79488 [CQF] Different behavior with empty-a... Open
Backwards Compatibility: Fully Compatible
Sprint: QO 2023-12-25, QO 2024-01-08
Participants:

 Description   

A primary goal of a Sargable node is to provide a facility for solving an access path selection problem in Bonsai. In the absense of indexes in a collection there is no need to perform this conversation as we won't be doing any access path selection work, and eventually we will get from Filter to Sargable and back to Filter node. However, a Sargable node presently participates in other optimizations, such as predicate reoredeing based on selectivity or top-level fields pushdown. If these optimizations could be "extracted" from the Sargable node and implemented as stand-alone rewrites, we could skip this rewrite when we know that we're about to generate a collection scan plan. Another interesting optimization done by the Sargable node is interval simplification (e.g., a > x && a > y becomes a > max(x, y)). However, this simplification can only be done if we know that a given path is non-multikey, and without indexes this information is not available, meaning we cannot simplify such intervlas. The only case when we can do that is a predicate within $elemMatch. We will support this case when we extend the boolean simplifier to support interval simplification so that we can perform this optimization much earlier and w/o the need to create a Sargable node.



 Comments   
Comment by Githook User [ 21/Dec/23 ]

Author:

{'name': 'Timour Katchaounov', 'email': '34627040+timourk@users.noreply.github.com', 'username': 'timourk'}

Message: SERVER-83456 Default skip Filter->Sargable rewrite for collscan plans (#17726)

GitOrigin-RevId: 43ffca2fd4c059f235a9495dc9c18b6abd278802
Branch: master
https://github.com/mongodb/mongo/commit/83730c7d2dd3a503ad102e4ba74a18649b5e9b40

Comment by Anton Korshunov [ 21/Nov/23 ]

> There is a narrow case where these kinds of simplifications can happen in M2, which is when an M2-eligible query against a collection with indexes has a $natural hint. In this case, we will allow the query to use Bonsai, and we will use the multikeyness information from the indexes to remove Traverses. This is another time when we could do interval simplification within Bonsai, though it might not be very common

Good point. I guess for natural hints we can still use the rewrite. We're going to make OptPhase manager configurable so that we can specify which phases/rewrites to run based on the query shape and metadata.

Comment by Anton Korshunov [ 21/Nov/23 ]

> This is not necessarily the case. A type-bracketed comparison in find is implicitly an $elemMatch (get an element which is both greater than 5 and less than empty string), which is why we end up performing interval simplifications even with M2 queries with no indexes.

This simplification makes sense when we push an interval into index bounds. For a simple filter I guess it's better to keep the type bracketing interval intact so that in ABT lowering we can detect this pattern and use SBE primitives which already support type bracketing. This is to your question which you raised at the huddle just recently. 

Comment by Ben Shteinfeld [ 20/Nov/23 ]

> The only case when we can do that is a predicate within $elemMatch.

This is not necessarily the case. A type-bracketed comparison in find is implicitly an $elemMatch (get an element which is both greater than 5 and less than empty string), which is why we end up performing interval simplifications even with M2 queries with no indexes.

Comment by Hana Pearlman [ 20/Nov/23 ]

> However, this simplification can only be done if we know that a given path is non-multikey, and without indexes this information is not available, meaning we cannot simplify such intervals.

There is a narrow case where these kinds of simplifications can happen in M2, which is when an M2-eligible query against a collection with indexes has a $natural hint. In this case, we will allow the query to use Bonsai, and we will use the multikeyness information from the indexes to remove Traverses. This is another time when we could do interval simplification within Bonsai, though it might not be very common

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