Hi,

> i really have some perfomance problems with selects which includes or-clause.

(using Firebird 1.5.2 RC3)

> This one is fast (<10ms):
> SELECT t_apos.ID_APOS
> FROM t_apos
> JOIN t_auftrag ON f_id_auftrag = id_auftrag
> WHERE F_ID_WERKSTOFF = 11;
> PLAN JOIN (T_APOS INDEX (RDB$FOREIGN161),T_AUFTRAG INDEX (RDB$PRIMARY39))
> RDB$FOREIGN161 = F_ID_WERKSTOFF
> RDB$PRIMARY39 = ID_AUFTRAG

> This one is slow (>15sec)
> SELECT t_apos.ID_APOS
> FROM t_apos
> JOIN t_auftrag ON f_id_auftrag = id_auftrag
> WHERE F_ID_WERKSTOFF = 11 OR F_ID_WERKSTOFF = 245
> PLAN JOIN (T_AUFTRAG NATURAL,T_APOS INDEX

(RDB$FOREIGN1,RDB$FOREIGN161,RDB$FOREIGN161))

> RDB$FOREIGN161 = F_ID_WERKSTOFF
> RDB$FOREIGN1 = F_ID_AUFTRAG
>
> Does anyone has an explanation. This can't be normal.
> Same effect happens for every select with or clause. (see previous post:

Perfomance differences)

Did you read the replies?

The optimizer chooses indexes based on selectivity. For a OR condition every
comparison inside that should be able to use a index or none index (for those
comparisons inside the OR) is used at all. I guess your problem here is the
foreign-key on F_ID_WERKSTOFF, because this one has many duplicates?
If so the optimizer still doesn't see it as bad enough to ignore.

One of the things you can do is changing the comparison, such as :

SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON f_id_auftrag = id_auftrag
WHERE
F_ID_WERKSTOFF + 0 = 11 OR
F_ID_WERKSTOFF + 0 = 245

Another option is if f_id_auftrag and f_id_werkstoff are together very selective
and create a compound index on (f_id_auftrag, F_ID_WERKSTOFF). But to let the
optimizer choose the correct compound index you should change your query to :

SELECT
t_apos.ID_APOS
FROM
t_auftrag
JOIN t_apos ON (
(f_id_auftrag = id_auftrag and F_ID_WERKSTOFF = 11) OR
(f_id_auftrag = id_auftrag and F_ID_WERKSTOFF = 245))

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