Query to evaluate space used

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