TIL

Today I Learned

---

Project maintained by gushwell Hosted on GitHub Pages — Theme by mattgraham

PostgreSQL の各インデックスのサイズを確認する

以下のSQLを実行する

SELECT 
  nspname || '.' || relname AS "relation",
  relpages,  pg_relation_size(C.oid) AS "size"
FROM  pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;

こちらの方が簡単かな。

SELECT tablename, indexname, relpages, pg_relation_size(N.oid) FROM pg_indexes IX
LEFT JOIN pg_class N on (N.relname = IX.indexname)
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(N.oid) DESC;

参考URL:

https://wiki.postgresql.org/wiki/Disk_Usage/ja

ちなみに、インデックスの再構築をするには、

reindex index インデックス名;

reindex table テーブル名;