postgresSql 查看相关数据库参数

[Ch-msm]

• 查看数据库大小

  select pg_size_pretty(pg_database_size('postgres'));

• 查看数据表大小

  select pg_size_pretty(pg_total_relation_size('infrastructure_asset'));

• 查看所有表大小

  select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

• 所有表和索引大小

  WITH user_schema AS (
  SELECT relnamespace FROM pg_catalog.pg_statio_user_tables urtb join pg_catalog.pg_class cls on urtb.relid=cls.oid group by relnamespace
  )
  SELECT
  relname AS objectname,
  relkind AS objecttype,
  reltuples AS "#entries",
  pg_size_pretty(relpages::bigint*8*1024) AS size
  FROM pg_catalog.pg_class JOIN user_schema ON pg_class.relnamespace=user_schema.relnamespace
  ORDER BY relpages DESC;