Re: Equivalent praxis to CLUSTERED INDEX? - Mailing list pgsql-performance

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