Re: Index size

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

Shoulda read the code rather than going by memory ;-). What nbtsort.c
actually says is

* It is not wise to pack the pages entirely full, since then *any*
* insertion would cause a split (and not only of the leaf page; the need
* for a split would cascade right up the tree). The steady-state load
* factor for btrees is usually estimated at 70%. We choose to pack leaf
* pages to 90% and upper pages to 70%. This gives us reasonable density
* (there aren't many upper pages if the keys are reasonable-size) without
* incurring a lot of cascading splits during early insertions.

and indeed the code seems to do that:

/* set "full" threshold based on level. See notes at head of file. */
if (level > 0)
state->btps_full = (PageGetPageSize(state->btps_page) * 3) / 10;
else
state->btps_full = PageGetPageSize(state->btps_page) / 10;

regards, tom lane