018.PGSQL-pgsql查询库的⼤⼩、表的⼤⼩
库的⼤⼩
1.在postgresql数据库中默认情况下可通过pg_database_size函数加数据库名称的⽅式来查看数据库的⼤⼩
select  pg_database_size('ioc')
select pg_size_pretty(pg_database_size('ioc'))
表的⼤⼩
表的记录数
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind ='r'and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts -- 查出所有表（包含索引）并排序
-- 查出所有表（包含索引）并排序
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  where table_schema in ( 'ioc_dm'  ,'ioc_dw','ioc_ods','ioc_standard','ioc_support','ioc_theme')
ORDER BY
pg_total_relation_size('"'|| table_schema ||'"."'|| table_name ||'"') DESC
-- 查出所有表（包含索引）并排序
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  where table_schema ='ioc_dm'and table_name like'%index%'
ORDER BY
pg_total_relation_size('"'|| table_schema ||'"."'|| table_name ||'"') DESC
-
-数据库中单个表的⼤⼩（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));
--查出所有表（包含索引）并排序
tabletotal函数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
--查出表⼤⼩按⼤⼩排序并分离data与index
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
元命令查看表⼤⼩、索引⼤⼩
mydb=> \dt
List of relations
Schema|  Name    | Type  | Owner
----------+-----------+-------+--------
myschema | o_ls_test |table| pguser
(1 row)
mydb=> \dt+ o_ls_test
List of relations
Schema|  Name    | Type  | Owner  |  Size  | Description
----------+-----------+-------+--------+--------+-------------
myschema | o_ls_test |table| pguser |326 MB |测试表
(1 row)
mydb=> \di+ rid_index
List of relations
Schema|  Name    | Type  | Owner  |Table|  Size  | Description
----------+-----------+-------+--------+-----------+--------+-------------
myschema | rid_index |index| pguser | o_ls_test |107 MB |

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。