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 3E3E8539BA for ; Thu, 21 Apr 2005 15:04:09 -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 53490-03 for ; Thu, 21 Apr 2005 18:04:06 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 99BC853F7B for ; Thu, 21 Apr 2005 15:04:04 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 40C7F30953; Thu, 21 Apr 2005 20:02:53 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.performance Subject: Re: How can an index be larger than a table Date: Thu, 21 Apr 2005 13:51:18 -0400 Organization: cbbrowne Computing Inc Lines: 27 Message-ID: <60br882f3d.fsf@dba2.int.libertyrms.com> References: <1114078390.26441.232391808@webmail.messagingengine.com> <200504211031.51567.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:xIor25Dezx9UFGZNhdWxGiv7nNM= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.162 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/581 X-Sequence-Number: 11978 josh@agliodbs.com (Josh Berkus) writes: > David, > >> What also seems weird to me is that the control table has some unique >> indexes created on it, but the data_upate_events table just has a unique >> constraint.  Will postgres use an index in the background to enforce >> this constraint? > > If you somehow have a unique constraint without a unique index, something is > seriously broken. I suspect hacking of system tables. > > Otherwise, it sounds like you have index bloat due to mass deletions. Run > REINDEX, or, preferably, VACUUM FULL and then REINDEX. There is in a sense no "best order" for this; VACUUM FULL will wind up further injuring the indices when it reorganizes the table, which means that whether you do it first or last, you'll have "index injury." This actually seems a plausible case for CLUSTER. (And as Douglas & Douglas says, "You can become a hero by using CLUSTER." :-)) -- (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.''"