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

Re: Equivalent praxis to CLUSTERED INDEX?

From: Gaetano Mendola <mendola(at)bigfoot(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: 2004-08-27 08:26:26
Message-ID: cgmr7s$59d$ (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.

Gaetano Mendola

In response to


pgsql-performance by date

Next:From: Russell SmithDate: 2004-08-27 08:48:08
Subject: Re: Query performance issue with 8.0.0beta1
Previous:From: Stefano BonninDate: 2004-08-27 06:57:43
Subject: Query performance issue with 8.0.0beta1

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