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)
原文链接
