X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AD01B5E46F5 for ; Fri, 27 Aug 2004 05:32:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09725-06 for ; Fri, 27 Aug 2004 08:32:01 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 407E55E46F3 for ; Fri, 27 Aug 2004 05:31:59 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i7R8VuLq012214 for ; Fri, 27 Aug 2004 08:31:56 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i7R8Qa4J010565 for pgsql-performance@postgresql.org; Fri, 27 Aug 2004 08:26:36 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Equivalent praxis to CLUSTERED INDEX? Date: Fri, 27 Aug 2004 10:26:26 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 49 Message-ID: References: <200408270145.i7R1jop04185@candle.pha.pa.us> <87pt5dnta9.fsf@stark.xeocode.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: en-us, en In-Reply-To: <87pt5dnta9.fsf@stark.xeocode.com> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/386 X-Sequence-Number: 8022 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