PostgreSQL查看数据库,索引,表,表空间大小的示例代码
PostgreSQL提供了多个系统管理函数来查看表,索引,表空间及数据库的大小,下面详细介绍一下。
二、数据库对象尺寸函数
三、实例讲解
3.1查看存储一个指定的数值需要的字节数
david=#selectpg_column_size(1);
pg_column_size
----------------
(1row)
david=#selectpg_column_size(10000);
pg_column_size
----------------
(1row)
david=#selectpg_column_size("david");
pg_column_size
----------------
(1row)
david=#selectpg_column_size("hello,world");
pg_column_size
----------------
(1row)
david=#selectpg_column_size("2013-04-1815:17:21.622885+08");
pg_column_size
----------------
(1row)
david=#selectpg_column_size("中国");
pg_column_size
----------------
(1row)
david=#
3.2查看数据库大小
查看原始数据
david=#\dtest
Table"public.test"
Column | Type |Modifiers
-----------+-----------------------+-----------
id |integer |
name |charactervarying(20)|
gender |boolean |
join_date|date |
dept |character(4) |
Indexes:
"idx_join_date_test"btree(join_date)
"idx_test"btree(id)
david=#selectcount(1)fromtest;
count
---------
(1row)
david=#
查看david数据库大小
david=# 查看所有数据库大小 david=# 这样查出来的结果,看上去太长了,不太容易读数。 3.3以人性化的方式显示大小 david=# 3.4查看单索引大小 david=#selectpg_size_pretty(pg_relation_size("idx_test")); david=# david=# 3.5查看指定表中所有索引大小 david=#selectpg_size_pretty(pg_indexes_size("test")); david=# idx_test和idx_join_date_test两个索引大小加起来差不多等于上面pg_indexes_size()查询出来的索引大小。 3.6查看指定schema里所有的索引大小,按从大到小的顺序排列。 david=#selectindexrelname,pg_size_pretty(pg_relation_size(relid))frompg_stat_user_indexeswhereschemaname="public"orderbypg_relation_size(relid)desc; david=# 3.7查看指定表大小 david=#selectpg_size_pretty(pg_relation_size("test")); david=# 使用pg_table_size()函数查看 david=#selectpg_size_pretty(pg_table_size("test")); david=# 3.8查看指定表的总大小 david=#selectpg_size_pretty(pg_total_relation_size("test")); david=# 3.9查看指定schema里所有的表大小,按从大到小的顺序排列。 david=# 3.10查看表空间大小 david=#selectpg_tablespace_size("pg_default"); david=#selectpg_size_pretty(pg_tablespace_size("pg_default")); david=# 另一种查看方法: david=#selectpg_tablespace_size("pg_default")/1024/1024/1024as"SIZEG"; david=#
david=#selectpg_database_size("david");
pg_database_size
------------------
(1row)
david=#selectpg_database.datname,pg_database_size(pg_database.datname)ASsizefrompg_database;
datname | size
-----------+-------------
template0| 6513156
postgres | 6657144
jboss | 6521348
bugs | 6521348
david | 190534776
BMCV3 |28147135608
mydb | 10990712
template1| 6521348
(8rows)
david=#selectpg_size_pretty(pg_database_size("david"));
pg_size_pretty
----------------
MB
(1row)
david=#selectpg_relation_size("idx_test");
pg_relation_size
------------------
(1row)
pg_size_pretty
----------------
MB
(1row)
david=#selectpg_size_pretty(pg_relation_size("idx_join_date_test"));
pg_size_pretty
----------------
MB
(1row)
david=#selectpg_indexes_size("test");
pg_indexes_size
-----------------
(1row)
pg_size_pretty
----------------
MB
(1row)
david=#select*frompg_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}
(6rows)
indexrelname |pg_size_pretty
-------------------------------+----------------
idx_join_date_test |91MB
idx_test |91MB
testtable_idx |1424kB
city_pkey |256kB
city11 |256kB
countrylanguage_pkey |56kB
sale_pkey |8192bytes
track_pkey |8192bytes
tbl_partition_201211_joindate|8192bytes
tbl_partition_201212_joindate|8192bytes
tbl_partition_201301_joindate|8192bytes
tbl_partition_201302_joindate|8192bytes
tbl_partition_201303_joindate|8192bytes
customer_pkey |8192bytes
album_pkey |8192bytes
item_pkey |8192bytes
tbl_partition_201304_joindate|8192bytes
tbl_partition_201307_joindate|8192bytes
tbl_partition_201305_joindate|0bytes
tbl_partition_201306_joindate|0bytes
(20rows)
david=#selectpg_relation_size("test");
pg_relation_size
------------------
(1row)
pg_size_pretty
----------------
MB
(1row)
david=#selectpg_table_size("test");
pg_table_size
---------------
(1row)
pg_size_pretty
----------------
MB
(1row)
david=#selectpg_total_relation_size("test");
pg_total_relation_size
------------------------
(1row)
pg_size_pretty
----------------
MB
(1row)
david=#selectrelname,pg_size_pretty(pg_relation_size(relid))frompg_stat_user_tableswhereschemaname="public"orderbypg_relation_size(relid)desc;
relname |pg_size_pretty
-------------------------------+----------------
test |91MB
testtable |1424kB
city |256kB
countrylanguage |56kB
country |40kB
testcount |8192bytes
tbl_partition_201302 |8192bytes
tbl_partition_201303 |8192bytes
person |8192bytes
customer |8192bytes
american_state |8192bytes
tbl_david |8192bytes
emp |8192bytes
tbl_partition_201212 |8192bytes
tbl_partition_201304 |8192bytes
tbl_partition_error_join_date|8192bytes
tbl_partition_201211 |8192bytes
album |8192bytes
tbl_partition_201307 |8192bytes
tbl_xulie |8192bytes
tbl_partition_201301 |8192bytes
sale |8192bytes
item |8192bytes
track |8192bytes
tbl_partition_201306 |0bytes
tbl_partition |0bytes
tbl_partition_201305 |0bytes
person2 |0bytes
(28rows)
david=#selectspcnamefrompg_tablespace;
spcname
------------
pg_default
pg_global
(2rows)
pg_tablespace_size
--------------------
(1row)
pg_size_pretty
----------------
GB
(1row)
david=#selectpg_tablespace_size("pg_default")/1024/1024as"SIZEM";
SIZEM
--------
(1row)
SIZEG
--------
(1row)相关文章