greenplum查询表结构java_Greenplum小把戏 - 几个常用数据库对象大小查询SQL

62 阅读 0 评论 41 点赞

我是靠谱客的博主欣喜草丛，最近开发中收集的这篇文章主要介绍greenplum查询表结构java_Greenplum小把戏 - 几个常用数据库对象大小查询SQL，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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)