Subject Re: Firebird 2.1.3 optimizer does not use index
Author Andreas Rulle
Hello Ismael,

yes, you are right the different data types make the point.

Obviously older versions of the optimizer have been more tolerant according to type differences.

If the datatypes of the attributes are equal then the optimizer in Firebird 2.1.3 does use the same indices as it has done in the older Firebird versions:

If s1 is of type Integer as in

create table T2 (s1 Integer, s2 varchar(50));

then the left join

select t1.s1 from t1 left join t2 on t1.s1=t2.s1

uses the index

PLAN JOIN (T1 NATURAL, T2 INDEX (IDX_T12))

in opposite to

PLAN JOIN (T1 NATURAL,T2 NATURAL) with data type string for T2.s1.

And the statement on the productive tables now takes 0.61 s!

Greetings

Andreas