Re: How much space do database objects take up in data files

Thanks, appears to work great!!
Peter

On Feb 13, 2008 9:46 PM, Jeff Frost <jeff(at)frostconsultingllc(dot)com> wrote:
> On Wed, 13 Feb 2008, Carol Walter wrote:
>
> > I've struggled with this one too. You can get the size of databases with
> > SELECT pg_database_size('database name'); You can get the size of tables
> > with SELECT pg_relation_size('table name');
> >
> > Carol
> > On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote:
> >
> >> Hi,
> >>
> >> How can I find out how much space is taken up by database objects in data
> >> files?
>
> Give this query a try. It's a variation of one posted on this list some time
> ago. Unfortunately, I'm not sure who to credit for the original post. This
> one takes into account index and toast size and sorts descending by totalsize.
>
> SELECT nspname, relname,
> pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
> FROM
> (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
> COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
> FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
> CASE WHEN reltoastrelid=0 THEN 0
> ELSE pg_relation_size(reltoastrelid)
> END AS toastsize,
> CASE WHEN reltoastrelid=0 THEN 0
> ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
> WHERE ct.oid = cl.reltoastrelid))
> END AS toastindexsize
> FROM pg_class cl, pg_namespace ns
> WHERE cl.relnamespace = ns.oid
> AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
> AND cl.relname IN
> (SELECT table_name FROM information_schema.tables
> WHERE table_type = 'BASE TABLE')) ss
> ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;
>
>
>
> --
> Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>