[PSQL - Query Table Sizes]Table/View Sizes #psql

psql-query-table-sizes.sql

--
-- PostgreSQL: query how larger tables (data + index) are
--
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
    , pg_size_pretty(sum(table_bytes) OVER(ORDER BY total_bytes)) AS table_total
    , pg_size_pretty(sum(total_bytes) OVER(ORDER BY total_bytes)) AS db_total
  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::numeric(16,0) 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 IN ('r', 'i', 'm')
  ) a
) a 
-- WHERE table_name LIKE 'mat%' 
ORDER BY total_bytes DESC
;