基于视图的 占用空间 查询

• 所有数据库的占用空间

  SELECT pd.datname, 
      pg_size_pretty(pg_database_size(pd.datname)) AS size 
  FROM pg_database pd;
  

• 单个数据库占用空间查询

  SELECT pg_size_pretty(pg_database_size('数据库名'));
  

• 单个表占用空间查询

  • 不包括索引的大小

    SELECT pg_size_pretty(pg_table_size('表名'));
    

  • 包括索引的总大小

    SELECT pg_size_pretty(pg_total_relation_size('表名'));
    

• public下所有单个表的

  • 索引总占用空间(由大到小)

    SELECT relname, 
        pg_size_pretty(pg_indexes_size(relid)) table_indexes_size
    FROM pg_stat_user_tables
    WHERE schemaname='public' 
    ORDER by pg_indexes_size(relid) DESC;
    

    • relname 表的名字
    • table_indexes_size 表内所有索引的总大小
  • 内容+索引总占用空间大小(由大到小)

    SELECT relname, 
        pg_size_pretty(pg_relation_size(relid)) table_tables_size
    FROM pg_stat_user_tables
    WHERE schemaname='public' 
    ORDER by pg_relation_size(relid) DESC;
    

• 单个索引占用空间查询

  SELECT pg_size_pretty(pg_relation_size('索引名字'));
  

• public下，所有单个索引的占用空间(由大到小)

  SELECT indexrelname, 
      pg_size_pretty(pg_relation_size(indexrelid)) single_index_size
  FROM pg_stat_user_indexes
  WHERE schemaname='public' 
  ORDER by pg_relation_size(indexrelid) DESC;
  

  • indexrelname 索引的名字
  • single_index_size 单个索引的大小

• 查询一个字符串，需要多少字节的列去存储

  SELECT pg_column_size('测试字符串');