原作者：李元鹏
- 对象size函数
- 测试使用
- 创建测试数据
- pg_column_size
- pg_database_size
- pg_indexes_size
- pg_partition_indexes_size
- pg_partition_size
- pg_relation_size
- pg_size_pretty
- pg_table_size
- pg_tablespace_size
- pg_total_relation_size
MogDB为用户提供了很多的系统函数来对数据库、表空间、表、索引、甚至字段的大小进行查看。
对象size函数
MogDB=# \df pg_*size*
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+---------------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_column_size | integer | "any" | normal | f | f | f
pg_catalog | pg_database_size | bigint | name | normal | f | f | f
pg_catalog | pg_database_size | bigint | oid | normal | f | f | f
pg_catalog | pg_indexes_size | bigint | regclass | normal | f | f | f
pg_catalog | pg_partition_indexes_size | bigint | oid, oid | normal | f | f | f
pg_catalog | pg_partition_indexes_size | bigint | text, text | normal | f | f | f
pg_catalog | pg_partition_size | bigint | oid, oid | normal | f | f | f
pg_catalog | pg_partition_size | bigint | text, text | normal | f | f | f
pg_catalog | pg_relation_size | bigint | regclass | normal | f | f | f
pg_catalog | pg_relation_size | bigint | regclass, text | normal | f | f | f
pg_catalog | pg_size_pretty | text | bigint | normal | f | f | f
pg_catalog | pg_size_pretty | text | numeric | normal | f | f | f
pg_catalog | pg_table_size | bigint | regclass | normal | f | f | f
pg_catalog | pg_tablespace_size | bigint | name | normal | f | f | f
pg_catalog | pg_tablespace_size | bigint | oid | normal | f | f | f
pg_catalog | pg_total_relation_size | bigint | regclass | normal | f | f | f
(16 rows)
MogDB=#
测试使用
创建测试数据
MogDB=# create tablespace lxs_tbs relative location 'tablespace/lxs_tbs1';
CREATE TABLESPACE
MogDB=# create database lxsdb with tablespace = lxs_tbs;
CREATE DATABASE
MogDB=# create user lxs identified by 'lxs' sysadmin;
ERROR: Password must contain at least 8 characters.
MogDB=# create user lxs identified by 'enmotech@123' sysadmin;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
MogDB=# \c lxsdb lxs
Password for user lxs:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "lxsdb" as user "lxs".
lxsdb=>
lxsdb=> create table lxs(
lxsdb(> id int,
lxsdb(> name varchar2(20),
lxsdb(> info text,
lxsdb(> constraint pk_lxs_id primary key(id)
lxsdb(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_lxs_id" for table "lxs"
CREATE TABLE
lxsdb=> create index idx_lxs_name on lxs(name);
CREATE INDEX
lxsdb=> insert into lxs select n,'hello mogdb'||n ,n from generate_series(1,1000000) n;
INSERT 0 1000000
lxsdb=> create table lyp
lxsdb-> ( id int,
lxsdb(> name char(20)
lxsdb(> )
lxsdb-> partition by range (id)
lxsdb-> (partition lyp_p1 values less than (10000),
lxsdb(> partition lyp_p2 values less than (20000),
lxsdb(> partition lyp_p3 values less than (30000)
lxsdb(> );
CREATE TABLE
lxsdb=> create index idx_lyp_id on lyp(id) local
lxsdb-> (
lxsdb(> partition idx_lyp_id1,
lxsdb(> partition idx_lyp_id2,
lxsdb(> partition idx_lyp_id3
lxsdb(> );
CREATE INDEX
lxsdb=> insert into lyp select n,'name'||n from genrate_series(1,28000) n;
ERROR: function genrate_series(integer, integer) does not exist
LINE 1: insert into lyp select n,'name'||n from genrate_series(1,280...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
lxsdb=> insert into lyp select n,'name'||n from generate_series(1,28000) n;
INSERT 0 28000
lxsdb=>
lxsdb=> \dt+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+---------+----------------------------------+-------------
public | lxs | table | lxs | 58 MB | {orientation=row,compression=no} |
public | lyp | table | lxs | 1752 kB | {orientation=row,compression=no} |
(2 rows)
lxsdb=> \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+--------------+-------+-------+-------+--------+---------+-------------
public | idx_lxs_name | index | lxs | lxs | 72 MB | |
public | idx_lyp_id | index | lxs | lyp | 672 kB | |
public | pk_lxs_id | index | lxs | lxs | 21 MB | |
(3 rows)
lxsdb=>
pg_column_size
lxsdb=> \df pg_column_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+----------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_column_size | integer | "any" | normal | f | f | f
(1 row)
lxsdb=>
查看存储一个指定值需要的字节大小，返回数值类型，可跟任意类型的参数。
lxsdb=> select pg_column_size(1);
pg_column_size
----------------
4
(1 row)
lxsdb=> select pg_column_size(100000000);
pg_column_size
----------------
4
(1 row)
lxsdb=> select pg_column_size('Hello MogDB!');
pg_column_size
----------------
13
(1 row)
lxsdb=> select pg_column_size('您好');
pg_column_size
----------------
7
(1 row)
lxsdb=>
pg_database_size
lxsdb=> \df pg_database_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_database_size | bigint | name | normal | f | f | f
pg_catalog | pg_database_size | bigint | oid | normal | f | f | f
(2 rows)
lxsdb=>
用于查看数据库大小，返回数字类型，参数数据类型为name和oid。
lxsdb=> \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+---------------+--------+------------+--------------------------------------------
lxsdb | omm | UTF8 | C | C | | A | 166 MB | lxs_tbs |
postgres | omm | UTF8 | C | C | | A | 42 MB | pg_default | default administrative connection database
template0 | omm | UTF8 | C | C | =c/omm +| A | 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | | |
template1 | omm | UTF8 | C | C | =c/omm +| A | 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | | |
(4 rows)
lxsdb=> select oid,datname,
lxsdb-> pg_database_size(oid),
lxsdb-> pg_database_size(datname)
lxsdb-> from pg_database order by 2;
oid | datname | pg_database_size | pg_database_size
-------+-----------+------------------+------------------
49173 | lxsdb | 174391300 | 174391300
15940 | postgres | 43638788 | 43638788
15935 | template0 | 13197316 | 13197316
1 | template1 | 13205508 | 13205508
(4 rows)
lxsdb=> select oid,datname,
lxsdb-> pg_database_size(oid)/1024/1024 mb,
lxsdb-> pg_database_size(datname)/1024/1024 mb
lxsdb-> from pg_database order by 2;
oid | datname | mb | mb
-------+-----------+------------------+------------------
49173 | lxsdb | 166.312503814697 | 166.312503814697
15940 | postgres | 41.6171913146973 | 41.6171913146973
15935 | template0 | 12.5859413146973 | 12.5859413146973
1 | template1 | 12.5937538146973 | 12.5937538146973
(4 rows)
lxsdb=>
pg_indexes_size
lxsdb=> \df pg_indexes_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+-----------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_indexes_size | bigint | regclass | normal | f | f | f
(1 row)
lxsdb=>
查看指定表中所有索引大小，72M + 21M = 93M。
lxsdb=> \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+--------------+-------+-------+-------+--------+---------+-------------
public | idx_lxs_name | index | lxs | lxs | 72 MB | |
public | idx_lyp_id | index | lxs | lyp | 672 kB | |
public | pk_lxs_id | index | lxs | lxs | 21 MB | |
(3 rows)
lxsdb=> select oid,relname from pg_class where relname='lxs';
oid | relname
-------+---------
49178 | lxs
(1 row)
lxsdb=> select oid,relname,
lxsdb-> pg_indexes_size(49178),
lxsdb-> pg_indexes_size('lxs'),
lxsdb-> pg_indexes_size(49178)/1024/1024 mb,
lxsdb-> pg_indexes_size('lxs')/1024/1024 mb,
lxsdb-> pg_indexes_size(oid),
lxsdb-> pg_indexes_size(relname::regclass),
lxsdb-> pg_indexes_size(oid)/1024/1024 mb,
lxsdb-> pg_indexes_size(relname::regclass)/1024/1024 mb
lxsdb-> from pg_class where relname='lxs';
oid | relname | pg_indexes_size | pg_indexes_size | mb | mb | pg_indexes_size | pg_indexes_size | mb | mb
-------+---------+-----------------+-----------------+------------+------------+-----------------+-----------------+------------+------------
49178 | lxs | 97918976 | 97918976 | 93.3828125 | 93.3828125 | 97918976 | 97918976 | 93.3828125 | 93.3828125
(1 row)
lxsdb=>
pg_partition_indexes_size
lxsdb=> \df pg_partition_indexes_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+---------------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_partition_indexes_size | bigint | oid, oid | normal | f | f | f
pg_catalog | pg_partition_indexes_size | bigint | text, text | normal | f | f | f
(2 rows)
lxsdb=>
查看指定分区表下的分区索引大小。
lxsdb=> \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+--------------+-------+-------+-------+--------+---------+-------------
public | idx_lxs_name | index | lxs | lxs | 72 MB | |
public | idx_lyp_id | index | lxs | lyp | 672 kB | |
public | pk_lxs_id | index | lxs | lxs | 21 MB | |
(3 rows)
lxsdb=> select oid,relname from pg_class where relname='lyp';
oid | relname
-------+---------
49211 | lyp
(1 row)
lxsdb=> select oid,relname,
lxsdb-> pg_indexes_size(49211),
lxsdb-> pg_indexes_size('lyp'),
lxsdb-> pg_indexes_size(49211)/1024 kb,
lxsdb-> pg_indexes_size('lyp')/1024 kb,
lxsdb-> pg_partition_indexes_size('lyp','lyp_p1'),
lxsdb-> pg_partition_indexes_size('lyp','lyp_p1'),
lxsdb-> pg_partition_indexes_size('lyp','lyp_p1'),
lxsdb-> pg_partition_indexes_size('lyp','lyp_p1')+pg_partition_indexes_size('lyp','lyp_p1')+pg_partition_indexes_size('lyp','lyp_p1'),
lxsdb-> (pg_partition_indexes_size('lyp','lyp_p1')+pg_partition_indexes_size('lyp','lyp_p1')+pg_partition_indexes_size('lyp','lyp_p1'))/1024 kb
lxsdb-> from pg_class where relname='lyp';
oid | relname | pg_indexes_size | pg_indexes_size | kb | kb | pg_partition_indexes_size | pg_partition_indexes_size | pg_partition_indexes_size | ?column? | kb
-------+---------+-----------------+-----------------+-----+-----+---------------------------+---------------------------+---------------------------+----------+-----
49211 | lyp | 688128 | 688128 | 672 | 672 | 245760 | 245760 | 245760 | 737280 | 720
(1 row)
lxsdb=>
pg_partition_size
lxsdb=> \df pg_partition_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+-------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_partition_size | bigint | oid, oid | normal | f | f | f
pg_catalog | pg_partition_size | bigint | text, text | normal | f | f | f
(2 rows)
lxsdb=>
查看分区表大小
lxsdb=> select pg_partition_size('lyp','lyp_p1');
pg_partition_size
-------------------
638976
(1 row)
lxsdb=> select pg_partition_size('lyp','lyp_p2');
pg_partition_size
-------------------
638976
(1 row)
lxsdb=> select pg_partition_size('lyp','lyp_p3');
pg_partition_size
-------------------
516096
(1 row)
lxsdb=>
pg_relation_size
lxsdb=> \df pg_relation_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_relation_size | bigint | regclass | normal | f | f | f
pg_catalog | pg_relation_size | bigint | regclass, text | normal | f | f | f
(2 rows)
lxsdb=>
查看对象大小
lxsdb=> \dt+ lxs
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+-------+----------------------------------+-------------
public | lxs | table | lxs | 58 MB | {orientation=row,compression=no} |
(1 row)
lxsdb=> \di+ pk_lxs_id
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+-----------+-------+-------+-------+-------+---------+-------------
public | pk_lxs_id | index | lxs | lxs | 21 MB | |
(1 row)
lxsdb=> \di+ idx_lxs_name
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+--------------+-------+-------+-------+-------+---------+-------------
public | idx_lxs_name | index | lxs | lxs | 72 MB | |
(1 row)
lxsdb=>
lxsdb=> select oid,relname,
lxsdb-> pg_relation_size(oid),
lxsdb-> pg_relation_size(49178),
lxsdb-> pg_relation_size('lxs'),
lxsdb-> pg_relation_size(49184),
lxsdb-> pg_relation_size('pk_lxs_id'),
lxsdb-> pg_relation_size(49186),
lxsdb-> pg_relation_size('idx_lxs_name')
lxsdb-> from pg_class where relname like '%lxs%';
oid | relname | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size
-------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------
49178 | lxs | 60678144 | 60678144 | 60678144 | 22487040 | 22487040 | 75431936 | 75431936
49184 | pk_lxs_id | 22487040 | 60678144 | 60678144 | 22487040 | 22487040 | 75431936 | 75431936
49186 | idx_lxs_name | 75431936 | 60678144 | 60678144 | 22487040 | 22487040 | 75431936 | 75431936
(3 rows)
lxsdb=>
pg_size_pretty
lxsdb=> \df pg_size_pretty
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+----------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_size_pretty | text | bigint | normal | f | f | f
pg_catalog | pg_size_pretty | text | numeric | normal | f | f | f
(2 rows)
lxsdb=>
格式化方式显示大小
lxsdb=> \dt+ lxs
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+-------+----------------------------------+-------------
public | lxs | table | lxs | 58 MB | {orientation=row,compression=no} |
(1 row)
lxsdb=> \di+ pk_lxs_id
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+-----------+-------+-------+-------+-------+---------+-------------
public | pk_lxs_id | index | lxs | lxs | 21 MB | |
(1 row)
lxsdb=> \di+ idx_lxs_name
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+--------------+-------+-------+-------+-------+---------+-------------
public | idx_lxs_name | index | lxs | lxs | 72 MB | |
(1 row)
lxsdb=>
lxsdb=> select oid,relname,
lxsdb-> pg_relation_size(oid),
lxsdb-> pg_relation_size(49178),
lxsdb-> pg_relation_size('lxs'),
lxsdb-> pg_relation_size(49184),
lxsdb-> pg_relation_size('pk_lxs_id'),
lxsdb-> pg_relation_size(49186),
lxsdb-> pg_relation_size('idx_lxs_name')
lxsdb-> from pg_class where relname like '%lxs%';
oid | relname | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size | pg_relation_size
-------+--------------+------------------+------------------+------------------+------------------+------------------+------------------+------------------
49178 | lxs | 60678144 | 60678144 | 60678144 | 22487040 | 22487040 | 75431936 | 75431936
49184 | pk_lxs_id | 22487040 | 60678144 | 60678144 | 22487040 | 22487040 | 75431936 | 75431936
49186 | idx_lxs_name | 75431936 | 60678144 | 60678144 | 22487040 | 22487040 | 75431936 | 75431936
(3 rows)
lxsdb=>
lxsdb=> select oid,relname,
lxsdb-> pg_size_pretty(pg_relation_size(oid)),
lxsdb-> pg_size_pretty(pg_relation_size(49178)),
lxsdb-> pg_size_pretty(pg_relation_size('lxs')),
lxsdb-> pg_size_pretty(pg_relation_size(49184)),
lxsdb-> pg_size_pretty(pg_relation_size('pk_lxs_id')),
lxsdb-> pg_size_pretty(pg_relation_size(49186)),
lxsdb-> pg_size_pretty(pg_relation_size('idx_lxs_name'))
lxsdb-> from pg_class where relname like '%lxs%';
oid | relname | pg_size_pretty | pg_size_pretty | pg_size_pretty | pg_size_pretty | pg_size_pretty | pg_size_pretty | pg_size_pretty
-------+--------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------
49178 | lxs | 58 MB | 58 MB | 58 MB | 21 MB | 21 MB | 72 MB | 72 MB
49184 | pk_lxs_id | 21 MB | 58 MB | 58 MB | 21 MB | 21 MB | 72 MB | 72 MB
49186 | idx_lxs_name | 72 MB | 58 MB | 58 MB | 21 MB | 21 MB | 72 MB | 72 MB
(3 rows)
lxsdb=>
pg_table_size
lxsdb=> \df pg_table_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+---------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_table_size | bigint | regclass | normal | f | f | f
(1 row)
lxsdb=>
查看表大小
lxsdb=> select oid,relname,
lxsdb-> pg_table_size(oid),
lxsdb-> pg_table_size(relname::regclass),
lxsdb-> pg_table_size('lxs'),
lxsdb-> pg_table_size(49178)
lxsdb-> from pg_class where relname ='lxs';
oid | relname | pg_table_size | pg_table_size | pg_table_size | pg_table_size
-------+---------+---------------+---------------+---------------+---------------
49178 | lxs | 60719104 | 60719104 | 60719104 | 60719104
(1 row)
lxsdb=>
pg_tablespace_size
lxsdb=> \df pg_tablespace_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+--------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_tablespace_size | bigint | name | normal | f | f | f
pg_catalog | pg_tablespace_size | bigint | oid | normal | f | f | f
(2 rows)
lxsdb=>
显示表空间大小
lxsdb=> select oid,spcname,
lxsdb-> pg_size_pretty(pg_tablespace_size(oid)),
lxsdb-> pg_size_pretty(pg_tablespace_size(spcname))
lxsdb-> from pg_tablespace;
oid | spcname | pg_size_pretty | pg_size_pretty
-------+------------+----------------+----------------
1663 | pg_default | 73 MB | 73 MB
1664 | pg_global | 513 MB | 513 MB
49172 | lxs_tbs | 166 MB | 166 MB
(3 rows)
lxsdb=>
pg_total_relation_size
lxsdb=> \df pg_total_relation_size
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+------------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | pg_total_relation_size | bigint | regclass | normal | f | f | f
(1 row)
lxsdb=>
指定表的总大小（包括表及表下的索引）
lxsdb=> \d lxs
Table "public.lxs"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(20) |
info | text |
Indexes:
"pk_lxs_id" PRIMARY KEY, btree (id) TABLESPACE lxs_tbs
"idx_lxs_name" btree (name) TABLESPACE lxs_tbs
lxsdb=> \dt+ lxs
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+-------+----------------------------------+-------------
public | lxs | table | lxs | 58 MB | {orientation=row,compression=no} |
(1 row)
lxsdb=> \di+ pk_lxs_id
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+-----------+-------+-------+-------+-------+---------+-------------
public | pk_lxs_id | index | lxs | lxs | 21 MB | |
(1 row)
lxsdb=> \di+ idx_lxs_name
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+--------------+-------+-------+-------+-------+---------+-------------
public | idx_lxs_name | index | lxs | lxs | 72 MB | |
(1 row)
lxsdb=>
lxsdb=> select oid,relname,
lxsdb-> pg_size_pretty(pg_total_relation_size(oid)),
lxsdb-> pg_size_pretty(pg_total_relation_size(relname::regclass))
lxsdb-> from pg_class where relname='lxs';
oid | relname | pg_size_pretty | pg_size_pretty
-------+---------+----------------+----------------
49178 | lxs | 151 MB | 151 MB
(1 row)
lxsdb=>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
