From: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: Query to evaluate space used
Date: Wed, 18 Sep 2002 15:56:08 -0400
Message-ID: <amaln4$312j$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jboes@nexcerpt.com
Subject: Re: Query to evaluate space used
In-Reply-To: <amaln4$312j$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox