greenplum常用SQL

获取greenplum中的所有储存过程以及函数

select
	n.nspname as schema_name,
	p.proname as procedure_name,
	p.prosrc as pro_src
from
	pg_proc as p
join pg_namespace as n on
	p.pronamespace = n.oid
where
	n.nspname not like 'pg_%'
	and n.nspname != 'information_schema'
	and n.nspname not like 'gp_%'
	and n.nspname != 'session_state'
order by
	n.nspname,
	p.proname

pg_proc系统目录表存储关于函数（或过程）的信息，包括所有的内建函数以及由CREATE FUNCTION定义的函数。该表也包含了聚集和窗口函数以及普通函数的数据。 

greenplum每个会话占用内存情况

psql -d testdb -c "CREATE EXTENSION gp_internal_tools;" select * from session_state.session_level_memory_consumption

查看postgres正在执行的sql｜关闭指定查询进程

select
	procpid,
	start,
	now() - start as lap,
	current_query
from
	(
	select
		backendid,
		pg_stat_get_backend_pid (S.backendid) as procpid,
		pg_stat_get_backend_activity_start (S.backendid) as start,
		pg_stat_get_backend_activity (S.backendid) as current_query
	from
		(
		select
			pg_stat_get_backend_idset () as backendid ) as S ) as S
order by
	lap desc;

关闭指定postgres查询进程：

SELECT pg_cancel_backend(27087);
SELECT pg_terminate_backend(27087);

查询各表记录数

select
	relname as 表名,
	reltuples as 记录数
from
	pg_class
where
	relkind = 'r'
	and relnamespace = (
	select
		oid
	from
		pg_namespace
	where
		nspname = 'xxxxx')
order by
	记录数 desc;

查询各表占用空间大小

select
	schemaname as schema_name,
	relname as table_name,
	pg_size_pretty(pg_total_relation_size(relid)) as size
from
	pg_catalog.pg_statio_user_tables
order by
	pg_total_relation_size(relid) desc;

获取数据库所有表信息（schema 名称，表名称，表备注，字段数量，大小）

select
	n.nspname as schema_name,
	c.relname as table_name,
	obj_description(c.oid) as table_comment,
	COUNT(a.attname) as column_count,
	pg_size_pretty(pg_total_relation_size(c.oid)) as table_size,
	case
		when c.relkind = 'r' then '数据表'
		when c.relkind = 'v' then '视图'
		when c.relkind = 'm' then '物化视图'
	end as table_type,
	c.reltuples::int8 as row_count
from
	(
	select
		relname,
		oid,
		reltuples,
		relkind,
		relnamespace
	from
		pg_class
	where
		relkind in ('r', 'v', 'm')) as c
join pg_namespace as n on
	c.relnamespace = n.oid
left join pg_attribute as a on
	c.oid = a.attrelid
	and a.attnum > 0
group by
	n.nspname,
	c.relname,
	c.oid,
	c.relkind,
	c.reltuples
order by
	n.nspname,
	c.relname

获取所有表/视图的所有字段信息

select
	n.nspname as schema_name,
	c.relname as table_name,
	a.attname as column_name,
	format_type(a.atttypid, a.atttypmod) as column_type,
	d.description as column_comment,
	case
		when c.relkind = 'r' then '数据表'
		when c.relkind = 'v' then '视图'
		when c.relkind = 'm' then '物化视图'
	end as table_type
from
	pg_class as c
join pg_attribute as a on
	c.oid = a.attrelid
join pg_namespace as n on
	c.relnamespace = n.oid
left join pg_description as d on
	c.oid = d.objoid
	and a.attnum = d.objsubid
where
	c.relkind in ('r', 'v', 'm')
	-- 查询普通表/视图/物化视图   
	and a.attnum > 0
	-- 排除系统列   -- 
	and n.nspname = 'your_schema_name'
	-- 替换为你要查询的 schema 名称
order by
	n.nspname,
	c.relname,
	a.attnum

获取所有索引信息

select
	schemaname as schema_name,
	tablename as table_name,
	indexname as index_name,
	indexdef as index_def
from
	pg_indexes
where
	schemaname not like 'pg_%'
	and schemaname != 'information_schema'

SELECT
     pid,
    state,
    wait_event_type,
    wait_event,
    query
FROM gp_dist_random('pg_stat_activity')
WHERE state <> 'idle';


SELECT
    pid,
    state,
    wait_event,
    wait_event_type,
    query_start,
    now()-query_start AS runtime,
    query
FROM pg_stat_activity
WHERE usename='xxx'
AND state <> 'idle';