PostgreSQL Performance Problems

postgresql-performance-problems.sql

-- Check active queries 
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- Terminate active queries with ProcessID
select pg_terminate_backend(pid);

-- Get all queries which are older then 5 minutes (idle or active)
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Cancel all processes older then 5 minutes
SELECT pg_terminate_backend(queries.pid) from (
  SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
  FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
) AS queries;