Dimitry Sibiryakov wrote:

>> No - with multi-column indexes resetting the selectivity is a
>> good idea. And Dmitry S says that it can also affect single
>> key primary and unique indexes ...
>
> No, actually - no. When you said it, I realized that in the case I
> mentioned, primary key was multi-columned and that might make the
> difference.

OK, we're verging on agreement here. Generally, the Firebird
optimizer is cost-based. It calculates the number of pages it
will need to read to resolve a query using an estimate of the
total number of pages in the table and the selectivity of the
index. The selectivity is stored in the index metadata and
is updated when a index is created, reactivated, or when its
selectivity is set explicitly. There is a special case for
indexes that require uniqueness - their selectivity is known
in advance and doesn't need to be re-evaluated.

When you select on the leading part of a multi-column unique
index, Firebird uses the selectivity for that part of the
index. If that's what you're doing, you do need to set the
selectivity for that index because, in your particular usage,
it's not acting as a unique index.

Clear?

Ann