Hi Carsten,

> > 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.

Should be, but check to be sure.

> I think there are some serious bugs in the plan optimizer.

This is not a bug, but as designed.
I agree that he could do (and should do) a better job in some circumstances, but
the optimizer hasn't serious bugs.

> For me it looks random if the optimzer chooses to take a natural join (very
> slow) or an index join (very fast)

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?

> Firebird makes a natural join and doesn't use the index on f_adatum
> (duration 10sec).

Yes, because only a index can be used for f_adatum and/or id_auftrag and both
are in table t_auftrag.
Again, please use everywhere aliasses so it's readable to what tables every
field belongs.

> 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))

Thus the optimizer has calculated the join order correct here.

> 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.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info