POSTGRESQL 查看資料庫 資料表大小

標籤：des   style   blog   color   os   io   資料   ar   

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