I have a problem with a query. All the tables are joined by PK/FK or
FK/PK relationships:

Query #1:
---------

SELECT C.CVEART,C.DESCRIP,SUM(B.CANTIDAD) AS CANTID
FROM PEDIDOS A
JOIN DETALLEPED B ON (B.NUMERO=A.NUMERO) // (B.FK = A.PK)
JOIN ARTICULOS C ON (C.CVEART=B.CVEART) // (C.PK = B.FK)
WHERE A.FECHA BETWEEN '01/01/2003' AND '08/31/2003' // PED_FEC index
GROUP BY C.CVEART,C.DESCRIP

So far so good, plan uses all availabe indices:

Plan
PLAN SORT (JOIN (A INDEX (PED_FEC),B INDEX (RDB$FOREIGN5),C INDEX
(RDB$PRIMARY27)))

Adapted Plan
PLAN SORT (JOIN (A INDEX (PED_FEC),B INDEX (INTEG_12),C INDEX
(INTEG_63)))


Query #2:
Same query plus one additional join at the end:
-------------------------------------
SELECT C.CVEART,C.DESCRIP,SUM(B.CANTIDAD) AS CANTID
FROM PEDIDOS A
JOIN DETALLEPED B ON (B.NUMERO=A.NUMERO) // (B.FK = A.PK)
JOIN ARTICULOS C ON (C.CVEART=B.CVEART) // (C.PK = B.FK)
JOIN DEPARTAMENTOS D ON (D.NUMERO=C.GRUPO) // (D.PK = C.FK)
WHERE A.FECHA BETWEEN '01/01/2003' AND '08/31/2003' // PED_FEC index
GROUP BY C.CVEART,C.DESCRIP

This time, the FK index on DETALLEPED (B alias) is not used:

Plan
PLAN SORT (JOIN (B NATURAL,C INDEX (RDB$PRIMARY27),D INDEX
(RDB$PRIMARY1),A INDEX (RDB$PRIMARY98,PED_FEC)))

Adapted Plan
PLAN SORT (JOIN (B NATURAL,C INDEX (INTEG_63),D INDEX (INTEG_2),A
INDEX (INTEG_230,PED_FEC)))


Is this normal or is the optimizer faulty?

Thanks in advance,

-Jorge