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 0AC3053975 for ; Thu, 21 Apr 2005 15:24:26 -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 58504-02 for ; Thu, 21 Apr 2005 18:24:23 +0000 (GMT) Received: from unicorn.rentec.com (unicorn.rentec.com [216.223.240.9]) by svr1.postgresql.org (Postfix) with ESMTP id DF97453F7F for ; Thu, 21 Apr 2005 15:24:18 -0300 (ADT) Received: from ram.rentec.com (mailhost [192.5.35.66]) by unicorn.rentec.com (8.13.1/8.12.1) with ESMTP id j3LIOCDV008637 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 21 Apr 2005 14:24:14 -0400 (EDT) X-Rentec: external Received: from [192.168.96.111] (bottlenose.rentec.com [192.168.96.111]) by ram.rentec.com (8.13.1/8.12.1) with ESMTP id j3LIOC5S004496; Thu, 21 Apr 2005 14:24:12 -0400 (EDT) Message-ID: <4267EFF9.2000908@rentec.com> Date: Thu, 21 Apr 2005 14:24:57 -0400 From: Michael Guerin User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3) Gecko/20040910 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Josh Berkus Cc: Bill Chandler , pgsql-perform Subject: Re: Index bloat problem? References: <20050421173855.76286.qmail@web51410.mail.yahoo.com> <200504211044.48794.josh@agliodbs.com> In-Reply-To: <200504211044.48794.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Logged: Logged by unicorn.rentec.com as j3LIOCDV008637 at Thu Apr 21 14:24:14 2005 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/583 X-Sequence-Number: 11980 >>Is >>this a common issue among all RDBMSs or is it >>something that is PostgreSQL specific? >> >> > >Speaking from experience, this sort of thing affects MSSQL as well, although >the maintenance routines are different. > > > Yes, this is true with MSSQL too, however sql server implements a defrag index that doesn't lock up the table.. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_30o9.asp "DBCC INDEXDEFRAG can defragment clustered and nonclustered indexes on tables and views. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance. ....Every five minutes, DBCC INDEXDEFRAG will report to the user an estimated percentage completed. DBCC INDEXDEFRAG can be terminated at any point in the process, and *any completed work is retained.*" -michael