Subject | Index usage / selectivity for all NULL |
---|---|
Author | tdtappe |
Post date | 2009-10-22T09:58:55Z |
I just noticed the following problem (FB 2.1, but I think it's the same with FB 2.0):
If I put an index on a column but this column doesn't (yet) have any values there's poor performance when I try to find a record with a specific value in this column. The statistics show a value of 1 for this index.
If I then update or insert a record with a non-null value for this column and recompute the index statistics the performance is just fine. The statistics then show a value of 0.5 for this index.
Even after resetting the value to NULL and recomputing index selectivity the performance is still fine (showing still a value of 0.5).
But after a backup/restore of the database the performance is bad again.
Is this an expected behaviour? And is there anything I can do about this?
--Heiko
If I put an index on a column but this column doesn't (yet) have any values there's poor performance when I try to find a record with a specific value in this column. The statistics show a value of 1 for this index.
If I then update or insert a record with a non-null value for this column and recompute the index statistics the performance is just fine. The statistics then show a value of 0.5 for this index.
Even after resetting the value to NULL and recomputing index selectivity the performance is still fine (showing still a value of 0.5).
But after a backup/restore of the database the performance is bad again.
Is this an expected behaviour? And is there anything I can do about this?
--Heiko