Greenplum常用SQL总结

查看表在各个segment的数据分布

=# SELECT gp_segment_id,count(*) FROM table_name GROUP BY gp_segment_id;

查看分区表在各个segment的数据分布

=# SELECT gp_segment_id,tableoid::regclass,count(*) FROM partition_tablename GROUP BY 1,2 ORDER BY 1,2;

查看磁盘可用空间

=# SELECT dfsegment, dfhostname, dfdevice, pg_size_pretty(dfspace) AS dfspace 
   FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;
 dfsegment | dfhostname  |         dfdevice          | dfspace
-----------+-------------+---------------------------+---------
         0 |  minion_187 |  /dev/mapper/ncl-paasdata | 842 MB
         1 |  minion_187 |  /dev/mapper/ncl-paasdata | 842 MB
         2 |  minion_187 |  /dev/mapper/ncl-paasdata | 842 MB
         3 |  minion_187 |  /dev/mapper/ncl-paasdata | 842 MB
         4 |  minion_188 |  /dev/mapper/ncl-paasdata | 844 MB
         5 |  minion_188 |  /dev/mapper/ncl-paasdata | 844 MB
         6 |  minion_188 |  /dev/mapper/ncl-paasdata | 844 MB
         7 |  minion_188 |  /dev/mapper/ncl-paasdata | 844 MB
(8 rows)

查看所有数据库占用空间大小

=# SELECT sodddatname AS db_name, pg_size_pretty(sodddatsize) AS db_size
   FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;
               db_name               | db_size
-------------------------------------+---------
 db_21c0944e79f2488bb610d58c89f1809b | 51 GB
 db_30639a04dc4b4b7c891667d7031cc989 | 46 GB
 db_9bd86a7457fc445c9b3dbcb39a4eed30 | 148 MB
 db_e0ecc09cf5db4dcfb1460c3ff06d1b0d | 146 MB
 dbplat                              | 147 MB
(5 rows)

查看表占用空间大小

=# SELECT relname AS name, sotdsize AS size, sotdtoastsize AS toast, sotdadditionalsize AS other
   FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class
   WHERE sotd.sotdoid=pg_class.oid ORDER BY relname;
           name            |   size   | toast  | other
---------------------------+----------+--------+-------
 comm_ne_test_mac          |        0 |      0 |     0
 generationstrategytable   |   262144 |      0 |     0
 old_t_npa_ctn_moc_busi    | 62816256 | 294912 |     0
 old_t_npa_ctn_moc_card    | 91947008 | 294912 |     0
 old_t_npa_ctn_moc_ethserv | 43483136 | 294912 |     0
(5 rows)

查看索引占用空间大小

=# SELECT soisize, relname as indexname
   FROM pg_class, gp_toolkit.gp_size_of_index
   WHERE pg_class.oid=gp_size_of_index.soioid AND pg_class.relkind='i';
 soisize |           indexname
---------+-------------------------------
  557056 | t_npa_mark_style_pkey
  557056 | t_npa_mark_style_content_pkey
  294912 | t_npa_operator_log_pkey
  557056 | t_npa_periodmark_pkey
  557056 | t_npa_drill_content_pkey
(5 rows)

建表模板

drop table if exists rpt.rpt_bill_m ; 

create table rpt.rpt_bill_m (

user_id SERIAL, ----自增序列

acct_month varchar(6),

bill_fee numeric(16,2) ,

user_info text 

)

WITH (

appendonly=true, -- 对于压缩表跟列存储来说，前提必须是appendonly表

orientation=column,-- 列存 row

compresstype=zlib,-- 压缩格式 --QUICKLZ

COMPRESSLEVEL=5, -- 压缩等级 0--9 --1 压缩低查询快

OIDS=FALSE

)

DISTRIBUTED BY (user_id) -- 分布键

PARTITION BY LIST("acct_month") -- 分区键

(

PARTITION p_201810 VALUES ('201810'),

PARTITION p_201811 VALUES ('201811'),

PARTITION p_201812 VALUES ('201812'),

default partition other --容错没有分区键在此表

/* PARTITION p_20170801 START('20170801'::DATE) END ('20170831'::DATE)

EVERY ('1 month'::INTERVAL) */

);

comment on column rpt.rpt_bill_m.user_info is '员工备注信息'; -- 注解