このエントリーをはてなブックマークに追加

PostgreSQLでテーブルサイズを得る

自分用のコピペメモです。

PostgreSQLでテーブルのサイズを取得します。

うわ、私のSQL力低すぎ…

SELECT
    relname
    ,CASE
        WHEN relkind = 'r' THEN 'Tablee'
        WHEN relkind = 'S' THEN 'Sequence'
        WHEN relkind = 'i' THEN 'Index'
        WHEN relkind = 'v' THEN 'View'
    END
    ,to_char(reltuples, '999,999,999') AS rows
    ,pg_size_pretty(pg_relation_size(oid)) AS size
FROM
    pg_class
WHERE
    relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')

UNION ALL

SELECT
    'TOTAL'
    ,'Table'
    ,to_char(sum(reltuples), '999,999,999') AS rows
    ,pg_size_pretty(sum(pg_relation_size(oid))::bigint) AS size
FROM
    pg_class
WHERE
    relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')