PostgreSQLでテーブルと行数、データサイズの一覧を取得する。

VACUUM FULLとREINDEXを行った後に行うのが吉。

SELECT
    c.relname as "Name",
    CASE
        c.relkind
        WHEN 'r' THEN 'テーブル'
        WHEN 'v' THEN 'ビュー'
        WHEN 'i' THEN 'インデックス'
        WHEN 'S' THEN 'シーケンス'
        WHEN 's' THEN 'スペシャル'
    END AS "Type",
    reltuples as rows,
    pg_relation_size(relname) as bytes
FROM
    pg_catalog.pg_class c
    JOIN
        pg_catalog.pg_roles r ON r.oid = c.relowner
    LEFT JOIN
        pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r','v','S','i','') AND
    n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
    pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    relkind, relname;