Hi all,

I'm learning how IB optimizer actually works. I noticed that some join
queries where the plan shows it could be satisfied by 2 or more indexes on
one of the table will perform badly, unless I override the plan to use
only 1 of the index or adding some criteria in order to avoid some index
from being used.
But some other queries where the plan also shows it could be satisfied by
2 or more indexes will perform well.
I tested this in my apps, joining 2-3 tables (each has 100-200 thousand
rows), I have appropriate indexes which should satisfy the query.

Just for additional info, here's a sample of my query:
1. select ... from table1, table2
where table1.id = table2.id
and <some criterias which I have indexes on>
and table1.ref = 'FOO';

2. select ... from table1, table2
where table1.id = table2.id
and <some criterias which I have indexes on>
and table1.ref in ('FOO', 'BAR', '...', '...', '...')

The differences is that I specify more values for table1.ref. And the plan
shows table1(rdb$foreign1, rdb$foreign1, rdb$foreign1....) rdb$foreign1 is
foreign key on table1.ref. The 1st query is very fast, but the 2nd takes
unexpectedly longer.

My question is, is IB optimizer using rule-based or cost-based optimizer?
I guess it looks like rule-based, because the plan shows all indexes. But
from my experience above, it also looks like cost-based, as sometimes it
seems to be able to pick the right index. But if it's cost-based, where's
the statistics? And if it's rule-based, what's the command SET STATISTICS
INDEX for? When should I perform SET STATISTICS INDEX? Periodically?

I'd appreciate if anyone would point me to some resource about IB optimizer?

TIA and best regards,
Sindu