Skip site navigation (1) Skip section navigation (2)

Re: Equivalent praxis to CLUSTERED INDEX?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Gaetano Mendola <mendola(at)bigfoot(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: 2004-08-27 16:23:57
Message-ID: 200408271623.i7RGNvK05464@candle.pha.pa.us (view raw or flat)
Thread:
Lists: 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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-08-27 16:31:06
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Previous:From: Russell SmithDate: 2004-08-27 08:48:08
Subject: Re: Query performance issue with 8.0.0beta1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group