--- In firebird-support@yahoogroups.com, "dr_bentonquest" <bentonquest@m...> wrote:

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

Yes and occationally yes, is my answer.

You do not say which version of Firebird/InterBase you are using. Myself, I am on Firebird 1.0 and I know that Arno has done some great work on the optimizer for Firebird 1.5.

You do not say how big a portion of A that has FECHA BETWEEN '01/01/2003' AND '08/31/2003', and how many records with FECHA > '08/31/2003' (it has to be a considerable porportion for the index to be useful at all). The reason Firebird chooses this second plan, is that it thinks it is more efficient than the first plan - something that may or may not be true (noone can tell from the information you supplied).

The one thing that definitely is wrong with the second plan, is that A uses PED_FEC even though it already uses RDB$PRIMARY98, something that will inevitably slow things down. Changing your query to

SELECT C.CVEART,C.DESCRIP,SUM(B.CANTIDAD) AS CANTID
FROM PEDIDOS A
JOIN DETALLEPED B ON (B.NUMERO=A.NUMERO)
JOIN ARTICULOS C ON (C.CVEART=B.CVEART)
JOIN DEPARTAMENTOS D ON (D.NUMERO=C.GRUPO)
WHERE A.FECHA BETWEEN '01/01/2003' AND '08/31/2003' or 2=0
GROUP BY C.CVEART,C.DESCRIP

will avoid using PED_FEC, something that may actually result in the most efficient plan possible for your query (note that I am assuming that there are not a lot of records with FECHA some time into the future).

Occationally in situations similar to this, I wish there was a possibility of telling Firebird the order in which some tables should appear in the plan, and still let it choose the indexes itself. Though in your case I'm not certain that FECHA is a good index to use at all.

HTH,
Set