Chinaunix首页 | 论坛 | 博客
  • 博客访问: 55888
  • 博文数量: 18
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 200
  • 用 户 组: 普通用户
  • 注册时间: 2013-03-01 13:27
个人简介

学习是一种信仰

文章分类

全部博文(18)

文章存档

2016年(8)

2015年(8)

2013年(2)

我的朋友

分类: Mysql/postgreSQL

2016-04-14 18:18:27

查看一个模式下所有表的大小
select r.relname, pg_size_pretty(pg_relation_size(r.relid))                                                               
from pg_stat_user_tables r                       
where r.schemaname='public'                           
order by pg_relation_size(r.relid) desc;

查询表与索引的关系信息
SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    COUNT(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(CASE WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(CASE WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace 
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           MAX(CAST(indisunique AS INTEGER)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE 
     pg_namespace.nspname='public'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;

查看一个模式下所有表及索引的大小:
select r.relname, pg_size_pretty(pg_relation_size(r.relid)), i.indexrelname,pg_size_pretty(pg_relation_size(i.indexrelid))
from pg_stat_user_tables r,pg_stat_user_indexes i
where r.schemaname='public'  and r.relname=i.relname
order by pg_relation_size(r.relid) desc;

查询重复创建的索引:
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
       (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
       (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
    SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
    FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

索引使用及统计信息:
set search_path='public'
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

查看索引使用情况:
select schemaname||'.'||relname as tablename, schemaname||'.'||indexrelname as indexname,idx_scan,idx_tup_read,idx_tup_fetch 
from pg_stat_all_indexes 
where indexrelname not like '%pk%' and indexrelname not like '%PK%' and schemaname='public' order by idx_scan desc;

查看执行时间超过30s的事务或者SQL
SELECT datname,pid,xact_start,query_start,waiting,client_addr,state,query 
FROM pg_stat_activity 
where (now()-query_start>= '00:00:30' or  now()-xact_start>= '00:00:30') and state !='idle' order by query_start;

当前连接数:
select max_conn, now_conn, max_conn-now_conn rest_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;

select usename,client_addr,count(client_addr) 
from pg_stat_activity 
group by usename,client_addr 
order by count(client_addr) desc;

查看表注释信息:
select t.schemaname,t.relname,description 
from pg_description d,pg_class c,pg_stat_all_tables t 
where d.objoid=c.oid and objsubid=0 and t.relname=c.relname and t.schemaname='public';

查看字段注释信息:
select table_schema,table_name,column_name,data_type,description 
from pg_class c,pg_description d,pg_namespace n,information_schema.columns i 
where d.objoid = c.oid and i.table_name=c.relname and i.ordinal_position=d.objsubid and c.relnamespace=n.oid and table_schema = 'public';

合并多行记录:
select c.id,array_to_string(array(
      select d.value 
      from xiaoya_crm.dict_consultant_info_temp d 
      where c.id=d.foreign_id),',') as value 
from xiaoya_crm.consultant_info_temp c 
order by c.id;

监控锁等进程:
select distinct locker.pid as locker_pid,
       locked.pid as locked_pid,
       locker_act.client_addr as locker_addr,
       locked_act.client_addr as locked_addr,
       locker_act.usename as locker_username,
       locked_act.usename as locked_username,
       locker.mode as locker_mode,
       locker.locktype as locker_locktype,
       locker_act.usename as locker_user,
       locked_act.usename as locker_user,
       locker_act.query_start as locker_query_start,
       locked_act.query_start as locked_query_start,
       locker_act.query as locker_query,
       locked_act.query as locked_query
from pg_locks locked,
     pg_locks locker,
     pg_stat_activity locked_act,
     pg_stat_activity locker_act
where locker.granted=true
and locked.granted=false
and locked.pid=locked_act.pid
and locker.pid=locker_act.pid
and locker_act.query not like '%select distinct locker.pid %'
and locker.pid <> locked.pid
and locker.mode not like 'AccessShareLock' and locker.mode not like 'ExclusiveLock'
order by locker_act.query_start asc limit 10;

阅读(1580) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~