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 10732539C5 for ; Thu, 21 Apr 2005 15:04:10 -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 53365-06 for ; Thu, 21 Apr 2005 18:04:06 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9965753F7A for ; Thu, 21 Apr 2005 15:04:04 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 38C4930952; Thu, 21 Apr 2005 20:02:52 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.performance Subject: Re: Index bloat problem? Date: Thu, 21 Apr 2005 13:47:24 -0400 Organization: cbbrowne Computing Inc Lines: 29 Message-ID: <60fyxk2f9v.fsf@dba2.int.libertyrms.com> References: <20050421170018.2998.qmail@web51403.mail.yahoo.com> <200504211022.03541.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:DUEbIw+FkfCjKIZfYCUSsTJX1XI= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.159 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/582 X-Sequence-Number: 11979 josh@agliodbs.com (Josh Berkus) writes: > Bill, > >> What about if an out-of-the-ordinary number of rows >> were deleted (say 75% of rows in the table, as opposed >> to normal 5%) followed by a 'VACUUM ANALYZE'?  Could >> things get out of whack because of that situation? > > Yes. You'd want to run REINDEX after and event like that. As you should now. Based on Tom's recent comments, I'd be inclined to handle this via doing a CLUSTER, which has the "triple heroism effect" of: a) Reorganizing the entire table to conform with the relevant index order, b) Having the effect of VACUUM FULL, and c) Having the effect of REINDEX all in one command. It has all of the "oops, that blocked me for 20 minutes" effect of REINDEX and VACUUM FULL, but at least it doesn't have the effect twice... -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"