pg_sizes.sql

SELECT relation,
       total_size,
       relation_size,
       indexes_size,
       count
FROM
    (SELECT nspname || '.' || relname AS "relation",
                              pg_size_pretty(pg_total_relation_size(C.oid) + COALESCE(partitions_info.total_size, 0)) AS "total_size",
                              pg_size_pretty(pg_relation_size(C.oid) + COALESCE(partitions_info.relation_size, 0)) AS "relation_size",
                              pg_size_pretty(pg_total_relation_size(C.oid) + COALESCE(partitions_info.total_size, 0) - pg_relation_size(C.oid) - COALESCE(partitions_info.relation_size, 0)) AS "indexes_size",
                              pg_total_relation_size(C.oid) + COALESCE(partitions_info.total_size, 0) "ts",
                              C.reltuples::bigint + COALESCE(partitions_info.reltuples, 0) AS "count"
     FROM pg_class C
     LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
     LEFT OUTER JOIN
         ( SELECT inhparent,
                  COALESCE(SUM(pg_relation_size(inhrelid))::bigint, 0) AS relation_size,
                  COALESCE(SUM(pg_total_relation_size(inhrelid))::bigint, 0) AS total_size,
                  COALESCE(SUM(pg_class.reltuples::bigint), 0) as reltuples
          FROM pg_inherits
          LEFT JOIN pg_class on (pg_inherits.inhrelid = pg_class.oid)
          GROUP BY inhparent) partitions_info ON (C.oid = partitions_info.inhparent)
     WHERE nspname NOT IN ('pg_catalog',
                           'information_schema')
         AND C.relkind <> 'i'
         AND nspname !~ '^pg_toast'
         AND C.oid NOT IN
             (SELECT DISTINCT inhrelid
              FROM pg_inherits) ) ts
ORDER BY ts DESC;