--- In firebird-support@yahoogroups.com, Kjell Rilbe wrote:
> Aage Johansen wrote:
>
> > I don't see it too often - maybe I take these points into
> > consideration as I write SQL statments ... I think one must
> > accept that optimisert aren't infallible.
>
> Yes, of course. It's just that this is a frequent problem for me. In
> a commercial product I would complain to the manufacturer. Loudly.

Looking at what you wrote earlier, I would suggest that the problem is
more than just the optimizer behaving badly:

> F_PK_ORGNR (0.000001)
> F_FTGSTAT (0.200000)
> F_USTAT (0.250000)
> A_FK_ORGNR (0.000001)
> A_PK_CFAR (0.000001)
> A_AESTAT (0.200000)
> A_AETYP (0.333333)
> A_USTAT (0.250000)

Excepting the PK and FK indexes, all the other indexes seems to have
lousy selectivity. In rare cases they may be useful when used with
care (e.g. an index on continent is useful if you want to locate
people in Antartica), but having several indexes with low selectivity
is more often than not a sign of poor design. Working with Aage, I
know that he only creates indexes when they are selective and useful.
That means he doesn't get as often into trouble with the optimizer
suggesting a poor plan. Sure, the optimizer ought to become even
better, but the developer is still responsible regarding which indexes
to create and which to avoid. I don't quite know what the figures
above refer to, but my guess is that values above 0.1 indicates that
the index should be removed. When other almost unique indexes are used
on the same table within the same query, using additional
non-selective indexes have about the same effect as trying to run and
pull Michael Schumachers car around the circuit to help the car move
faster...

Set