What is index selectivity?

Index selectivity is a number the determines the effectiveness of index. Best possible selectivity is when all table records have a different value for the columns in index (this is typical for primary keys and unique constraints). Selectivity is quantified by index 'statistics' which is computed like this:

select count(*)/count(distinct(index_field)) from table;

Please note that this query would fail if the table has zero rows (due to division by zero). To prevent this, you can use NULLIF function:

select count(*)/nullif(count(distinct(index_field)), 0) from table;

As you can see, higher statistics mean that there aren't many different values in the table column. If you have a Y/N or true/false field, it would have statistics value of 0.5 which is worst possible selectivity. As statistics approach zero, index gets more effective.

Index statistics can be read from RDB$INDICES system table. Statistics range from zero (the best selectivity) to 0.5 (the worst) with a special value of 1 (one) which means that all table rows have the same value for index column. Having index on such field is completely useless. You would never get zero selectivity, the lowest value is actually: 1/number of table records.

Index selectivity should be recalculated periodically because inserting, updating and deleting records changes it. Firebird calculates it only when index is created and when database is restored from backup. So make sure you recompute statistics after big changes in data. It can be done by running:

SET STATISTICS INDEX index_name;

Using EXECUTE BLOCK in Firebird 2 or higher you can recalculate statistics for all indexes using this simple SQL statement:

set term !! ;

EXECUTE BLOCK AS
declare variable index_name VARCHAR(31);
BEGIN
for select RDB$INDEX_NAME from RDB$INDICES into :index_name do
execute statement 'SET statistics INDEX ' || :index_name || ';';
END!!

set term ; !!