Subject Re: [IB-Architect] Firebird Query Speed improvement
Author Jim Starkey
At 10:25 PM 10/28/01 -0500, Sean Leyne wrote:
>A better optimizer is certainly a goal of the Firebird project -- but when
>that will be tackled?

I don't think a "better" optimizer is required, just a debugged one.

Here is some background. There are two types of optimizers,
heuristic based and cost based. Heuristic based optimizers
apply rules to determine retrieval strategy. Cost base optimizers
estimate the costs of the various alternatives and take the
cheapest. Neophytes almost always start with heuristic optimizers
because they seem straightforward. The problem is that they
don't scale with complexity.

Interbase had a cost base optimizer (I made the standard mistakes
on earlier systems). It worked fine. Somebody (we'll leave his
name out) tried to teach it a new trick by embedded a piece of
heuristic. It solved his test case but broke the general case.

Somebody needs to make one of the many cases where the optimizer
gets the wrong answer and find out why it failed then fix it.

The optimizer isn't that hard to understand. It takes a bunch
of streams, permutes the orders while estimating cost (projecting
cost to prune the tree), then optimizes the resulting join order.
Just a little C code. It doesn't bite.

Jim Starkey