pg_size view

pg_size.sql

CREATE OR REPLACE VIEW pg_size AS (
  SELECT
    a.table_name                  AS name,
    a.row_estimate :: INTEGER     AS row_estimate,
    pg_size_pretty(a.total_bytes) AS total,
    pg_size_pretty(a.table_bytes) AS "table",
    pg_size_pretty(a.index_bytes) AS index,
    pg_size_pretty(a.toast_bytes) AS toast
  FROM (SELECT
          a_1.oid,
          a_1.table_schema,
          a_1.table_name,
          a_1.row_estimate,
          a_1.total_bytes,
          a_1.index_bytes,
          a_1.toast_bytes,
          a_1.total_bytes - a_1.index_bytes - COALESCE(a_1.toast_bytes, 0 :: BIGINT) AS table_bytes
        FROM (SELECT
                c.oid,
                n.nspname                                           AS table_schema,
                c.relname                                           AS table_name,
                c.reltuples                                         AS row_estimate,
                pg_total_relation_size(c.oid :: REGCLASS)           AS total_bytes,
                pg_indexes_size(c.oid :: REGCLASS)                  AS index_bytes,
                pg_total_relation_size(c.reltoastrelid :: REGCLASS) AS toast_bytes
              FROM pg_class c
                LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relkind = ANY (ARRAY ['r' :: "char", 'm' :: "char"])) a_1) a
  ORDER BY a.total_bytes DESC
);