Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 409219FB4EC for ; Thu, 10 May 2007 01:23:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 67773-09 for ; Thu, 10 May 2007 01:23:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by postgresql.org (Postfix) with ESMTP id 76D7E9FB3AF for ; Thu, 10 May 2007 01:23:27 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id B4C6D6447E0; Wed, 9 May 2007 22:23:26 -0600 (MDT) Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (crestone.coronasolutions.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27379-01; Wed, 9 May 2007 22:23:24 -0600 (MDT) Received: from mac.local (c-71-196-213-0.hsd1.co.comcast.net [71.196.213.0]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by crestone.coronasolutions.com (Postfix) with ESMTP id 8FCB364479D for ; Wed, 9 May 2007 22:23:24 -0600 (MDT) Message-ID: <46429E37.9080608@drivefaster.net> Date: Wed, 09 May 2007 22:23:19 -0600 From: Dan Harris User-Agent: Thunderbird 2.0.0.0 (Macintosh/20070326) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: finding fragmented tables References: <46429C77.5090302@drivefaster.net> In-Reply-To: <46429C77.5090302@drivefaster.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at coronasolutions.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/67 X-Sequence-Number: 25387 Dan Harris wrote: > Carin Westblom wrote: >> How can I easily find specific tables and/or databases with a lot of >> space that may be reclaimed w a vacuum full? >> > > I picked up this tip on the list a while ago: > > SELECT pg_namespace.nspname, pg_class.relname, pg_class.reltuples, > pg_class.relpages, rowwidths.avgwidth, ceil(pg_class.reltuples * > rowwidths.avgwidth::double precision / > current_setting('block_size'::text)::double precision) AS expectedpages, > pg_class.relpages::double precision / ceil(pg_class.reltuples * > rowwidths.avgwidth::double precision / > current_setting('block_size'::text)::double precision) AS bloat, > ceil((pg_class.relpages::double precision * > current_setting('block_size'::text)::double precision - > ceil(pg_class.reltuples * rowwidths.avgwidth::double precision)) / > 1024::double precision) AS wastedspace > FROM ( SELECT pg_statistic.starelid, sum(pg_statistic.stawidth) AS > avgwidth > FROM pg_statistic > GROUP BY pg_statistic.starelid) rowwidths > JOIN pg_class ON rowwidths.starelid = pg_class.oid > JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace > WHERE pg_class.relpages > 1; > > > then do: > > select * from relbloat order by wastedspace desc; I forgot to add that the select needs to be prepended by: create view relbloat as ... sorry about that! -Dan