Subject Re: [firebird-support] Re: High CPU consumption
Author Ann W. Harrison
Alexander Tabakov wrote:
> So, following your directions I did a gstat and observed results. Now
> I have 2 questions:
> 1. Most of my indexes with "max versions" > 30000 were used in foreign keys. So,
> what is your recommendation in that case? Maybe I should use some kind of
> trigger based referential integrity?

Ah yes. I should have mentioned that but was a bit short of time. Most
of the really bad indexes are foreign key indexes, which is why the
change in V2 is so very valuable. Until then, my best suggestion is to
use triggers to check referential integrity. It's marginally less
reliable than constraint based checking, but doesn't have the
performance problems.

> 2. I found an index with very good selectivity (0,0000063...), but
> which contains about 35K records with null value. So statistics
> shows "max versions = 35K". Is this a problem?

Could be. If records are normally stored with the field null then
modified later to a real value, that modification will cause the null
index entry to be garbage collected eventually, at considerable cost.
I'd make a compound key for that index, trying to remember to simplify
it later when the new index format is available.