Search Postgresql Archives

Re: Index size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tatsuo Ishii <t-ishii@xxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux