Deepgreen(Greenplum) DBA常用运维SQL

1.查看对象大小(表、索引、数据库等)

select pg_size_pretty(pg_relation_size(’$schema.$table’));
示例：
tpch=# select pg_size_pretty(pg_relation_size('public.customer'));
 pg_size_pretty
----------------
 122 MB
(1 row)

2.查看用户(非系统)表和索引

tpch=# select * from pg_stat_user_tables;
 relid | schemaname |       relname        | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | last_vacuum | last_autovacuum | last_analyze
| last_autoanalyze
-------+------------+----------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+-------------+-----------------+--------------
+------------------
 17327 | public     | partsupp             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 17294 | public     | customer             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 17158 | public     | part                 |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17259 | public     | supplier             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 16633 | gp_toolkit | gp_disk_free         |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17394 | public     | lineitem             |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 17361 | public     | orders               |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
 16439 | gp_toolkit | __gp_masterid        |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 49164 | public     | number_xdrive        |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17193 | public     | region               |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 49215 | public     | number_gpfdist       |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 16494 | gp_toolkit | __gp_log_master_ext  |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 40972 | public     | number               |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 16413 | gp_toolkit | __gp_localid         |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 16468 | gp_toolkit | __gp_log_segment_ext |        0 |            0 |          |               |         0 |         0 |         0 |             |                 |
|
 17226 | public     | nation               |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             |                 |
|
(16 rows)

tpch=# select * from pg_stat_user_indexes;
 relid | indexrelid | schemaname | relname  |      indexrelname       | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+----------+-------------------------+----------+--------------+---------------
 17259 |      17602 | public     | supplier | idx_supplier_nation_key |        0 |            0 |             0
 17327 |      17623 | public     | partsupp | idx_partsupp_partkey    |        0 |            0 |             0
 17327 |      17644 | public     | partsupp | idx_partsupp_suppkey    |        0 |            0 |             0
 17294 |      17663 | public     | customer | idx_customer_nationkey  |        0 |            0 |             0
 17361 |      17684 | public     | orders   | idx_orders_custkey      |        0 |            0 |             0
 17394 |      17705 | public     | lineitem | idx_lineitem_orderkey   |        0 |            0 |             0
 17394 |      17726 | public     | lineitem | idx_lineitem_part_supp  |        0 |            0 |             0
 17226 |      17745 | public     | nation   | idx_nation_regionkey    |        0 |            0 |             0
 17394 |      17766 | public     | lineitem | idx_lineitem_shipdate   |        0 |            0 |             0
 17361 |      17785 | public     | orders   | idx_orders_orderdate    |        0 |            0 |             0
(10 rows)

3.查看表分区

select b.nspname||'.'||a.relname as tablename, d.parname as partname from pg_class a, pg_namespace b, pg_partition c,pg_partition_rule d where a.relnamespace = b.oid and b.nspname = 'public' and a.relname = 'customer' and a.oid = c.parrelid and c.oid = d.paroid order by parname;
 tablename | partname
-----------+----------
(0 rows)

4.查看Distributed key

tpch=# select b.attname from pg_class a, pg_attribute b, pg_type c, gp_distribution_policy d, pg_namespace e where d.localoid = a.oid and a.relnamespace = e.oid and e.nspname = 'public' and a.relname= 'customer' and a.oid = b.attrelid and b.atttypid = c.oid and b.attnum > 0 and b.attnum = any(d.attrnums) order by attnum;
  attname
-----------
 c_custkey
(1 row)

5.查看当前存活的查询

select procpid as pid, sess_id as session, usename as user, current_query as query, waiting,date_trunc('second',query_start) as start_time, client_addr as useraddr from pg_stat_activity where datname ='$PGDATABADE'
and current_query not like '%from pg_stat_activity%where datname =%' order by start_time;

示例：
tpch=# select procpid as pid, sess_id as session, usename as user, current_query as query, waiting,date_trunc('second',query_start) as start_time, client_addr as useraddr from pg_stat_activity where datname ='tpch'
tpch-# and current_query not like '%from pg_stat_activity%where datname =%' order by start_time;
 pid | session | user | query | waiting | start_time | useraddr
-----+---------+------+-------+---------+------------+----------
(0 rows)