查询正在执行的所有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语句
DELETE
FROM
lb_api_task_report
WHERE
task_id IN ( SELECT task_id FROM lb_api_task_report WHERE task_id NOT IN ( SELECT ID FROM lb_api_test_task ))
这样的话SELECT ID FROM lb_api_test_task将成为一个子查询,而且不会走索引,每次走一遍全表扫描
通过explain查看(可以看到不走索引)
如果要释放掉慢查询资源
SELECT pg_terminate_backend(PID);
SQL优化之一:NOT IN 优化
正确的方式应该使用not exists,就不会出现子查询了:
DELETE
FROM
lb_api_task_report
WHERE
task_id IN ( SELECT task_id FROM lb_api_task_report t1 WHERE NOT EXISTS ( SELECT 1 FROM lb_api_test_task t2 WHERE t2.ID = t1.task_id ) );
通过explain查看