Hi,

> We are using Firebird 1.0.0 and we observe with IbPLANAnalyser that in
> queries with many joins and on tables that have primary key, unique key
> and foreign keys defined in order to force relational integrity,
> the optimiser consistently is choosing wrong PLAN's
> PLAN's that make use of indexes of unique constraints or foreign keys
> and have a bad performance

It really depends on the tables/fields and different indices that are
available.
It's not always bad, thus show us information about the query.

> I read somewhere in a newsgroup that Interbase was supposed not to
> make use of indexes that have low selectivity.

The index with the best selectivity is choosen above a worser one, but if
only 1 is available and it can be used the it is used.

> Apparently this is not true for Firebird 1.00

Firebird 1.0 has the same optimizer as Interbase.

> Is this a bug ? Are there versions of Interbase or Firebird that do
> not have this behaviour ?

There're bugs in the optimizer (some call it features, but i don't), but
there are already a lot of them solved in Firebird 1.5. Thus i would say
give FB1.5 a try and if it still does a bad job post the query with
necessary information (index-selectivity / DDL).

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81