I've add some explanations to the indices used.
And I have another effect i cannot explain myself.

"Carsten Schäfer" wrote:

> Hi,
> I'm using Firebird 1.5.2 RC3.
> I have 2 tables (definitions at the end of this message):
> the following select is fast: (1 sec)
> SELECT t_apos.ID_APOS,
> t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
> FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
> (t_apos.f_status = 13 AND t_auftrag.f_adatum between '27.08.2004
> 00:00' AND '27.11.2004 11:23' ) AND id_apos != 0 Plan: PLAN JOIN
> (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX (RDB$FOREIGN1))
>

RDB$FOREIGN1 = F_ID_AUFTRAG

> and this is slow: (>191 sec)
> SELECT t_apos.ID_APOS,
> t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
> FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
> (t_apos.f_status = 13 AND t_auftrag.f_adatum between '27.08.2004
> 00:00' AND '27.11.2004 11:23' ) AND id_apos > 0 Plan:PLAN JOIN
> (T_AUFTRAG INDEX (IND_AUFTRAG_ADATUM),T_APOS INDEX
> (RDB$FOREIGN1,RDB$PRIMARY44))
>

RDB$PRIMARY44=ID_APOS

> Only difference is id_apos != 0 and id_apos >0
> Interbase Plananalyzer tells me that it needs > 190 seconds to fetch
> the values (about 15.000)
>
> Can someone explain why this big difference happens ?
>
> Another difference i don't understand:
> This one
> SELECT t_apos.ID_APOS,
> t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
> FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
> t_auftrag.f_id_kunde = 10 AND id_apos != 0
> PLAN:PLAN JOIN (T_AUFTRAG INDEX (RDB$FOREIGN146),T_APOS INDEX
> (RDB$FOREIGN1))
>

RDB$FOREIGN146 =F_ID_KUNDE

> is always faster than
> SELECT t_apos.ID_APOS,
> t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
> FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE
> (t_auftrag.f_id_kunde = 10 AND t_auftrag.f_adatum between '27.08.2004
> 00:00' AND '27.11.2004 11:23' ) AND id_apos != 0 PLAN: PLAN JOIN
> (T_AUFTRAG INDEX (RDB$FOREIGN146,IND_AUFTRAG_ADATUM),T_APOS INDEX
> (RDB$FOREIGN1))
> which takes more time when the difference in the dates is growing.
>
> The first one is faster although there are more values to fetch.
> Can someone please explain this ?
>

Another one:
This is fast:
SELECT DISTINCT t_apos.ID_APOS,
t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE f_id_kunde
=10
Plan: PLAN SORT (JOIN (T_AUFTRAG INDEX (RDB$FOREIGN146),T_APOS INDEX
(RDB$FOREIGN1)))
This is also fast:
SELECT DISTINCT t_apos.ID_APOS,
t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE id_apos
=10000
Plan: PLAN SORT (JOIN (T_APOS INDEX (RDB$PRIMARY44),T_AUFTRAG INDEX
(RDB$PRIMARY39)))
RDB$PRIMARY39 = id_auftrag

But the combination is slow (about 7 sec) (each alone is under 50 ms)
SELECT DISTINCT t_apos.ID_APOS,
t_apos.f_liefer_nr,t_apos.f_status,t_apos.f_id_auftrag,t_auftrag.f_id_kunde
FROM t_apos JOIN t_auftrag ON f_id_auftrag = id_auftrag WHERE f_id_kunde
=10 or id_apos = 10000
Plan: PLAN SORT (JOIN (T_AUFTRAG NATURAL,T_APOS INDEX (RDB$FOREIGN1)))

Is this normal ?

mfg
Carsten