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