<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

Svein,

Thanks a lot for your insight. Same problem occurs even if I remove
the WHERE clause that filters the date range. I'm using FB 1.0 on
Linux, will try FB 1.5 and see what happens.

Best Regards,

-Jorge