1

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.

  • I can give you a SQL statement that using Postgres-Tables to calculate Table-size and Partitions-sizes (grouped by ParentTablename) but its also a little bit roundabout – Patrick7 Apr 6 '16 at 13:31
  • Why and how is this query not easy? – dezso Apr 6 '16 at 13:55
  • Well it doesn't work on postgres for a start. – PhilHibbs Apr 6 '16 at 14:44
3

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.