Postgresql Slow Query Debugging: Debugging Production Postgres Slow Queries Without Causing Downtime

-- See all active queries sorted by duration
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start IS NOT NULL
ORDER BY duration DESC;

-- Queries running for over 30 seconds
SELECT pid, duration, query
FROM (
  SELECT pid, now() - query_start AS duration, query
  FROM pg_stat_activity
  WHERE state = 'active'
) t
WHERE duration > interval '30 seconds';

-- Kill a specific runaway query (never use terminate in production unless certain)
SELECT pg_cancel_backend(PID); -- Sends SIGINT, allows graceful cancel
-- pg_terminate_backend(PID)   -- SIGTERM, only if cancel doesn't work

-- Find blocking queries and their victims
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query,
  now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Common causes of blocking:
-- 1. Long-running transaction holding lock
-- 2. ALTER TABLE (takes ACCESS EXCLUSIVE lock)
-- 3. VACUUM FULL (takes ACCESS EXCLUSIVE lock)
-- 4. Missing index causing full table scan while holding row locks

-- Enable pg_stat_statements (add to postgresql.conf, reload not restart)
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows,
  round(100 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct_total,
  LEFT(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with highest variance (inconsistently slow)
SELECT LEFT(query, 100), calls, mean_exec_time, stddev_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 10;
-- High stddev = sometimes fast, sometimes slow = index bloat or lock waits

-- Enable auto_explain (no restart required, hot-reload)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000; -- Log queries over 1 second
SET auto_explain.log_analyze = true;       -- Include actual row counts
SET auto_explain.log_buffers = true;       -- Include buffer hit/miss stats
SET auto_explain.log_timing = true;
SET auto_explain.log_nested_statements = true;

-- These settings are session-level, add to postgresql.conf for global:
-- auto_explain.log_min_duration = 1000
-- auto_explain.log_analyze = on

-- Now any query taking > 1s gets its EXPLAIN ANALYZE logged automatically
-- Check PostgreSQL logs for entries like:
-- LOG: duration: 2341.123 ms  plan:
--   Seq Scan on orders (cost=0.00..250000.00 rows=5000000 ...)
--   (actual time=0.027..2340.891 rows=4999999 ...)

-- Check index bloat (pages used vs pages actually needed)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Unused indexes (never been scanned)
SELECT indexrelid::regclass AS index, relid::regclass AS table, idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND indisunique IS FALSE;  -- Exclude unique constraints
-- Drop unused indexes — they slow down writes for zero read benefit

-- Rebuild bloated index online (no lock!)
REINDEX INDEX CONCURRENTLY idx_orders_status;  -- PostgreSQL 12+