> Most if not all admin tools will tell you the query plan used. In
> iSQL, simply type 'SET PLAN'. Compare the two plans, and you will see
> the differences.
>
> Run SET STATISTICS for each of the indices involved and see if that
> fixes things. If not, then the problem is that, although Firebird has
> an accurate statistics, the optimiser got it wrong.
>
> A useful trick is to use +0 for integer/timestamp based fields and
> ||'' for string fields when you want to prevent an index from being
> used. Some other DBMS use 'hints' for this case.
>
> Sometimes, the culprit is a foreign key index which has poor
> selectivity.
>
> For example, 99% of employees of a company may have been born in a
> single country.
>
> Select e.Name
> from Employee e
> join Country c on (e.CountryOfBirthID = c.ID)
> where c.Name = :CountryName
>
> Firebird may choose
>
> PLAN JOIN (C NATURAL,E INDEX (FK_COUNTRY_1))
>
> When the following plan may be better.
>
> PLAN JOIN (E NATURAL,C INDEX (PK_COUNTRY))
>
> The following query would achieve this:
>
> Select e.Name
> from Employee e
> join Country c on (e.CountryOfBirthID+0 = c.ID)
> where c.Name = :CountryName
>
> > Are there any docs
> > on Firebird internals (other than the source code [#-o] ) that would
> > explain? Or maybe a general text on query optimization? I am
> concerned
> > that I will have to change the query again in six months time.
>
> Well the plan itself may change in 6 months time because the data
> shape may change, and this is a good thing, a sort of self
> maintenance.
>
> Craig Stuntz has written a few pages about IB, and a lot of it is
> just as applicable to FB, so you can google for his work and have a
> read.
>
> If you have trouble adapting your original query to prevent the rogue
> indices, then post their plans back to the list.
>
> Adam
>
This is a great help - thank you. I'll start looking at the plans. I
remember reading somewhere that Firebird was self-tuning, now I know
(at least in part) what was meant.