select pg_size_pretty(pg_relation_size('idx_test'));
pg_size_pretty
----------------
39 MB
(1 row)
david=#
david=# select pg_size_pretty(pg_relation_size('idx_join_date_test'));
pg_size_pretty
----------------
39 MB
(1 row)
david=#
3.5 查看指定表中所有索引大小
david=# select pg_indexes_size('test');
pg_indexes_size
-----------------
82477056
(1 row)
david=# select pg_size_pretty(pg_indexes_size('test'));
pg_size_pretty
----------------
79 MB
(1 row)
david=#
idx_test 和idx_join_date_test 两个索引大小加起来差不多等于上面pg_indexes_size() 查询出来的索引大小。
3.6 查看指定schema 里所有的索引大小，按从大到小的顺序排列。
david=# select * from pg_namespace;
nspname | nspowner | nspacl
--------------------+----------+-------------------------------------
pg_toast | 10 |
pg_temp_1 | 10 |
pg_toast_temp_1 | 10 |
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
public | 10 | {postgres=UC/postgres,=UC/postgres}
(6 rows)
david=# select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
indexrelname | pg_size_pretty
-------------------------------+----------------
idx_join_date_test | 91 MB
idx_test | 91 MB
testtable_idx | 1424 kB
city_pkey | 256 kB
city11 | 256 kB
countrylanguage_pkey | 56 kB
sale_pkey | 8192 bytes
track_pkey | 8192 bytes
tbl_partition_201211_joindate | 8192 bytes
tbl_partition_201212_joindate | 8192 bytes
tbl_partition_201301_joindate | 8192 bytes
tbl_partition_201302_joindate | 8192 bytes
tbl_partition_201303_joindate | 8192 bytes
customer_pkey | 8192 bytes
album_pkey | 8192 bytes
item_pkey | 8192 bytes
tbl_partition_201304_joindate | 8192 bytes
tbl_partition_201307_joindate | 8192 bytes
tbl_partition_201305_joindate | 0 bytes
tbl_partition_201306_joindate | 0 bytes
(20 rows)
david=#
3.7 查看指定表大小
david=# select pg_relation_size('test');
pg_relation_size
------------------
95748096
(1 row)
david=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
91 MB
(1 row)
david=#
使用pg_table_size() 函数查看
david=# select pg_table_size('test');
pg_table_size
---------------
95789056
(1 row)
david=# select pg_size_pretty(pg_table_size('test'));
pg_size_pretty
----------------
91 MB
(1 row)
david=#
3.8 查看指定表的总大小
david=# select pg_total_relation_size('test');
pg_total_relation_size
------------------------
178266112
(1 row)
david=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
170 MB
(1 row)
david=#
3.9 查看指定schema 里所有的表大小，按从大到小的顺序排列。
david=# se