Thanks, appears to work great!! Peter On Feb 13, 2008 9:46 PM, Jeff Frost <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > 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 >
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings