I prepared an ad-hoc query similar to:

select p.name, d.DateOfDiagnosis
from patient p
join diagnosis d on d.patientid=p.patientid
where d.diagnosis starting '206'

To my surprise, the suggested plan was
PLAN JOIN(P NATURAL, D INDEX(IDX_DIAGNOSIS, IDX_PATIENT_ID))

rather than
PLAN JOIN(D INDEX(IDX_DIAGNOSIS), P INDEX(IDX_PATIENT_PK))

Now, I had no problem forcing the plan I wanted by adding +0, but I
had expected Firebird 1.5.2 (or maybe WI-V6.3.2.4731 is 1.5.3?) to
make a better guess. The patient table contains about 1m records,
whereas the diagnosis table contains 1.2m records, hence the majority
of patients have only 1 diagnosis. Of these, I wanted about 20000
records. There's no doubt that the latter plan is far better than the
first, in writing this message I tried to prepare the two alternatives
so that I could verify the plans were as I believed and accidentally
hit 'First' rather than 'Prepare' on the worst alternative and had to
terminate IB_SQL rather than wait for a reply.

Why would Firebird even seriously consider the plan it chose and
prefer using NATURAL over the PK when the query involves two
similarly-sized tables? I might have followed its logic (though I
would have disagreed) if it was the IDX_DIAGNOSIS it chose to ignore,
but I consider our PKs to be extremely selective ;o)

Confused,
Set