postgresql 查询缓慢原因分析

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