1、重置表索引

REINDEX INDEX index_name;//重置单个索引
REINDEX TABLE table_name;//重置整个表的索引

2、查询父表的分区表

select c.relname from pg_class c join pg_inherits pi on pi.inhrelid=c.oid join pg_class c2 on c2.oid=pi.inhparent where c2.relname='父表名'

3、查询最大连接数、当前连接数和剩余连接数

select max_conn,now_conn,max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,select count(*) from pg_stat_activity) as now_conn from pg_settings where name='max_connections') t

4、查询表索引

select relname,indexrelname,idx_scan,idx_tup_read,idx_tup_fetch from pg_stat_user_indexes where relname='res_tree_main' order by idx_scan,idx_tup_read,idx_tup_fetch

5.查询某模式下的所有表名

select tablename from pg_tables where schemaname='模式名称' order by tablename;

6.表锁查询

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,T.QUERY_START
from PG_STAT_ACTIVITY T
where T.WAIT_EVENT_TYPE = 'Lock' and query like '%faultret%'

7.表解锁

select PG_CANCEL_BACKEND('147604');

8.模糊查询索引

--先声明
　create extension pg_trgm;
--对表字段添加索引
　CREATE INDEX idx_res_geoloc_chs_name ON res_geoloc_chs USING GIN(name gin_trgm_ops);

10.列转行

使用函数string_agg (relative_label_content, ',') as relative_label_content

样例：

　　select cwu.username,cwu.password,string_agg(cwr.name,',') rolename from cnosc_wfw_user cwu left join cnosc_wfw_userrole ur on cwu.id=ur.userid left join cnosc_wfw_role cwr on ur.roleid=cwr.id group by cwu.username,cwu.password