Subject Re: [firebird-support] Index Memory Usage
Author Ann Harrison

On Mar 17, 2015, at 1:55 AM, Ekundayo edayo_a@... [firebird-support] <> wrote:

Hi All, can anyone tell me (or point me in the direction of manuals) how i can calculate the amount of memory used by the indexes of a particular table?

Probably not.  The challenge is that Firebird uses prefix compression on indexes, so after data in the columns in the index have been transformed to compare correctly, bytewise, respecting column boundaries, most index entries have their first bytes truncated to eliminate those bytes that duplicate the previous entry.  So 0x0 followed by ABC1234 followed by ABC1235 would show up in the index as 0x0, ABC12345, x065.  The last entry is a binary 6 indicating that the first 6 characters are skipped, then a 5.  The space used by indexes is data dependent.

I have a table in which I store sha1 hashes (currently about 2 million records) as char(40) and its indexed, I'm wondering if the table would consume less memory if the hashes are stored as BIGINT

Build your indexes both ways and use gstat to estimate their usage.  My gues is that BigInt wins, but that's a guess.\

Good luck,