Subject Re: Optimizer Woes Svein Erling 2003-08-22T13:46:54Z
--- 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:
> ---------
>
> 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)))
>
> 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:
> -------------------------------------
> 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)))
>
> 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