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.

Interesting. Right after CREATE INDEX for a int4 column using pgbench
-s 10(1,000,000 tuples), I got 2184 leaf pages. From my caliculation
the number of leaf pages is expected to 1965, which is 100% full case
assumption of course. So 1965/2184 = 0.8997 = 90% is actually used?
--
Tatsuo Ishii