maxirobaina wrote:
Anybody can help me and explain me how read this index statistic ?
Ann W. Harrison answers:
Index RDB$FOREIGN125 (1)
The name of the index is RDB$FOREIGN125. From the name, I infer that the index was created automatically as part of a foreign key declaration in which this table is the child (aka referencing table). This is the second index defined for the table - the number in parentheses is the index id, and the ids start at 0.
Depth: 3, leaf buckets: 1799, nodes: 1185722
The index has three levels, which is good - a depth more than three suggests that increasing the database page size may improve performance. The width of the index at its bottom level is 1799 pages. There are 1185722 entries in the index. Sometimes you'll see more index entries than there are records in the table. That's an effect of multi-generational indexes - if an indexed fields is updated, a single record will have two (or more) index entries.
Average data length: 0.00, total dup: 1184098, max dup: 82105
Average data length of 0.00 is good and bad. The data length is the amount of space taken in the index to represent index key values. A zero average length means that prefix and suffix compression are being used heavily so the index is very dense. However, prefix compression is most successful when the index contains lots of duplicates. In this case, there are only 1624 distinct values among of the 1185722 entries. So, on average, there are 730 instances of each value. That's not great. What's worse is that the distribution is uneven, as shown by the max dup: 82105. The most heavily duplicated value has has eight-two thousand, one hounded and five instances.
My rule of thumb is that more than ten thousand duplicates on any single value will cause garbage collection problems. Version 2 has a new index structure that eliminates the problem. Were it me, I'd eliminate the constraint for now.
Fill distribution:
0 - 19% = 0
None of the index pages is less than 20% full
20 - 39% = 1
One index page is less than 40% full, probably the top of the index.
40 - 59% = 75
Seventy-five pages are between 40 and 59% full - that's not a problem and may suggest that the actual key is fairly large - upper levels are less susceptible to prefix compression, and a large key reduces the packing density of index entries... so those 75 pages might be level 2 of the index. Or they may be pages that split recently.
60 - 79% = 5
Five pages are between 60 and 79% full - here I would suspect splits in the leaf level rather than lack of compression.
80 - 99% = 1718
The vast majority of pages - 1718 out of 1799 - are over 79% full. That's pretty good. But it's good because prefix compression is working well, and prefix compression is working because of the large number of duplicates, which is bad.