Hi,

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

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

The problem lies in the fact that the FB1.5.x (and earlier) optimizer doesn't optimize "IS NULL /
STARTING WITH" operations very well. First it isn't used for the JOIN order calculation and second
those are not combined (when possible ofcourse) with other comparisons.
I'm sure the FB2 optimizer does a better job here, but with using the new ODS!

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info