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)
PostgreSQL集群篇——常用的运维SQL
