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

Although I do not see a good design using a field integer and in the other varchar

Greetings
=========
|| ISMAEL ||
=========

----- Original Message -----
From: Andreas Rulle
To: firebird-support@yahoogroups.com
Sent: Thursday, July 15, 2010 7:12 AM
Subject: [firebird-support] Firebird 2.1.3 optimizer in does not use index



Hello,

during a migration from Firebird 1.0.3 to Firebird 2.1.3 we need some help in
optimizing a left join statement.

* On Firebird 1.0.3 the statement takes 1.3 s
* and on Firebird 2.1.3 we have to wait for more than one hour.

The tables and indices and statements (except from the migration) are unchanged.

The tables that are joined contain about 65000 and 110000 rows. The selectivity
of the indices is good: 1/number rows.

A simplified version of the problem is:

a) The tables and their data:

create table T1 (s1 integer, s2 varchar(50));

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

insert into T1 values(1,'Wert 1');
insert into T1 values(2,'Wert 2');
insert into T1 values(3,'Wert 3');

insert into T2 values('1','Wert 1');

create unique index IDX_T11 on T1 (S1);
create unique index IDX_T12 on T2 (S2);

b) Sample queries and their plans:

This query (extremely simplified) uses no Index:

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

PLAN JOIN (T1 NATURAL,T2 NATURAL)

If the logic of the join (which will deliver of course the wrong result) is changed,
the index ist used:

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

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

Analog results are observed for the statements on the original tables, the second
statement is fast, the first takes more than an hour.

We tried to specify an explicite execution plan, but the indices have not been taken.

Question: What can be done to optimize the statement on Firebird 2.1.3?

Greetings

Andreas





[Non-text portions of this message have been removed]