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);