Re: Index size - Mailing list pgsql-general

From Tom Lane
Subject Re: Index size
Date
Msg-id 19187.1109701328@sss.pgh.pa.us
Whole thread Raw
In response to Re: Index size  (Tatsuo Ishii <t-ishii@sra.co.jp>)
Responses Re: Index size  (Tatsuo Ishii <t-ishii@sra.co.jp>)
List pgsql-general
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> ... Now the number becomes 1967+7 = 1974. Still it's different from
> 2745. If you don't have deleted tuples, the difference probably comes
> from the fact that a btree index can never be 100% occupied. IMO
> 1974/2745 = 0.71 seems not so bad.

In fact the traditional figure for the steady-state load factor of a
btree index is 2/3rds; that is, after a long sequence of inserts and
deletes you can expect about one-third of each page to be empty space.

If Ioannis' number was taken immediately after a CREATE INDEX operation,
then his index size isn't reflective of any settling to a steady-state
load factor; 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.

            regards, tom lane

pgsql-general by date:

Previous
From: George Essig
Date:
Subject: Re: Backupping the table values
Next
From: josue
Date:
Subject: Re: row numbering