查看表的大小
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;
