• 技术文章 >数据库 >PostgreSQL

    PostgreSQL如何查询每个表的大小

    月亮邮递员月亮邮递员2020-04-01 15:24:15原创8959

    PostgreSQL如何查询每个表的大小

    1、查询数据库中单个表的大小(不包含索引)

    1

    select pg_size_pretty(pg_relation_size('表名'));

    2、查询所有表的大小并排序(包含索引)

    推荐:postgresql教程

    1

    2

    3

    4

    5

    SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"'

        || table_schema || '"."' || table_name || '"')) AS size

    FROM information_schema.tables

    ORDER BY

    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20

    3、查询表大小按大小排序并分离data与index

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    SELECT

    table_name,

    pg_size_pretty(table_size) AS table_size,

    pg_size_pretty(indexes_size) AS indexes_size,

    pg_size_pretty(total_size) AS total_size

    FROM (

    SELECT

    table_name,

    pg_table_size(table_name) AS table_size,

    pg_indexes_size(table_name) AS indexes_size,

    pg_total_relation_size(table_name) AS total_size

    FROM (

    SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name

    FROM information_schema.tables

    ) AS all_tables

    ORDER BY total_size DESC

    ) AS pretty_sizes

    推荐学习《Python教程》。

    专题推荐:postgresql 查询 表大小
    上一篇:PostgreSQL如何查询所有表名 下一篇:PostgreSQL如何安装

    相关文章推荐

    • python怎么知道列表大小• PostgreSQL如何导出数据库文件• PostgreSQL如何创建视图• PostgreSQL如何查询所有表名

    全部评论我要评论

    © 2021 Python学习网 苏ICP备2021003149号-1

  • 取消发布评论
  • 

    Python学习网