> I have created a btree index on a 'int4' attribute of a table.
>
> After i have inserted 1,000,000 raws in my table, i can see that my index
> size is 2745 Blocks (8KB each) from pg_class. That means about 21,960 KB
> size.
>
> I try to understand hows is this number generated, because thought that
> for each new entry in table, there is a new entry in index and that each
> entry of the index is:
>
> 4 Bytes for the int4 attribute
> and
> 40 Bytes for oid
>
> So 44 * 1,000,000 ~ 42,969 KB
>
> Can anybody inform me where I do the mistake?
There's no oid in index tuples. There is an 8-byte long header for
each index tuple. Since you are inserting 4-byte long user data, you
index tuples are 12-byte each. Each index tuple needs a "pointer" in a
block, which is called "item pointer" and that is 4-byte long. Each
block can hold up to floor((8192-24(page header)-16(special
data))/(12+4)) = 509 tuples. ceil(1,000,000/509) = 1965 is the blocks
you need for your index. In addition to this, you need a "meta page"
and a "root page". So it becomes 1965+1+1 = 1967. Also you need
"internal pages", whose numer is hard to guess since it depends on the
actual index tree structure(for example, tree height). From my limited
experience, for 1,000,000 tuples, you will need at least 7 internal
pages. Now the number becomes 1967+7 = 1974. Still it's different from
2745. If you don't have deleted tuples, the difference probably comes
from the fact that a btree index can never be 100% occupied. IMO
1974/2745 = 0.71 seems not so bad.
--
Tatsuo Ishii
