1.查看对象大小(表、索引、数据库等)
2.查看用户(非系统)表和索引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)AI 代码解读
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)AI 代码解读
3.查看表分区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)AI 代码解读
4.查看Distributed keyselect 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)AI 代码解读
5.查看当前存活的查询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)AI 代码解读
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)AI 代码解读
