Hello,
just another fight with IB's optimizer.

I am performing a left join such as:

select ...
from A
left join B on (A.FK1 = B.PK)
left join C on (A.FK2 = C.PK)

the primary keys (PK) of B and C consist of three columns each
(varchar(10), varchar(4) and integer, and no I can't change the database
structure at this stage) which are of course indexed. C has an
additional index on the first two fields of the primary key plus a bunch
of other (it is selective enough, but not as the primary key, I'd
suppose).

It happens that my IB5.6 optimizer scans A in natural order, B using its
primary key index and C using that additional index instead of the
primary key index.

I am sure that at some point the chosen plan was different (natural, pk,
pk, that is); the time it takes to open the query has gone from less
than a second to a couple of minutes.
All indexes have been rebuilt; the optimizer is just screwing something
up.
Anyone knows where can I start to look?
Ciao
--
____
_/\/ando