You are underestimating the necessary design tension. If you expose hints, then you've constrained how your optimizer works because it has to work with those hints. (Oracle, for example, has been fighting this battle for ages.) Once you've built an optimizer, people come to depend on it so it is critical that you get it right.
They will need optimizer eventually, and they know it. But I'm glad that they are not implementing it before they are ready.
Secondly your selectivity estimation point can go either way, and on the whole I don't like it. I've personally experienced the situation where a database recomputes statistics, the CBO decides that a new query plan is needed for a common query, it made a poor choice, and the first that any human hears about it, the site is down.
The problem here is that the risk profile for the application of trying to be smart here is completely backwards. In general, as long as a query is not a bottleneck, I don't care about making it faster. Oh, you made it 2x faster? I didn't mind before and I'm unlikely to even know that you did so. But if you JUST ONCE switch to a bad plan on production without warning, your users WILL notice, they WILL care, and they WILL NOT be happy.
As a developer, I don't care that you make the right choice 95% of the time. I want you to make a predictable choice. Because if you're making a bad choice in development, I've got a window of opportunity to notice and do something about it where nobody cares. But if you randomly make changes on production, every single mistake counts.
Oh, but you say that this just means that you need stored query plans? I agree, and this is an example of why the behavior of the optimizer has to be thought through very carefully before you just throw something out there, people come to depend on it, and then you realize that you have put barriers to thinking of it the way you want to think of it.
This accords with our experience with large Oracle databases as well.
We've had a few panics caused by a DBA having updated table statistics when trying to optimize some query. This would occasionally cause sudden, massive changes in the way that other unrelated queries were performed, which queries would not finish and sometimes bring down the database.
These experiences caused us to have to change our procedures and keep the test database data closely in sync with the production data, so we'd know how newly gathered statistics would affect queries. The database is large enough that having testing be a full and recent copy of production is pretty painful. Oracle has since introduced features in 11g that allow pinning query plans, we've yet to try these though.
In the simplest form, you could say, "I have 2 reasonable plans, let's try A, and if it takes above time X, then start B in parallel and go with whatever finishes first."
You could ramp up the idea to handle changing query plans based on updated statistics by sending some fraction of queries to one plan, and some to another. Then keep stats on how that worked out for you.
Basically never simply flip the switch in production to try a new, unproven query.
Incidentally 12g advertises that they will actually collect statistics during query execution and based on those will validate the query plan. If that works, then this problem should get better. But of course that comes with overhead, and is likely to be a complex piece of code, so you tell me whether you trust them to get it right right away.
A cost-based optimizer also needs a way to handle the combinatorial explosion of possible plans. For MongoDB, maybe they can be exhaustive or use a simple strategy. But for a SQL DBMS, the search space is way too huge, and you need a way to navigate it intelligently.
This "try the plan out" idea has come up many times, and it's a reasonable idea, but it's nothing new and not a complete solution. You need a way to evaluate whether a plan has a reasonable chance of being the best one before the whole plan is even constructed, much less executed.
Combinatorial explosion is also one of the reasons that humans generally aren't great at coming up with non-trivial plans, either.
A good optimizer is a combination of many good ideas; so I don't mean to be discouraging, just that one idea is not the full story.
If you are worried about the risk of a changing plan, you are between a rock and a hard place. If you lock the plan down (or hard-code it in the application), and the data characteristics change, that could be disastrous. If you don't lock it down, that's a leap of faith that the optimizer will adapt gracefully.
I understand the issues you've brought up in your post as well as the traditional methods of pruning the search space of query plans through using left-deep join trees and histograms for calculating selectivity.
My top-level point was that there is no way humans will come up with the optimal query plan by hand, and like you said even if they do - the data will change.
As an aside, the reason Mongo can use such a simple but effective strategy is because it doesn't support joins, which makes calculating the optimal plan much, much easier. RethinkDB does, however, and as such, a CBO is that much more important.
I am not disputing the value of a cost based optimizer.
I'm just pointing out that if you have an existing plan that seems to be currently working the default should be to not just switch it up without testing that your switch didn't hurt anything. Instead you need to use care.
Yup. This kind of complexity they have been forced into because the simple general solution that they think should work and which does the vast majority of the time also routinely causes pants on fire emergencies.
I frequently see apps with thousands of distinct query signatures. Having a developer manually chose indexes, join ordering, aggregation method, (just to name a few) for every single query, and then select multiple plans because input parameters absolutely do result in scenario where plans can be 10,000x off in performance -- well, that's just untenable.
Not trying to claim that CBOs are the panacea here, but let's be realistic. Having developers manually plan every single query is not the right choice.
There is a logical fallacy in what you've said. It absolutely is true that there are times when input parameters can be a factor of 10k in performance. It is also absolutely true that applications can have thousands of distinct query signatures.
What DOES NOT follow is that most of those distinct query signatures are very important to the application. A few are. But most are not. However volunteering to add the wrong one can take the application down.
Having developers manually plan every single query is not the right choice.
I agree. However the many applications successfully built on MySQL demonstrate that for a lot of people, crappy but consistent planning is good enough. At least then people can know where the problems are, and fix them.
They will need optimizer eventually, and they know it. But I'm glad that they are not implementing it before they are ready.
Secondly your selectivity estimation point can go either way, and on the whole I don't like it. I've personally experienced the situation where a database recomputes statistics, the CBO decides that a new query plan is needed for a common query, it made a poor choice, and the first that any human hears about it, the site is down.
The problem here is that the risk profile for the application of trying to be smart here is completely backwards. In general, as long as a query is not a bottleneck, I don't care about making it faster. Oh, you made it 2x faster? I didn't mind before and I'm unlikely to even know that you did so. But if you JUST ONCE switch to a bad plan on production without warning, your users WILL notice, they WILL care, and they WILL NOT be happy.
As a developer, I don't care that you make the right choice 95% of the time. I want you to make a predictable choice. Because if you're making a bad choice in development, I've got a window of opportunity to notice and do something about it where nobody cares. But if you randomly make changes on production, every single mistake counts.
Oh, but you say that this just means that you need stored query plans? I agree, and this is an example of why the behavior of the optimizer has to be thought through very carefully before you just throw something out there, people come to depend on it, and then you realize that you have put barriers to thinking of it the way you want to think of it.