Re: Estimating space required for indexes

Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
>> But this is not the whole story because heap pages are normally crammed
>> full while btree index pages are normally only filled 2/3rds full during
>> initial creation. (Plus you have to allow for upper b-tree levels, but

> Are there any benefits from getting btree index pages to be more effective
> in space usage ? I've read some paper about 98% space usage for Btree.

Standard theory says that optimal load for a b-tree is 65-70%. We used
to make CREATE INDEX cram the leaf pages full, but that just resulted in
a lot of page splits as soon as you did any inserts or updates. And the
page splits destroy the physical ordering of the index, which negates
any I/O savings you might have had from fewer pages.

I suppose if you know that the table will be static there might be some
value in telling CREATE INDEX to pack the index pages full, but I'm not
sure it's worth the trouble ...

regards, tom lane