Svein Erling Tysvær wrote:

> 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.

My guess is that the optimizer assigned a really bad selectivity to the
"d.diagnosis starting '206'" condition. The selectivity kept in 1.5 is
based on the whole value of the combined key fields. In 2.0,
selectivity is kept for each segment of a segmented key, which will help
some cases, but not yours. If the first three characters of the
diagnosis are often separately significant, you might consider creating
a separate indexed field on just those values.


Regards,


Ann