report.sql

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

-[ RECORD 1 ]+------------------------------
oid          | 19767
table_schema | public
table_name   | account_changes
row_estimate | 47
total_bytes  | 147456
index_bytes  | 98304
toast_bytes  | 8192
table_bytes  | 40960
total        | 144 kB
index        | 96 kB
toast        | 8192 bytes
table        | 40 kB


WITH entries AS (
  SELECT *, total_bytes - index_bytes - COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
          AND nspname = 'public'
  ) a
)
SELECT pg_size_pretty(SUM(total_bytes)) AS total_size, pg_size_pretty(SUM(index_bytes)) AS index_size FROM entries
;

total size & index size