postgres库常用sql

pg库查看索引：

 SELECT
 A.SCHEMANAME,
 A.TABLENAME,
 A.INDEXNAME,
 A.TABLESPACE,
 A.INDEXDEF,
 B.AMNAME,
 C.INDEXRELID,
 C.INDNATTS,
 C.INDISUNIQUE,
 C.INDISPRIMARY,
 C.INDISCLUSTERED,
 D.DESCRIPTION
 FROM
 PG_AMB
 LEFTJOINPG_CLASSFONB.OID=F.RELAM
 LEFTJOINPG_STAT_ALL_INDEXESEONF.OID=E.INDEXRELID
 LEFTJOINPG_INDEXCONE.INDEXRELID=C.INDEXRELID
 LEFTOUTERJOINPG_DESCRIPTIONDONC.INDEXRELID=D.OBJOID,
 PG_INDEXESA
 WHERE A.SCHEMANAME=E.SCHEMANAMEANDA.TABLENAME=E.RELNAMEANDA.INDEXNAME=E.INDEXRELNAME
 ANDE.SCHEMANAME='public'ANDE.RELNAME='table_name'

pg库查看所有表

select * from pg_tables ;

pg库查看所有角色

SELECT * FROM pg_roles;  

Pg库查询今天、昨天、上周对比

按小时：
select tab1.paic_time1 as label,coalesce(tab1.sum1,0) as value,'今天' as des from 
(
select date_trunc('hour',pa_time1) as paic_time1,
       count(*) sum1
  from (select t.cdate as pa_time1
          from t_query_log t
         where t.cdate >= current_date) pa_requery
group by date_trunc('hour',pa_time1) 
) tab1
union all
select tab2.paic_time1 as label,coalesce(tab2.sum1,0) as value,'昨天' as des from 
(
select date_trunc('hour',pa_time1) as paic_time1,
       count(*) sum1
  from (select t.cdate as pa_time1
          from t_query_log t
         where t.cdate >= CURRENT_DATE - interval '1 day' and t.cdate < CURRENT_DATE) pa_requery
group by date_trunc('hour',pa_time1) 
) tab2
union all
select tab3.paic_time1 as label,coalesce(tab3.sum1,0) as value,'上周' as des from 
(
select date_trunc('hour',pa_time1) as paic_time1,
       count(*) sum1
  from (select t.cdate as pa_time1
          from t_query_log t
         where t.cdate >= CURRENT_DATE - interval '7 days' and t.cdate < CURRENT_DATE - interval '6 days' ) pa_requery
group by date_trunc('hour',pa_time1) 
) tab3

//

按十分钟
select tab1.paic_time1 as label,coalesce(tab1.sum1,0) as value,'今天' as des from 
(
select to_timestamp(substring(to_char(pa_time1,'yyyy-mm-dd hh24:mi') from 1 for 15) || '0','yyyy-mm-dd hh24:mi') as paic_time1,
       count(*) sum1
  from (select t.cdate as pa_time1
          from t_query_log t
         where t.cdate >= current_date) pa_requery
group by to_timestamp(substring(to_char(pa_time1,'yyyy-mm-dd hh24:mi') from 1 for 15) || '0','yyyy-mm-dd hh24:mi')
) tab1
union all
select tab2.paic_time1 as label,coalesce(tab2.sum1,0) as value,'昨天' as des from 
(
select to_timestamp(substring(to_char(pa_time1,'yyyy-mm-dd hh24:mi') from 1 for 15) || '0','yyyy-mm-dd hh24:mi') as paic_time1,
       count(*) sum1
  from (select t.cdate as pa_time1
          from t_query_log t
         where t.cdate >= CURRENT_DATE - interval '1 day' and t.cdate < CURRENT_DATE) pa_requery
group by to_timestamp(substring(to_char(pa_time1,'yyyy-mm-dd hh24:mi') from 1 for 15) || '0','yyyy-mm-dd hh24:mi')
) tab2
union all
select tab3.paic_time1 as label,coalesce(tab3.sum1,0) as value,'上周' as des from 
(
select to_timestamp(substring(to_char(pa_time1,'yyyy-mm-dd hh24:mi') from 1 for 15) || '0','yyyy-mm-dd hh24:mi') as paic_time1,
       count(*) sum1
  from (select t.cdate as pa_time1
          from t_query_log t
         where t.cdate >= CURRENT_DATE - interval '7 days' and t.cdate < CURRENT_DATE - interval '6 days' ) pa_requery
group by to_timestamp(substring(to_char(pa_time1,'yyyy-mm-dd hh24:mi') from 1 for 15) || '0','yyyy-mm-dd hh24:mi')
) tab3

利用sql查询数据库查看当前慢SQL(超过5秒)：

select * from pg_stat_activity where state<>'idle' and (backend_xid is not null or backend_xmin is not null)  and now()-query_start > interval '5 s' order by query_start ;

对于like语句的查询，在text字段上加专门的索引：
CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);