Runtime optimization is important too. Examples include the DB’s buffer cache (predicting future disk accesses based on previous accesses using e.g. LRU2) and self-tuning algorithms such as hybrid hash join (which figures out what key to use to partition, and which side of a join to build, based on data it sees while it is running).
It’s not either/or. You have to have both planning time and runtime optimization. Stats need to get a lot better. Especially if you are optimizing N-way joins for N > 5. I’m a big fan of the “How good are optimizers, really?” paper and the join-order benchmark. And approaches such as materializing intermediate result sets and re-planning. But even if your stats are not great, CBO is an organizing principle for an optimizer. Is it worth pushing that filter through that join? Cost before and after, and see which is better. Is it still worth it if I push down the aggregation? Again, run the costs. Without an organizing principle, every optimization (transform rule) has to be aware of every other rule, and that leads to a software architecture that is too tightly coupled, and eventually it is not possible to add a new feature without breaking existing functionality. My definition of a “cost-based optimizer” is one that is nondeterministic. By which I mean, it can investigate multiple paths, thereby avoiding local minima. Therefore I am a fan of simple, crappy stats such as estimated row count. Julian [1] http://www.vldb.org/pvldb/vol9/p204-leis.pdf <http://www.vldb.org/pvldb/vol9/p204-leis.pdf> > On Feb 4, 2020, at 7:30 AM, JiaTao Tao <[email protected]> wrote: > > Under big data, does CBO have such a big effect? > Node like filter/join/aggregate, their cost is estimated. > > There's one case, I call it runtime optimizing, it means optimizing while c > alculating, You adjust your execution plan in real-time based on the > execution statistics of the previous step(like join stragety selection, > hash or broadcast or SMJ), but it's not CBO, like in volcano planner. > > Regards! > > Aron Tao
