PostgreSQL 数据库统计信息查看

Table 9-73. Database Object Size Functions

Name	Return Type	Description
pg_column_size(any)	int	Number of bytes used to store a particular value (possibly compressed)
pg_database_size(oid)	bigint	Disk space used by the database with the specified OID
pg_database_size(name)	bigint	Disk space used by the database with the specified name
pg_indexes_size(regclass)	bigint	Total disk space used by indexes attached to the specified table
pg_relation_size(relation regclass, fork text)	bigint	Disk space used by the specified fork (‘main’, ‘fsm’, ‘vm’, or ‘init’) of the specified table or index
pg_relation_size(relation regclass)	bigint	Shorthand for pg_relation_size(..., 'main')
pg_size_pretty(bigint)	text	Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric)	text	Converts a size in bytes expressed as a numeric value into a human-readable format with size units
pg_table_size(regclass)	bigint	Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)
pg_tablespace_size(oid)	bigint	Disk space used by the tablespace with the specified OID
pg_tablespace_size(name)	bigint	Disk space used by the tablespace with the specified name
pg_total_relation_size(regclass)	bigint	Total disk space used by the specified table, including all indexes and TOAST data

所以，比如要查看 test 数据库的大小，则输入：

select pg_size_pretty(pg_database_size('test'));

POSTGRESQL 查看数据库 数据表大小

1、查看数据库大小：

select pg_database_size('log_analysis');

 pg_database_size 
------------------
         23799992
(1 row)

2、select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

log_analysis=# select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; 

   datname    |  size   
--------------+---------
 template1    | 6594 kB
 template0    | 6457 kB
 postgres     | 6586 kB
 b2c_product  | 27 GB
 spider       | 11 MB
 crm          | 54 MB
 log_analysis | 23 MB
(7 rows)

log_analysis=# 

3、按顺序查看索引

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

log_analysis=# 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 
------------------------------------------------------------+----------------
 pk_dim_sight_area                                          | 184 kB
 idx_area_dim_sight_area                                    | 184 kB
 idx_city_dim_sight_area                                    | 184 kB
 idx_country_dim_sight_area                                 | 184 kB
 idx_region_dim_sight_area                                  | 184 kB
 pk_dim_page_type                                           | 8192 bytes
 cpc_supplier_sight_daily_pkey                              | 0 bytes

4、查看所有表的大小

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

log_analysis=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

                    relname                     | pg_size_pretty 
------------------------------------------------+----------------
 dim_sight_area                                 | 184 kB
 dim_page_type                                  | 8192 bytes
 wirelessapi_log_2014_08_08                     | 0 bytes
 trace_log_2014_08_04                           | 0 bytes
 dm_mobile                                      | 0 bytes
 trace_log_2014_07_14                           | 0 bytes