hivefans

pg_table

Nov 23rd, 2020
986
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 查看postgresql 数据库 表 大小
  2. 查看数据库大小
  3. 查看david 数据库大小
  4. david=# select pg_database_size('david');
  5.  pg_database_size
  6. ------------------
  7.         190534776
  8.  
  9. 查看所有数据库大小
  10. david=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;                
  11.   datname  |    size    
  12. -----------+-------------
  13.  template0 |     6513156
  14.  postgres  |     6657144
  15.  jboss     |     6521348
  16.  bugs      |     6521348
  17.  david     |   190534776
  18.  BMCV3     | 28147135608
  19.  mydb      |    10990712
  20.  template1 |     6521348
  21. (8 rows)
  22.  
  23. 以人性化的方式显示大小
  24. select pg_size_pretty(pg_database_size('david'));
  25.  pg_size_pretty
  26. ----------------
  27.  182 MB
  28. (1 row)
  29.  
  30. 查看单索引大小
  31. select pg_relation_size('idx_test');
  32.  pg_relation_size
  33. ------------------
  34.          41238528
  35. (1 row)
  36.  
  37. david=# select pg_size_pretty(pg_relation_size('idx_test'));
  38.  pg_size_pretty
  39. ----------------
  40.  39 MB
  41. (1 row)
  42. select pg_size_pretty(pg_relation_size('idx_join_date_test'));
  43.  pg_size_pretty
  44. ----------------
  45.  39 MB
  46. (1 row)
  47.  
  48. 查看指定表中所有索引大小
  49. select pg_indexes_size('test');                  
  50.  pg_indexes_size
  51. -----------------
  52.         82477056
  53.  
  54.  select pg_size_pretty(pg_indexes_size('test'));
  55.  pg_size_pretty
  56. ----------------
  57.  79 MB
  58. (1 row)
  59.  
  60. idx_test 和idx_join_date_test 两个索引大小加起来差不多等于上面pg_indexes_size() 查询出来的索引大小。
  61.  
  62. 查看指定schema 里所有的索引大小,按从大到小的顺序排列。
  63. select * from pg_namespace;
  64.       nspname       | nspowner |               nspacl                
  65. --------------------+----------+-------------------------------------
  66.  pg_toast           |       10 |
  67.  pg_temp_1          |       10 |
  68.  pg_toast_temp_1    |       10 |
  69.  pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  70.  information_schema |       10 | {postgres=UC/postgres,=U/postgres}
  71.  public             |       10 | {postgres=UC/postgres,=UC/postgres}
  72. (6 rows)
  73.  
  74. select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
  75.          indexrelname          | pg_size_pretty
  76. -------------------------------+----------------
  77.  idx_join_date_test            | 91 MB
  78.  idx_test                      | 91 MB
  79.  testtable_idx                 | 1424 kB
  80.  city_pkey                     | 256 kB
  81.  city11                        | 256 kB
  82.  countrylanguage_pkey          | 56 kB
  83.  sale_pkey                     | 8192 bytes
  84.  track_pkey                    | 8192 bytes
  85.  tbl_partition_201211_joindate | 8192 bytes
  86.  tbl_partition_201212_joindate | 8192 bytes
  87.  tbl_partition_201301_joindate | 8192 bytes
  88.  tbl_partition_201302_joindate | 8192 bytes
  89.  tbl_partition_201303_joindate | 8192 bytes
  90.  customer_pkey                 | 8192 bytes
  91.  album_pkey                    | 8192 bytes
  92.  item_pkey                     | 8192 bytes
  93.  tbl_partition_201304_joindate | 8192 bytes
  94.  tbl_partition_201307_joindate | 8192 bytes
  95.  tbl_partition_201305_joindate | 0 bytes
  96.  tbl_partition_201306_joindate | 0 bytes
  97. (20 rows)
  98.  
  99. 查看指定表大小
  100. select pg_relation_size('test');                
  101.  pg_relation_size
  102. ------------------
  103.          95748096
  104. (1 row)
  105.  
  106. david=# select pg_size_pretty(pg_relation_size('test'));
  107.  pg_size_pretty
  108. ----------------
  109.  91 MB
  110. (1 row)
  111.  
  112. 使用pg_table_size() 函数查看
  113. david=# select pg_table_size('test');                  
  114.  pg_table_size
  115. ---------------
  116.       95789056
  117. (1 row)
  118.  
  119. david=# select pg_size_pretty(pg_table_size('test'));  
  120.  pg_size_pretty
  121. ----------------
  122.  91 MB
  123. (1 row)
  124.  
  125. 查看指定表的总大小
  126. david=# select pg_total_relation_size('test');      
  127.  pg_total_relation_size
  128. ------------------------
  129.               178266112
  130. (1 row)
  131.  
  132. david=# select pg_size_pretty(pg_total_relation_size('test'));
  133.  pg_size_pretty
  134. ----------------
  135.  170 MB
  136. (1 row)
  137.  
  138. 查看指定schema 里所有的表大小,按从大到小的顺序排列。
  139. david=# select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
  140.             relname            | pg_size_pretty
  141. -------------------------------+----------------
  142.  test                          | 91 MB
  143.  testtable                     | 1424 kB
  144.  city                          | 256 kB
  145.  countrylanguage               | 56 kB
  146.  country                       | 40 kB
  147.  testcount                     | 8192 bytes
  148.  tbl_partition_201302          | 8192 bytes
  149.  tbl_partition_201303          | 8192 bytes
  150.  person                        | 8192 bytes
  151.  customer                      | 8192 bytes
  152.  american_state                | 8192 bytes
  153.  tbl_david                     | 8192 bytes
  154.  emp                           | 8192 bytes
  155.  tbl_partition_201212          | 8192 bytes
  156.  tbl_partition_201304          | 8192 bytes
  157.  tbl_partition_error_join_date | 8192 bytes
  158.  tbl_partition_201211          | 8192 bytes
  159.  album                         | 8192 bytes
  160.  tbl_partition_201307          | 8192 bytes
  161.  tbl_xulie                     | 8192 bytes
  162.  tbl_partition_201301          | 8192 bytes
  163.  sale                          | 8192 bytes
  164.  item                          | 8192 bytes
  165.  track                         | 8192 bytes
  166.  tbl_partition_201306          | 0 bytes
  167.  tbl_partition                 | 0 bytes
  168.  tbl_partition_201305          | 0 bytes
  169.  person2                       | 0 bytes
  170. (28 rows)
  171.  
  172. 查看表空间大小
  173. david=# select spcname from pg_tablespace;
  174.   spcname  
  175. ------------
  176.  pg_default
  177.  pg_global
  178. (2 rows)
  179.  
  180. david=# select pg_tablespace_size('pg_default');                
  181.  pg_tablespace_size
  182. --------------------
  183.         28381579760
  184. (1 row)
  185.  
  186. david=# select pg_size_pretty(pg_tablespace_size('pg_default'));
  187.  pg_size_pretty
  188. ----------------
  189.  26 GB
  190. (1 row)
  191.  
  192. 另一种查看方法:
  193. david=# select pg_tablespace_size('pg_default')/1024/1024 as "SIZE M";    
  194.  SIZE M
  195. --------
  196.   27066
  197. (1 row)
  198.  
  199. david=# select pg_tablespace_size('pg_default')/1024/1024/1024 as "SIZE G";
  200.  SIZE G
  201. --------
  202.      26
  203. (1 row)
  204.  
  205.  
RAW Paste Data