From: Jeff Boes <jboes@nexcerpt.com>
Subject: Query to evaluate space used
Date: Wed, 18 Sep 2002 15:56:08 -0400
Organization: Nexcerpt, Inc.
Lines: 51
Message-ID: <amaln4$312j$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.11.3 (Unix)
To: pgsql-sql@postgresql.org

This may or may not be original, but I cobbled it together and thought it
might be useful:  a query that reports how much space is used by each
table in your database, including TOAST and TOAST-IDX tables.

This particular version is a bit fancy because it shows the top 20 by
space, followed by a row for "All Others".  You could eliminate the half
starting with 'union', and take out the 'limit 20' clause if you wanted
to see them all.

select "Table", "KRows", "MB" from
(select 1 as sort_order, * from (select min(relname) as "Table",
to_char(max(reltuples)/1000,'9990.9') as "KRows",
sum(relpages)/128 as "MB" from (
    select relname, '', reltuples, relpages
    from pg_class
    where relkind = 'r'
    union all
    select a.relname, b.relname, 0, b.relpages
    from pg_class a
    join pg_class b
    on (b.relname like 'pg_toast_' || a.relfilenode || '%')
    where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc limit 20) as top_20
union
select 2, 'All Others', to_char(sum("KRows"),'9990.9'),
sum("MB")
from (
select min(relname) as "Table",
sum(reltuples)/1000 as "KRows",
sum(relpages)/128 as "MB" from (
    select relname, '', reltuples, relpages
    from pg_class
    where relkind = 'r'
    union all
    select a.relname, b.relname, 0, b.relpages
    from pg_class a
    join pg_class b
    on (b.relname like 'pg_toast_' || a.relfilenode || '%')
    where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc offset 20) as "Others") as rows
order by sort_order, "MB" desc

-- 
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise