Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 828859FB289 for ; Sat, 14 Apr 2007 05:31:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 94622-03 for ; Sat, 14 Apr 2007 05:31:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (webmail.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id ACDA49FB1DD for ; Sat, 14 Apr 2007 05:31:34 -0300 (ADT) thread-index: Acd+b1hD5jwvdMsWTMq/Ssop93TnKA== Received: from [192.168.0.22] ([85.211.22.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Sat, 14 Apr 2007 04:31:48 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 Subject: Re: Finding bloated indexes? From: "Simon Riggs" To: "Dan Harris" Cc: "PostgreSQL Performance" In-Reply-To: <461FE1A4.70908@drivefaster.net> References: <461FE1A4.70908@drivefaster.net> Content-Type: text/plain; charset="iso-8859-1" Date: Sat, 14 Apr 2007 09:27:25 +0100 Message-ID: <1176539246.3635.317.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 14 Apr 2007 08:31:48.0937 (UTC) FILETIME=[58302B90:01C77E6F] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/303 X-Sequence-Number: 24180 On Fri, 2007-04-13 at 14:01 -0600, Dan Harris wrote: > Is there a pg_stat_* table or the like that will show how bloated an index is? > I am trying to squeeze some disk space and want to track down where the worst > offenders are before performing a global REINDEX on all tables, as the database > is rougly 400GB on disk and this takes a very long time to run. > > I have been able to do this with tables, using a helpful view posted to this > list a few months back, but I'm not sure if I can get the same results on indexes. Use pgstatindex in contrib/pgstattuple -- Simon Riggs EnterpriseDB http://www.enterprisedb.com