Re: Index size

> > 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;
>

Thanks for the explanation.

So it seems Ioannis' number was not taken immediately after a CREATE
INDEX operation?
--
Tatsuo Ishii