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('time'));

---

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

select pg_size_pretty(pg_indexes_size('dw_cs'));

---

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

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

---

六： 查询指定表大小（仅仅指定表数据）

select pg_size_pretty(pg_relation_size('dw_cs'));

---

七： 查询指定表总大小（包括表数据与索引）

select pg_size_pretty(pg_total_relation_size('dw_cs'));

---

八： 查询指定模式里面所有表大小（仅仅指定表数据，从大到小）

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' 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 = 'public' 
        ) table_num ON table_num.relname = table_size.relname 
WHERE
    schemaname = 'public' 
ORDER BY
    pg_relation_size ( relid ) DESC;

---