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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
