查询慢sql

如果服务器数据库反应突然巨慢，估计是有慢sql

运行以下sql可以看到慢sql的一些详情PID，运行的sql语句。。。

SELECT
pid,
datname,
usename,
client_addr,
application_name,
STATE,
backend_start,
xact_start,
xact_stay,
query_start,
query_stay,
REPLACE ( query, chr( 10 ), ’ ’ ) AS query
FROM
(
SELECT
pgsa.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
FROM
pg_stat_activity AS pgsa
WHERE
pgsa.STATE != ‘idle’
AND pgsa.STATE != ‘idle in transaction’
AND pgsa.STATE != ‘idle in transaction (aborted)’
) idleconnections
ORDER BY
query_stay DESC

怎样停止正在执行的慢sql
SELECT pg_cancel_backend(进程id);

这个是查看表是否被锁

select oid from pg_class where relname=‘m_ss_kjcx_tech_inovate_talent’
select pid from pg_locks where relation=‘4384913’
–如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(140650678843136)