Occationally Firebird/InterBase chooses to use too many indexes. Even
though you are using the primary key in your plan, it thinks that it would
be even better if it added another and rdb$foreign76 is an index covering
either SYS_FILIA_ID or KOMPLET_ID or both. To improve speed in such cases,
one often have to prevent indexes from being used - either doing what you
have done or 'or it' with something evaluating to false:

SELECT KT.*
FROM KOMPLET_TOWAR KT
JOIN KOMPLET K
ON KT.SYS_FILIA_ID = K.SYS_FILIA_ID AND KT.KOMPLET_ID = K.KOMPLET_ID
LEFT OUTER JOIN TOWAR T
ON KT.SYS_FILIA_ID = T.SYS_FILIA_ID AND KT.TOWAR_ID = T.TOWAR_ID
LEFT OUTER JOIN TOWAR_TYP TT
ON T.TOWAR_TYP_ID = TT.TOWAR_TYP_ID
WHERE
(KT.SYS_FILIA_ID = 1 or 2=0) and KT.KOMPLET_ID = 112

I doubt it is possible to make Firebird always choose the best plan (short
of attempts done in its infancy, when the optimizer used hours to prepare
complex statements), but maybe Arno has made or will make it improve (I
haven't tried Fb 1.5 yet) so it doesn't try to add additional indexes once
a unique index (e.g. a primary key) is used.

Set

At 00:03 14.06.2003 +0000, you wrote:

>Hi,
>
> Can someone explain why following example:
>
>SELECT KT.*
>FROM KOMPLET_TOWAR KT
>JOIN KOMPLET K
>ON KT.SYS_FILIA_ID = K.SYS_FILIA_ID AND KT.KOMPLET_ID = K.KOMPLET_ID
>LEFT OUTER JOIN TOWAR T
>ON KT.SYS_FILIA_ID = T.SYS_FILIA_ID AND KT.TOWAR_ID = T.TOWAR_ID
>LEFT OUTER JOIN TOWAR_TYP TT
>ON T.TOWAR_TYP_ID = TT.TOWAR_TYP_ID
>WHERE
>KT.SYS_FILIA_ID = 1 and KT.KOMPLET_ID = 112
>
>has this plan (why using some FK?):
>
> PLAN JOIN (JOIN (JOIN (K NATURAL,KT INDEX
>(RDB$PRIMARY34,RDB$FOREIGN76)),T
> INDEX (RDB$PRIMARY29)),TT INDEX (RDB$PRIMARY26))
>
>...and execution time about 850ms whilst if I replace WHERE clause
>with:
>
>KT.SYS_FILIA_ID ||','|| KT.KOMPLET_ID = '1,112'
>
>the plan looks right:
>
> PLAN JOIN (JOIN (JOIN (K NATURAL,KT INDEX (RDB$PRIMARY34)),T INDEX
> (RDB$PRIMARY29)),TT INDEX (RDB$PRIMARY26))
>
>..and the execution time gets almost TEN TIMES less (90ms)!!??
>
>FB 1.0.2