"Arno Brinkman" wrote:

> Hi Carsten,
>
>
> Certainly not random, the optimizer calculates the join order based
> on estimated values and this is not random.
>
>> 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))
>
> This is a complete different query as the previous message?

This is not complete different, just another field, both have an index.

>> 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))
>
> Is there a index on f_id_werkstoff ? and more important to which
> table does it belong.
> If f_id_werkstoff is in t_apos and f_adatum in t_auftrag then never a
> index can be used for the condition (t_apos.f_id_werkstoff = x OR
> t_auftrag.f_adatum = x), because both tables are not "available" at
> the same time for evaluation.
>
>> 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.
>
> With adding "OR 2=0" the optimizer is never able to use indexes on
> the whole OR condition, thus above PLAN is as expected.
>
>> I think it's a bug.
>
> I don't think, but if you're sure the optimizer can do better i want
> a test-case so i'm able to reproduce it.
>

In my first message i posted the table definitions, so theoreticaly you can
test it.
f_id_werkstoff is in t_apos and is a foreign key so it has an index.
What i don't understand: I have 2 queries, both are very fast (<100ms) ,
but when i compound them with 'or' the query is not usable anymore (>10sec).
An or-clause should never take much longer than the addition of the 2
clauses.

mfg
Carsten.