[SERVER-61915] Provide the ability for queries to "hint" and use a particular Query Engine Created: 03/Dec/21  Updated: 30/Jan/24

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

Type: Improvement Priority: Major - P3
Reporter: Harshad Dhavale Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 2
Labels: sbe
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-77469 Propagate QuerySettings from mongos t... Closed
Duplicate
duplicates SERVER-82547 Make "queryFramework" an input parame... Closed
Related
is related to SERVER-82591 Persistent query setting / hint to in... Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

MongoDB version 5.X+ introduced the new Slot Based Execution (SBE) query engine. At the same time, the Classic Query Engine is available as well. This is a feature request to introduce a functionality that will allow users to "hint" and use a particular Query Engine while running a query. The benefits of this will be:

  • It will provide the ability to compare/investigate the performance difference for queries while using either query engine, in real-time.
  • It would make it unnecessary to "switch" the entire query engine (by enabling/disabling SBE) for all the queries, just to compare/investigate the query performance difference with the 2 engines for a small subset of queries.


 Comments   
Comment by Jussi Kauppila [ 17/Nov/23 ]

We also have major issues with query plans with SBE and 7 (have to make another issue about them). Now it looks like we will have to start an annoying project to downgrade back to mongo 6 because of these issues. 

Upvoting this heavily. 

Comment by Johnny Shields [ 06/Nov/23 ]

It's unfortunate that this ticket has sat unaddressed since 5.x. We had major issues with the SBE in 7.0 (SERVER-79088, SERVER-82548, SERVER-82549) that could have been mitigated if we had a way to opt-in/opt-out of the SBE gradually. I humbly suggest MongoDB to address this before more folks start upgrading to 7.0.

For reference, it could be somewhere else besides the "hint" API, for example, it could be something similar to writeConcern.

Comment by David Storch [ 07/Dec/21 ]

As part of the development of the new query optimizer, we are expecting to make some changes to the user-facing hint API. When doing so, we can consider in tandem whether or not to schedule this improvement.

Note that a per-query hint to force the classic engine would need to be excluded from the stable API version 1. Also, since we eventually plan to remove support for the classic engine entirely at some point in the medium to long term future, we would need to clearly communicate to customers that this hint is a temporary solution; it will not be supported indefinitely, and a future release would drop support for this hint when the classic engine is removed.

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