Hi,

> OK, I tried it on a different but similar query. It seems to achieve
> almost the same speed as dropping the F.Status condition altogether. But
> I was a bit surprised by the plan.
>
> This is the original plan for the query I just tested:
> PLAN JOIN (A INDEX (A_Status,A_Status),
> F INDEX (F_PK,F_Status,F_Status))
>
> This is the plan with your suggested modification of the query:
> PLAN JOIN (A INDEX (A_Status,A_Status),
> F INDEX (F_PK,F_PK))
>
> I would have expected something like this:
> PLAN JOIN (A INDEX (A_Status,A_Status),
> F INDEX (F_PK_STATUS,F_PK_STATUS))
> where F_ID_STATUS is the index you said I should create. (Note that you
> mixed up FK/PK on F in your reply.)
>
> What's going on?

When the optimizer can use a PK (full matched !) then this is already the best
match to make and thus ignores other indexes. Indeed i didn't saw that it was a
PK for F, but in above query the optmizer makes the right decision.

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