Subject | Re: Firebird 2.1.3 optimizer does not use index |
---|---|
Author | Andreas Rulle |
Post date | 2010-07-15T16:40:22Z |
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
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