postgresql SQL语句优化

查询正在执行的所有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查看