February 14th, 2014, 11:37 AM
Question about bad indices
My program does a query like that:
There's an index on f_employer_uoid in table t_person.
select count(*) from t_person p join t_employer e on (p.f_employer_uoid=e.uoid) where e.f_reference='X';
The program runs in more than one place - sometimes there is not a single record in t_person with f_employer_uoid set. (All the records have f_employer_uoid null and IBAnalyst reports it as a bad index). In such case, on some systems Firebird ignores the index (and the query is very slow), on other systems the index is used. Even on systems with same version of Firebird, the behaviour is different.
Is there a way to influence this behaviour (always have the indices used)? (Preferably without setting a plan explicitely.)
February 17th, 2014, 09:18 AM
The problem have 2 parts.
First, the indexes work with data. NULL is not a data value, it shows that there isn't any data in this cell. Firebird use the index to find fast data values. When there isn't any data it's like comparing data with nothing.
The second part is that every index in Firebird has Index selectivity value and Firebird use it to determine which index to use in a query. Even if you have many records, if the index selectivity is 0 Firebird may generate a plan without that index. The index selectivity is calculated when the index is created, on database restore and manually with SET STATISTICS INDEX statement. If the selectivity of your indexes is 0 this may cause some strange plans when executing queries.
February 17th, 2014, 10:47 AM
Thank you for the answer.
I do understand the stuff about index selectivity but I have the impression that Firebird behaves inconsistently.
I run the same query on 2 databases. In both databases the statistics for indices are recalculated periodically, and that particular index has the worst possible selectivity (RDB$STATISTICS=1). I run the same query with both databases. In one case, the plan shows that the index is used, in the other case it's not used.
Another option you could use is to override the plan to use your desired index.
An example of how to do that is below :
select * from EMPLOYEE
where EMP_NO = 5
PLAN (EMPLOYEE NATURAL)