Subject Re: Firebird 2.1.3 optimizer in does not use index
Author Andreas Rulle
Hello Alexendre!

Thank you for your interest into this point.

> I don't know what you did to upgrade from 1.X to 2.X

We migrate from 1.0.3 to 2.1.3 by making a backup with a 1.0.3 Firebird-Server and performing a restore with a 2.1.3 Firebird-Server.

> Could you please show us the ODS version ?

The ODS version of the database file is 11.1

> Did you refresh the indices statistics ?

Yes, we updated them and they a have high overall selectivity in the table RDB$INDICES as it is indicated by the value 0.00001 in the column RDB$STATISTICS

But, the tables have been designed for a software product line with serveral catalogs and data languages in one database file and the concrete project has only one catalog and one language per database file.

And therefore in the table INDEX_SEGMENTS the FIELD_NAMEs with FIELD_POSITIONs 1 and 2 of the most original indices have an RDB_STATISTICS value of 1.


* (C1) field types are equal and
* (C2) the fields with the low selectivity 1 are not part of a new index

the new index has been chosen by the Firebird 2.1.3 optimizer.

If only one of the two conditions (C1) and (C2) is true then in our tests the opitimizer has not choosen the index.