From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Gaetano Mendola <mendola@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: Fri, 27 Aug 2004 12:23:57 -0400 (EDT)
Message-ID: <200408271623.i7RGNvK05464@candle.pha.pa.us> (raw)
In-Reply-To: <cgmr7s$59d$1@floppy.pyrenet.fr>
I had FILLFACTOR in the TODO list until just a few months ago, but
because no one had discussed it in 3-4 years, I removed the item. I
have added mention now in the auto-cluster section because that actually
seems like the only good reason for a non-100% fillfactor. I don't
think our ordinary btrees have enough of a penalty for splits to make a
non-full fillfactor worthwhile, but having a non-full fillfactor for
autocluster controls how often items have to be shifted around.
---------------------------------------------------------------------------
Gaetano Mendola wrote:
> Greg Stark wrote:
>
> > The discussions before talked about a mechanism to try to place new
> > tuples as close as possible to the proper index position.
>
> Means this that an index shall have a "fill factor" property, similar to
> Informix one ?
>
> From the manual:
>
>
> The FILLFACTOR option takes effect only when you build an index on a table
> that contains more than 5,000 rows and uses more than 100 table pages, when
> you create an index on a fragmented table, or when you create a fragmented
> index on a nonfragmented table.
> Use the FILLFACTOR option to provide for expansion of an index at a later
> date or to create compacted indexes.
> When the index is created, the database server initially fills only that
> percentage of the nodes specified with the FILLFACTOR value.
>
> # Providing a Low Percentage Value
> If you provide a low percentage value, such as 50, you allow room for growth
> in your index. The nodes of the index initially fill to a certain percentage and
> contain space for inserts. The amount of available space depends on the
> number of keys in each page as well as the percentage value.
> For example, with a 50-percent FILLFACTOR value, the page would be half
> full and could accommodate doubling in size. A low percentage value can
> result in faster inserts and can be used for indexes that you expect to grow.
>
>
> # Providing a High Percentage Value
> If you provide a high percentage value, such as 99, your indexes are
> compacted, and any new index inserts result in splitting nodes. The
> maximum density is achieved with 100 percent. With a 100-percent
> FILLFACTOR value, the index has no room available for growth; any
> additions to the index result in splitting the nodes.
> A 99-percent FILLFACTOR value allows room for at least one insertion per
> node. A high percentage value can result in faster selects and can be used for
> indexes that you do not expect to grow or for mostly read-only indexes.
>
>
>
>
> Regards
> Gaetano Mendola
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: pgman@candle.pha.pa.us, mendola@bigfoot.com
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
In-Reply-To: <200408271623.i7RGNvK05464@candle.pha.pa.us>
References: <cgmr7s$59d$1@floppy.pyrenet.fr> <200408271623.i7RGNvK05464@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox