Using Firebird 2.1.1 for Windows here. I have a this PICKUP table with two indexes:

CREATE INDEX PICKUP_CLIFEC ON PICKUP (CLIENTE, FECHAREC);
CREATE INDEX PICKUP_FECHAREC ON PICKUP (FECHAREC);

Hint: CLIENTE column is varchar(38), FECHAREC column is date.

Now in a JOIN query that uses the following WHERE clause:

where (A.CLIENTE=:clienteid) and (A.FECHAREC between CURRENT_DATE-31 and CURRENT_DATE)

I get this plan:

PLAN SORT (JOIN (A INDEX (PICKUP_FECHAREC), B INDEX (RDB$PRIMARY26), C INDEX (RDB$PRIMARY14)))

It's using PICKUP_FECHAREC index (which is based only in the date column) and I think it should be using the PICKUP_CLIFEC index, which starts with the CLIENTE column just like the WHERE clause needs.

Am I missing something or is the optimizer missing the mark?

P.S. Below is the complete query if you want to look at it:

select A.ID,A.NUMERO,A.FECHAREC,A.HORAREC,A.ORIGEN,A.ORICIUDAD||', '||A.ORIESTADO,
A.DESTINO,A.DESCIUDAD||', '||A.DESESTADO,A.BULTOS,A.PESO,A.VOLUMEN,C.DESCESP
from PICKUP A
join RASTREO B ON (B.ID=A.ID)
join STATUS C ON (C.NUMERO=B.STATUS)
where (A.CLIENTE=:clienteid) and (A.FECHAREC between CURRENT_DATE-31 and CURRENT_DATE)
order by A.FECHAREC desc, A.HORAREC desc