[SERVER-82217] Filter query with sort performs much worse with SBE Created: 16/Oct/23 Updated: 30/Nov/23 Resolved: 28/Nov/23 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | 7.0.2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Minor - P4 |
| Reporter: | Philipp Boe | Assignee: | Ivan Fefer |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||
| Issue Links: |
|
||||||||
| Assigned Teams: |
Query Execution
|
||||||||
| Operating System: | ALL | ||||||||
| Sprint: | QE 2023-11-27, QE 2023-12-11 | ||||||||
| Participants: | |||||||||
| Description |
|
We have recently upgraded from 6.0.4 to Mongo 7.0.2 and noticed a significant drop in query performance for certain filter queries using a sort. For example, this query takes 13 seconds with SBE:
With the classic engine it takes 1.5 seconds:
The collection contains around 600K documents and there is a compound index that contains the "fsType" field (as the first key). It looks like with SBE the query doesn't use the index because it seems to be processing all 600k documents. I've attached the explain outputs for the queries as files. Without the sort param SBE performs well (and seems to use the index):
|
| Comments |
| Comment by Ivan Fefer [ 30/Nov/23 ] |
|
No, there is no stable API for switching to classic, as the exact query engine used to process the query is a deep implementation detail and we are working to make the transition to SBE as invisible as possible. I understand that in that case we failed to do so. In general, our advice is not to set internalQueryFrameworkControl without instructions from our customer support. We have a plan to address the issue of SBE multiplanning in the next few patch releases for 7.0. With regards to continue using classic engine right now, your options are:
|
| Comment by Philipp Boe [ 30/Nov/23 ] |
|
Thanks for the info. I'll give these options a try. I do have a question regarding: If this doesn't help than switching back to Classic engine should. Is there a public/stable API for switching back to the classic engine? We've been using the internalQueryFrameworkControl parameter for that, but as edwin.zhou@mongodb.com pointed out this is an internal parameter. |
| Comment by Ivan Fefer [ 29/Nov/23 ] |
|
philipp.boersteken@crownpeak.com In the meantime, what can you do to in your case is: |
| Comment by Ivan Fefer [ 28/Nov/23 ] |
|
I have attached repro.js |
| Comment by Ivan Fefer [ 28/Nov/23 ] |
|
philipp.boersteken@crownpeak.com Thanks again for taking time to report this and giving us the practical example of this problem. This situation is a part of bigger issue with multi planning design for SBE that we are actively working on. Unfortunately, there is no quick fix for this problem, but more systematic solution is being created as I type this. I am going to close this ticket as a duplicate of SERVER-83196.
|
| Comment by Ivan Fefer [ 28/Nov/23 ] |
|
I investigated some more. Here are my findings:
Now let's examine what's happening in Classic engine:
What's happening in SBE engine:
|
| Comment by Ivan Fefer [ 27/Nov/23 ] |
|
I also see that there are no returned documents at all (because of "a" as a search value). |
| Comment by Ivan Fefer [ 27/Nov/23 ] |
|
How does the classic multi planner handles blocking stages? |
| Comment by Edwin Zhou [ 27/Oct/23 ] |
|
Hi philipp.boersteken@crownpeak.com, Thank you for your detailed ticket description and for providing the explain plans. Since "internalQueryFrameworkControl" is an internal parameter, we have chosen not to document this at this time. I will pass this over to our Query team to evaluate the execution of these queries. Kind regards, |
| Comment by Philipp Boe [ 16/Oct/23 ] |
|
On a side note: It took us quite some time to figure out how to force the classic query engine because we couldn't find any documentation. Are there any plans to document the internalQueryFrameworkControl parameter (or any other public API variant of that parameter)? |