Optimizer improvement for InterBase 7.5.0

Abstract: Optimizer improvement for InterBase 7.5.0

Prior to InterBase 7.5.0, the InterBase optimizer always considered foreign key indexes to have higher selectivity than user indexes. Under some rare conditions, this lead to some inefficent query plans generated by the optimizer where a foreign key was used and a user index would be far more efficent. As of InterBase 7.5.0, the optimizer now considers the selectivity of all possible indexes, both system and user created, and selects the index with the highest selectivity.

The end result of this change is that if you have a foreign key with low selectivity, create a user index that is the same as the foreign key plus one additional field and the index for the foreign key will never be used. For example, if you have a single field foreign key, create an index on this field but plus one other field (which hopefully has high selectivity) and the foreign key will never used in query plans - unless you specify a custom query plan that uses it.