Re: Index size

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> ... 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.

In fact the traditional figure for the steady-state load factor of a
btree index is 2/3rds; that is, after a long sequence of inserts and
deletes you can expect about one-third of each page to be empty space.

If Ioannis' number was taken immediately after a CREATE INDEX operation,
then his index size isn't reflective of any settling to a steady-state
load factor; rather it happens because the CREATE INDEX command
deliberately loads the index leaf pages only 2/3rds full, to avoid a
disproportionate amount of page splitting when normal inserts commence.

regards, tom lane