Database Learning: Toward a Database That Becomes Smarter Every Time
arxiv.org
arxiv.org
From the Wikipedia page:
"Cracking is a technique that shifts the cost of index maintenance from updates to query processing. The query pipeline optimizers are used to massage the query plans to crack and to propagate this information. The technique allows for improved access times and self-organized behavior."
[0] https://pdfs.semanticscholar.org/1d5c/bc071f918143dbedf67a51...
I think ultimately, it's a much less tractable problem than anyone expected.
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).
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.
These constraints have effects that are not always obvious. For example, architecting a database kernel so that it can be dynamically optimized slows down computational throughput in the general case! This means that the benefits of dynamic optimization have to be sufficiently large that it offsets the performance tax of doing dynamic optimization at all. Also, because the performance tax is general, there is an implication that the optimization needs to be general across the set of runtime operations as well i.e. you can't optimize one operation at the cost of slowing down others. Nonetheless, there many areas such as cache replacement algorithms where dynamic optimization has big performance payoffs for the added complexity.
Second, dynamic optimization needs to be applicable incrementally, otherwise it tends to have a "stop the world" effect on performance when it is being applied, which is bad. A good example of this is dynamically optimizing storage for the actual distribution of queries it sees. Dynamically adding a new secondary index imposes a huge background cost that is visible in performance, so databases don't do it. However, dynamically optimizing individual pages for queries is common because it can be applied incrementally for pages you are already accessing anyway for a small one-time cost per page distributed over time as pages are accessed. And it only applies to pages you actually touch; building a new index pulls a lot of cold storage through the cache. Page level query optimization may be less effective overall than adding a second index but the fact that it can be applied incrementally makes it a preferred form of dynamic optimization.
In short, dynamic optimization has been studied and tried for many decades. Sophisticated databases do a lot of it but the mechanisms are chosen carefully to minimize adverse side effects in real world conditions.
My main issue is that most of the DB Stuff i do as a non-dba is so basic that it could be automated, but isn't for reasons that until your post came up weren't clear to me.
Having to guess why planer has wrong cost for some operation is already annoying, trying to guess why ML/AI created weird query plan would be even worse.