Get total size for all Postgres or Greenplum tables including partitions

Question

Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout:

with part_sz as (
  select ts.sotdschemaname sch
       , coalesce(ps.sopaidparenttablename,ts.sotdtablename) tbl
       , coalesce(ps.sopaidpartitiontablesize,ts.sotdsize) part_size
  from gp_toolkit.gp_size_of_table_disk ts
  left join gp_toolkit.gp_size_of_partition_and_indexes_disk ps
  on ts.sotdschemaname = ps.sopaidpartitionschemaname
  and ts.sotdtablename = ps.sopaidpartitiontablename
)
, tbl_sz as (
  select sch, tbl, sum(part_size) total_size
  from part_sz
  group by sch, tbl
)
select sch, tbl, total_size
from tbl_sz
order by total_size desc

Greenplum is built on Postgres 8.2, so something using that feature set would be ideal, but feel free to post anything using newer Postgres versions for the benefit of other readers.

Answer 1

Here is what i have, its also a little bit roundabout and not testet in PG8.2 (not supported anymore ;) ) but it shows your schema.tablename,PrettySize (first Select) and partitioned Tables are grouped to one (ParentTablename) (second Select). its maybe not the best solution (Tuning welcome)

--get all tables sizes and group partitioned tables by PartenTablename
SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT OUTER JOIN pg_inherits I ON (C.relfilenode = I.inhrelid OR C.relfilenode = I.inhparent)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND I.inhrelid is null
UNION ALL
select nspname || '.' || relname AS "relation", 
       pg_size_pretty(sum(pg_relation_size(inhrelid))) AS "total_size" 
FROM pg_inherits  I 
LEFT JOIN pg_class C ON (C.oid = I.inhparent)
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)  
GROUP BY  nspname,relname
ORDER BY 1,2