POSTGRESQL 查看数据库数据表大小

浏览数：36 / 时间：2015年06月12日

1、查看数据库大小：

select pg_database_size(‘log_analysis‘);

***(Single step mode: verify command)*******************************************
select pg_database_size(‘log_analysis‘);
***(press return to proceed or enter x and return to cancel)********************

 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; 
***(Single step mode: verify command)*******************************************
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
***(press return to proceed or enter x and return to cancel)********************

   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;
***(Single step mode: verify command)*******************************************
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname=‘public‘ order by pg_relation_size(relid) desc;
***(press return to proceed or enter x and return to cancel)********************

                        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;
***(Single step mode: verify command)*******************************************
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=‘public‘ order by pg_relation_size(relid) desc;
***(press return to proceed or enter x and return to cancel)********************

                    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