Find long-running PostgreSQL queries and slow statements

When a PostgreSQL server is slow, the first questions are “what is running right now?” and “what has used the most time recently?”. The pg_stat_activity view answers the first: it has one row per server process with its state, current query and timestamps. The pg_stat_statements extension answers the second.

The script: sessions older than N seconds

#!/usr/bin/env bash
set -Eeuo pipefail

usage() {
  echo "Usage: $(basename "$0") [min-seconds]   (default 60)" >&2
}

MIN_SECONDS=${1:-60}
if ! [[ $MIN_SECONDS =~ ^[0-9]+$ ]]; then
  usage
  exit 2
fi

psql -X -v ON_ERROR_STOP=1 -v min_seconds="$MIN_SECONDS" -P pager=off \
  -d "${PGDATABASE:-postgres}" <<'SQL'
SELECT pid,
       usename,
       datname,
       application_name,
       client_addr,
       state,
       wait_event_type,
       wait_event,
       date_trunc('second', now() - xact_start)  AS xact_age,
       date_trunc('second', now() - query_start) AS query_age,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND state IS DISTINCT FROM 'idle'
  AND now() - coalesce(xact_start, query_start) > make_interval(secs => :min_seconds)
ORDER BY coalesce(xact_start, query_start);
SQL

chmod +x pg-long-running.sh
sudo -u postgres ./pg-long-running.sh 300
PGHOST=db1.example.com PGUSER=monitoring ./pg-long-running.sh

It shows client sessions that are not idle and whose transaction (or query, if no transaction is open) started more than the given number of seconds ago, oldest first. To see other users’ query text, run it as a superuser or as a role that has pg_monitor or pg_read_all_stats: GRANT pg_monitor TO monitoring;.

Reading the output

• state = active: the query is running. If wait_event_type is set too (for example Lock or IO), it is waiting on something.
• state = idle in transaction: the client opened a transaction and is doing nothing. These hold locks and stop VACUUM from cleaning up; an old xact_age here is often the real problem.
• xact_age vs. query_age: a long transaction can consist of many short queries.
• query is truncated to 120 characters here; the full text (up to track_activity_query_size) is in the view.

Who is blocking whom

SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Stopping a query or session

Warning: cancelling rolls back the query’s work, and terminating ends the whole session and rolls back its open transaction. Check the pid, user and query in pg_stat_activity first, and prefer cancel over terminate.

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Non-superusers can signal sessions of roles they are a member of, or any non-superuser session if they have pg_signal_backend.

Top statements with pg_stat_statements

pg_stat_statements records execution statistics for every normalized statement (constants replaced by $1, $2…). It must be loaded at server start, so enabling it needs a restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on

CREATE EXTENSION pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       rows,
       left(regexp_replace(query, '\s+', ' ', 'g'), 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

SELECT pg_stat_statements_reset();

Sort by total_exec_time to find what costs the most overall (often a fast query called millions of times) and by mean_exec_time to find individually slow statements. Reset the statistics after a change so you can compare before and after. Then run the worst statement with EXPLAIN (ANALYZE, BUFFERS) on a test copy to see why it is slow; remember that EXPLAIN ANALYZE really executes the statement.

Official documentation

• The cumulative statistics system (pg_stat_activity)
• pg_stat_statements
• System administration functions