表のサイズをヒープ / インデックス / TOAST別に、および最大のインデックスを一覧表示

コマンド

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid) - pg_indexes_size(c.oid)) AS toast_fsm_vm,
       round(100.0 * pg_total_relation_size(c.oid) / NULLIF(pg_database_size(current_database()), 0), 1) AS pct_of_db
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

-- Largest indexes and their usage counts
SELECT s.schemaname,
       s.relname AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan,
       i.indisunique,
       i.indisprimary
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

概要

最も大きい表を合計、ヒープ、インデックス、TOAST(+FSM/VM)に分けて、データベースに占める割合とともに表示する。最も大きいインデックスをスキャン回数とunique/primaryのフラグとともに表示する。

使用する場面

ディスク使用量の増加時に、増加の原因(データ、インデックス、TOAST)を特定するため。使用されていない大きなインデックスの発見に。

注意

サイズ関数はリレーションごとにファイルシステムにアクセスするため、数万の表を持つデータベースでは遅くなる場合がある。パーティション表の親(relkind 'p')自体は0バイトであり除外される。階層全体の合計は: SELECT pg_size_pretty(sum(pg_total_relation_size(relid))) FROM pg_partition_tree('sales'); idx_scan = 0の大きなインデックスは削除候補だが、カウンタは最後の統計リセット以降かつこのサーバーのみの値である(レプリカでの使用は見えない)。unique/primaryインデックスは制約を強制しているため削除しない。