Re: Cost-based optimizers

On Tue, Dec 13, 2005 at 12:44:50PM +0800, Christopher Kings-Lynne wrote:
> And the answer is interesting as well:
>
> "I think we have to approach it in two ways. One is that you have to be
> able to execute good plans, and during the execution of a plan you want
> to notice when the actual data is deviating dramatically from what you
> expected. If you expected five rows and you?ve got a million, chances
> are your plan is not going to do well because you chose it based on the
> assumption of five. Thus, being able to correct mid-course is an area of
> enhancement for query optimizers that IBM is pursuing."

Well, now we have savepoints, it would actually be possible for a plan
to notice while running that's it's producing more or less than
expected and to abort, replan and start again.

Ofcourse, this is another can of worms. To do this you would have to be
able to have the failed query provide hints to the planner telling it
where it went wrong. Now, it may be possible to provide (via
post-mortem of an execution) a list of actual selectivites like:

table1.field1 = value (selectivity 5%)
func2(table2.field2) = value (selectivity 1%)

However, the biggest errors in selectivity occur when joining two
tables. Of the top of my head I can't think of any way to manage those
other than store the entire expression being tested...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.