Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3C1379FB42D for ; Thu, 10 May 2007 01:15:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 57936-02 for ; Thu, 10 May 2007 01:15:54 -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 5E0549FB43A for ; Thu, 10 May 2007 01:15:55 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id 27E416447E0; Wed, 9 May 2007 22:15:54 -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 26996-10; Wed, 9 May 2007 22:15:52 -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 4949864479D for ; Wed, 9 May 2007 22:15:52 -0600 (MDT) Message-ID: <46429C77.5090302@drivefaster.net> Date: Wed, 09 May 2007 22:15:51 -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: In-Reply-To: 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/66 X-Sequence-Number: 25386 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;