PostgreSql数据库表空间,索引,表大小查询


Postgresql 数据库

一: 查询表空间大小

select pg_size_pretty(pg_tablespace_size('pg_default'));

二: 查询所有数据库大小

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_databas;

三: 查询指定索引大小

select pg_size_pretty(pg_relation_size('索引字段名'));

四: 查询指定表所有索引大小

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

五: 查询指定模式里所有的索引大小,按从大到小的顺序排列

select relname,indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='模式名' order by pg_relation_size(relid) desc;

六: 查询指定表大小(仅仅指定表数据)

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

七: 查询指定表总大小(包括表数据与索引)

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

八: 查询指定模式里面所有表大小(仅仅指定表数据,从大到小)

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

九:查询指定schema里面所有表数据大小,总索引大小,总大小,行数

SELECT
    table_size.relname 表名,
    pg_size_pretty ( pg_relation_size ( relid ) ) 表数据大小,
    pg_size_pretty ( pg_indexes_size ( relid ) ) 表总索引大小,
    pg_size_pretty ( pg_total_relation_size ( relid ) ) 表总大小,
    表行数 
FROM
pg_stat_user_tables table_size
    LEFT JOIN (
        SELECT
            relname,
            reltuples :: DECIMAL ( 19, 0 ) 表行数 
        FROM
        pg_class r
        JOIN pg_namespace n ON ( relnamespace = n.oid ) 
        WHERE
            relkind = 'r' 
            AND n.nspname = '模式名' 
        ) table_num ON table_num.relname = table_size.relname 
WHERE
    schemaname = '模式名' 
ORDER BY
    pg_relation_size ( relid ) DESC;

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐