pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: Fri, 27 Aug 2004 10:26:26 +0200
Message-ID: <cgmr7s$59d$1@floppy.pyrenet.fr> (raw)
In-Reply-To: <87pt5dnta9.fsf@stark.xeocode.com>
References: <200408270145.i7R1jop04185@candle.pha.pa.us>
	<87pt5dnta9.fsf@stark.xeocode.com>

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







view thread (41+ messages)  latest in thread

Message-ID: <cgmr7s$59d$1@floppy.pyrenet.fr>
Permalink:  ../cgmr7s$59d$1@floppy.pyrenet.fr/
Also on:    postgresql.org/message-id/cgmr7s$59d$1@floppy.pyrenet.fr

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com
  Subject: Re: Equivalent praxis to CLUSTERED INDEX?
  In-Reply-To: <cgmr7s$59d$1@floppy.pyrenet.fr>

* 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