查看表的大小

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
    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
    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'
            AND indisunique = TRUE;

查询重复创建的索引

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;

查看执行时间超过30s的事务或者SQL

SELECT datname,pid,xact_start,query_start,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.relname,
  col_description (a.attrelid, a.attnum) AS COMMENT,
  format_type (a.atttypid, a.atttypmod) AS TYPE,
  a.attname AS NAME,
  a.attnotnull AS notnull 
FROM
  pg_class AS c,
  pg_attribute AS a 
WHERE a.attrelid = c.oid 
  AND a.attnum > 0 
  AND a.attname in('contact_company_id','contact_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;