redshift_size_by_table.sql

select
  cast(use2.usename as varchar(50)) as owner,
  pgc.oid,
  trim(pgdb.datname) as Database,
  trim(pgn.nspname) as Schema,
  trim(a.name) as Table,
  (b.mbytes / 1024) gbytes,
  a.rows,
  ((case when a.rows > 0 then b.mbytes / a.rows::real end) * 1024) kb_per_row,
  round(b.mbytes / (sum(b.mbytes) over ())::real * 100, 4) pct_of_cluster
from
 (select db_id, id, name, sum(rows) as rows
  from stv_tbl_perm a
  group by db_id, id, name
  ) as a
 join pg_class as pgc on pgc.oid = a.id
 left join pg_user use2 on (pgc.relowner = use2.usesysid)
 join pg_namespace as pgn on pgn.oid = pgc.relnamespace
    and pgn.nspowner > 1
 join pg_database as pgdb on pgdb.oid = a.db_id
 join
   (select tbl, count(*) as mbytes
    from stv_blocklist
    group by tbl
   ) b on a.id = b.tbl
 order by mbytes desc, a.db_id, a.name;