-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Fully Compatible
-
ALL
-
v5.0
-
Query Execution 2021-05-17, Query Execution 2021-05-31, Query Execution 2021-06-14, Query Execution 2021-06-28, Query Execution 2021-07-12, Query Execution 2021-07-26
For context, here is a brief description of how the trial period in the classic engine works, assuming that we have two plans, A and B. Given a budget of "work units", usually set to something fairly large such as 10,000 works, we allow each plan to do a unit of work in a round robin fashion. That is, A performs one unit of work, then B performs one unit of work, then A performs a second work, B a second work, and so on. If at any point during this process one of the plans reaches EOF, then that plan becomes the winner and the trial period ends.
The SBE multi-planner is fundamentally different because there is no concept of "works". Instead, each plan is granted a similar budget of "reads". A read is defined here as a single operation on a storage engine cursor, such as an index seek or reading a single index key. The budget is calculated in the same way as for the classic engine, so it is something pretty large such as 10,000. Instead of the round-robin units of work, the SBE multi-planner will execute each plan until it either reaches its budget and "exits early", or reaches EOF. First, plan A executes until hitting its budget or EOF, and then plan B does the same.
Let's suppose that plan A reaches EOF with approximately 10 works/reads, but plan B requires 100,000. In a scenario like this, SBE multi-planning takes much longer than classic engine multi-planning to achieve the same result. The classic engine will work each plan 10 times, at which point A reaches EOF and the trial period ends. In contrast, SBE will first execute plan A. It performs 10 reads and finishes. Then, SBE will execute plan B. It executes 10,000 reads before it hits its budget and exits early. They key observation is that SBE has done orders or magnitude more work as part of the multi-planning trial period.
One way to mitigate this would be to progressively lower the budget of reads as each plan runs its trial period. However, this would only work if the best plan happens to run its trial period first. The issue would still occur if the bad plans run their trial period first, and the best plan runs its trial last.