Table and index size (including partitioned tables)

Post date: Oct 16, 2014 7:1:7 PM

SELECT tabs.nspname AS schema_name

, COALESCE(parts.tablename, tabs.relname) AS table_name

, ROUND(SUM(sotaidtablesize)/1024/1024/1024,3) AS table_GB

, ROUND(SUM(sotaididxsize)/1024/1024/1024,3) AS index_GB

, ROUND(SUM(sotaidtablesize)/1024/1024/1024,3)+ROUND(SUM(sotaididxsize)/1024/1024/1024,3) total_gb

FROM   gp_toolkit.gp_size_of_table_and_indexes_disk sotd

,     (SELECT c.oid, c.relname, n.nspname

       FROM   pg_class c

       ,      pg_namespace n

       WHERE  n.oid = c.relnamespace

       AND    c.relname NOT LIKE '%_err'

      )tabs

LEFT JOIN pg_partitions parts

ON     tabs.nspname = parts.schemaname

AND    tabs.relname = parts.partitiontablename

WHERE  sotd.sotaidoid = tabs.oid and tabs.nspname = 'public'

GROUP BY tabs.nspname, COALESCE(parts.tablename, tabs.relname)

ORDER BY 1 desc,(ROUND(SUM(sotaidtablesize)/1024/1024/1024,3)+ROUND(SUM(sotaididxsize)/1024/1024/1024,3)) desc,2;