5 ms·

Actually, I worked on a query optimizer that did just such a thing in terms of running multiple physical plans in sort of a race to see which finished first and

Actually, I worked on a query optimizer that did just such a thing in terms of running multiple physical plans in sort of a race to see which finished first and tried to learn valuable statistics from that process. Actually, what it did first was use a time, CPU and memory constrained space to explore multiple logical query plans. Then it would choose the top 3 logical query plans, as well as one or two random logical query plans (whether they performed well or not). The top 3 plans would be turned into physical execution plans and run in parallel (assuming the underlying hardware could handle it). The first query to finish would be returned and the others cancelled/killed. Later when there was slack time on the system, it could take the other logical plans, convert them into physical plans and test them out to see how they performed. Thus generated more useful information to feed back. Machine learning is a great fit to database engine query optimizers, even for unexpected costly things, like data type conversions and results of expression evaluations (think something like memoization).

pkaye 9y ago
How did it work out in the end? Are there any limitations in its use?
- sixdimensional 9y ago
  One observation was the extra load it created (obviously) doing the learning work. In practice it became a feature you could turn on or off and many seemed too fearful to use it in production. The unpredictability of the performance during the early stages also caused fear. There was talk about running query plan explorations autonomously as soon as the system was turned on and had tables defined with relationships, but that path seemed to deviate from the goal of the project at the time too much, so was ignored. That said, optimizers don't always get things right the first time, so there were clear cases I saw in early concept stages where running multiple options made a huge difference. For example, testing two different join orders could result in one query that took under a minute and one that took over 20 minutes. So it definitely made an impact. We found that using learning in other areas also had a meaningful impact, like in saving time during data type calculations. Generally, I can say, for the project I worked on, there were clear benefits but it didn't progress to a point of learning all the limitations, unfortunately. I'd say still an area worth research, based on my experience.

pkaye 9y ago

How did it work out in the end? Are there any limitations in its use?

sixdimensional 9y ago
One observation was the extra load it created (obviously) doing the learning work. In practice it became a feature you could turn on or off and many seemed too fearful to use it in production. The unpredictability of the performance during the early stages also caused fear. There was talk about running query plan explorations autonomously as soon as the system was turned on and had tables defined with relationships, but that path seemed to deviate from the goal of the project at the time too much, so was ignored. That said, optimizers don't always get things right the first time, so there were clear cases I saw in early concept stages where running multiple options made a huge difference. For example, testing two different join orders could result in one query that took under a minute and one that took over 20 minutes. So it definitely made an impact. We found that using learning in other areas also had a meaningful impact, like in saving time during data type calculations. Generally, I can say, for the project I worked on, there were clear benefits but it didn't progress to a point of learning all the limitations, unfortunately. I'd say still an area worth research, based on my experience.