pg_stat_activity
最近发现系统运行缓慢，查询数据老是超时，于是排查下pg_stat_activity 系统表，看看有没有耗时的查询sql
SELECT pid, state, query, query_start, backend_type FROM pg_stat_activity
WHERE state = 'active' AND query LIKE '%SELECT%'
--and now()-query_start > interval '1 s'
and query_start > now() - interval '5 minutes' order by query_start;
非空闲的：<> 表示不等于，idle表示空闲的
state <> 'idle'
非空闲的，也可以理解为活跃的状态
state = 'active'
如果有非常耗时的sql，就试着执行下Explain解释语句，分析下sql耗时原因
复杂 pg_stat_activity
SELECTpid,datname,usename,client_addr,application_name,STATE,backend_start,xact_start,xact_stay,query_start,query_stay,REPLACE ( query, chr( 10 ), ' ' ) AS query
FROM(SELECTpgsa.pid AS pid,pgsa.datname AS datname,pgsa.usename AS usename,pgsa.client_addr client_addr,pgsa.application_name AS application_name,pgsa.STATE AS STATE,pgsa.backend_start AS backend_start,pgsa.xact_start AS xact_start,EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,pgsa.query_start AS query_start,EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,pgsa.query AS query FROMpg_stat_activity AS pgsa WHEREpgsa.STATE != 'idle' AND pgsa.STATE != 'idle in transaction' AND pgsa.STATE != 'idle in transaction (aborted)' ) idleconnections
ORDER BYquery_stay DESC
看一下xact_stay这个值是不是很大，很大的话就看看或者Explain下query里面的sql语句
解释语句
EXPLAIN (ANALYZE) <your_query>;
pg_locks
去pg_locks和pg_stat_activity两张表里关联查询是不是有锁（关联条件是pid和transcationId），排除下是不是锁的原因，如果有锁，就把锁进程kill掉试试
shared_buffers
查询数据库参数有没有问题
show shared_buffers
vacuum
收缩表，清理无用数据
vacuum full verbose table_name
vacuum full verbose analyze table_name
