Subject Re: [firebird-support] Re: Optimizer randomly chooses plans (WAS Big table and DB slowdowns)
Author Jerome Bouvattier
> > > Set's strategy, for making it
> > > ignore that index, will help avoid this slow-down effect from the
> >optimizer
> > > mistakenly choosing that index, based on old statistics.
> >
> >And can I be sure, using this technique, that the optimizer has enough
> >to always pick the right PLAN ?
> make it choose the SAME plan. Not necessarily the right one,
> though.

Good. That's what I wanted to hear.

> Most of the time, the optimizer is right when it decides on
> Natural - it has worked out that it would be cheaper than riding the
> index. This isn't SQLServer, which is entirely rule based and uses a
> completely different architecture for indexing and optimization, based on
> rules alone.
> With a relatively smaller set on the left, the Fb optimizer will often -
> correctly - choose the natural scan, because the left stream will be built
> into a bitmap, which will be faster than pulling up the index pages.

Ok, in my case the left stream is very limited and the right one is huge.
FB, though, chooses a natural scan for the *right* stream.
Well, I think the two joins plus the two indices with identical selectivity
on the left table might be the source of the hesitation here as Set pointed

I'll investigate further.