#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    2
    Rep Power
    0

    Question about bad indices


    Hello,

    My program does a query like that:
    Code:
    select count(*) from t_person p join t_employer e on (p.f_employer_uoid=e.uoid) where e.f_reference='X';
    There's an index on f_employer_uoid in table t_person.

    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.)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    2
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    19
    Rep Power
    0
    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)

IMN logo majestic logo threadwatch logo seochat tools logo