Arno Brinkman wrote:

> Hi Carsten,

>
> Good question (may be wrong selectivity in index statistics), but the
> JOIN order has been changed and that is most effective result at the
> moment.
>
>> Do I have to create compound indexes on every combination of
>> or-clauses ?
>
> No, only when a compound index is usefull (all fields are used for
> filtering).
>
>> My problem is that the user can construct this select and it can
>> consist of much more joins with other tables and can be much more
>> complex (abitrary composition of 'or' and 'and' clauses).
>> So Its impossible to create indexes for all possibilities.
>
> If you remove the compound index, the query below should give the
> same PLAN.
>
> SELECT
> t_apos.ID_APOS
> FROM
> t_apos
> JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
> WHERE
> F_ID_WERKSTOFF = 11 OR
> F_ID_WERKSTOFF = 245
>
> In this query i force the optimizer to ignore f_id_auftrag to be used
> in a index, thus it will put t_apos at the first position in the join
> order.
>
> Could you run SET STATISTICS INDEX <name> for every index on t_apos
> and t_auftrag to be sure those are correct and then try also again
> with your original query.
>

The database is freshly restored, so i think all indexes should be optimal
created.
I think there are some serious bugs in the plan optimizer.
For me it looks random if the optimzer chooses to take a natural join (very
slow) or an index join (very fast)
If i use your trick and do the following query:
SELECT t_apos.ID_APOS
FROM t_apos
JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
WHERE t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004 11:23'
PLAN JOIN (T_APOS NATURAL,T_AUFTRAG INDEX (RDB$PRIMARY39))
Firebird makes a natural join and doesn't use the index on f_adatum
(duration 10sec).
If i remove your trick(+0) the query is fast (<100ms) and the index is used
PLAN JOIN (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX (RDB$FOREIGN1))

The following query is slow
SELECT t_apos.ID_APOS
FROM t_apos
JOIN t_auftrag ON f_id_auftrag = id_auftrag
WHERE f_id_werkstoff = 10
or t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004 11:23'
PLAN JOIN (T_AUFTRAG NATURAL,T_APOS INDEX (RDB$FOREIGN1))

and there is no chance to get it fast.
I changed it to:
SELECT t_apos.ID_APOS
FROM t_apos
JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
WHERE f_id_werkstoff = 10
or (t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004 11:23' or
2=0)
PLAN JOIN (T_APOS NATURAL,T_AUFTRAG INDEX (RDB$PRIMARY39))
i tried all possibilities, but Firebird always makes a natural join for
this.
I think it's a bug.

mfg
Carsten