pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Kovacs <peter.kovacs.1.0rc@gmail.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: Carol Walter <walterc@indiana.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: How much space do database objects take up in data files
Date: Wed, 13 Feb 2008 22:05:55 +0100
Message-ID: <b6e8f2e80802131305m76cb5fa5i6632f28533761bbe@mail.gmail.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0802131222500.5521@discord.home.frostconsultingllc.com>
References: <b6e8f2e80802131043j70c15938ja094e45cd1559d5e@mail.gmail.com>
	<96FC91B8-A568-4781-80D5-0319AA27E8A3@indiana.edu>
	<Pine.LNX.4.64.0802131222500.5521@discord.home.frostconsultingllc.com>

Howooow!!! Great!!!

Thanks a lot.
Peter

On Feb 13, 2008 9:46 PM, Jeff Frost <jeff@frostconsultingllc.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@frostconsultingllc.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
>



view thread (6+ messages)  latest in thread

Message-ID: <b6e8f2e80802131305m76cb5fa5i6632f28533761bbe@mail.gmail.com>
Permalink:  ../b6e8f2e80802131305m76cb5fa5i6632f28533761bbe@mail.gmail.com/
Also on:    postgresql.org/message-id/b6e8f2e80802131305m76cb5fa5i6632f28533761bbe@mail.gmail.com

 · 

reply

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-admin@postgresql.org
  Cc: peter.kovacs.1.0rc@gmail.com, jeff@frostconsultingllc.com, walterc@indiana.edu
  Subject: Re: How much space do database objects take up in data files
  In-Reply-To: <b6e8f2e80802131305m76cb5fa5i6632f28533761bbe@mail.gmail.com>

* 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