Greenplum小把戏 - 几个常用数据库对象大小查询SQL

为什么80%的码农都做不了架构师？>>>   

1.查询表大小：

postgres=# \d
                         List of relations
 Schema |       Name        | Type  | Owner  |       Storage
--------+-------------------+-------+--------+----------------------
 public | compress_column_t | table | hgimdb | append only columnar
 public | hgimdbtab         | table | hgimdb | heap
 public | hgimdbtab_ext     | table | hgimdb | external
 public | hgimdbtab_load_t  | table | hgimdb | heap
 public | trips_column      | table | hgimdb | append only columnar
(5 rows)

postgres=# select pg_size_pretty(pg_relation_size('hgimdbtab_load_t'));
 pg_size_pretty
----------------
 32 GB
(1 row)

postgres=#

2.查询模式大小：

postgres=# select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB"                                                                     from pg_tables where schemaname='public' group by 1;
 schemaname | Size_MB
------------+---------
 public     |   33906
(1 row)

3.查询数据库大小：

postgres=# select pg_size_pretty(pg_database_size('postgres'));
 pg_size_pretty
----------------
 33 GB
(1 row)

4.查询所有数据库大小：

postgres=# \l
                 List of databases
   Name    | Owner  | Encoding | Access privileges
-----------+--------+----------+-------------------
 postgres  | hgimdb | UTF8     |
 template0 | hgimdb | UTF8     | =c/hgimdb
                               : hgimdb=CTc/hgimdb
 template1 | hgimdb | UTF8     | =c/hgimdb
                               : hgimdb=CTc/hgimdb
 test      | hgimdb | UTF8     |
 tpch      | hgimdb | UTF8     |
(5 rows)

postgres=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
  datname  | pg_size_pretty
-----------+----------------
 template1 | 94 MB
 template0 | 93 MB
 postgres  | 33 GB
 tpch      | 93 MB
 test      | 93 MB
(5 rows)

postgres=# select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database;
 sodddatname | sizeingb
-------------+----------
 tpch        |        0
 test        |        0
(2 rows)

postgres=#

5.查询分区表的总大小（包括索引和分区）：

postgres=# select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || partitiontablename))/1024/1024) "MB"                                                    from pg_partitions where tablename='compress_column_t' group by 1,2;
 schemaname | tablename | MB
------------+-----------+----
(0 rows)

 

转载于:https://my.oschina.net/javacy/blog/2999937