How to find slow queries in PostgreSQL

“The database is slow” can mean three very different things: one query is stuck right now, a handful of queries use most of the server’s time, or a particular call is occasionally slow with particular parameters. PostgreSQL ships with a tool for each. This guide walks through them in the order you would reach for them.

1. What is slow right now: pg_stat_activity

pg_stat_activity has one row per server process, with the statement it is running and how long it has been running. Start here when something is slow at this moment.

SELECT pid,
       usename,
       datname,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS runtime,
       left(query, 200)    AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY runtime DESC NULLS LAST;

A few things to look at:

• wait_event_type / wait_event: if a long-running statement is waiting on Lock, it is blocked, not slow. pg_blocking_pids(pid) tells you who holds the lock:

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

• idle in transaction: a session that opened a transaction and stopped sending commands. It holds its locks and keeps old row versions alive, which hurts vacuum. Check now() - state_change to see for how long.

• To stop a runaway statement use SELECT pg_cancel_backend(pid);, and if that is not enough, pg_terminate_backend(pid).

Members of the pg_read_all_stats role (and superusers) can see the query text of every session; other users only see their own.

The limitation: pg_stat_activity is a snapshot. Statements that take a few milliseconds come and go between your queries, and nothing is kept once a statement finishes.

2. What is slow overall: pg_stat_statements

pg_stat_statements aggregates statistics for every normalized statement (constants replaced by $1, $2, …) since the last reset. It is the best answer to “where does the server’s time go?”.

It must be loaded at server start. In postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Restart the server, then in each database where you want to query it:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The top statements by total execution time (column names are for PostgreSQL 13 and later; older versions use total_time and mean_time):

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1)                              AS total_ms,
       round(mean_exec_time::numeric, 2)                               AS mean_ms,
       rows,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
       left(query, 120)                                                AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time first. A 2 ms query called a million times usually matters more than a 5-second report that runs once a night. Then look at:

• mean_exec_time for statements that are slow on every call.
• shared_blks_read for statements that read a lot from outside PostgreSQL’s buffer cache. 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) gives the cache hit ratio per statement.
• rows / calls for statements that return far more rows than the application needs.

To measure a change, call SELECT pg_stat_statements_reset();, let the workload run, and query again.

The limitation: everything is aggregated. You see that a statement is slow on average, but not which parameter values made one call slow, or when it happened.

3. Individual slow calls: log_min_duration_statement

log_min_duration_statement writes every statement that runs longer than a threshold to the server log, with its duration. With the extended query protocol, which most drivers use, the log entry includes the bind parameters, so you can reproduce the exact call.

ALTER SYSTEM SET log_min_duration_statement = '250ms';
SELECT pg_reload_conf();

It can also be set for one database or role, which keeps noise down on a shared server:

ALTER DATABASE shop SET log_min_duration_statement = '100ms';

A typical entry:

LOG:  duration: 312.481 ms  execute <unnamed>: select ... where status = $1 and total between $2 and $3
DETAIL:  parameters: $1 = 'paid', $2 = '23', $3 = '25'

Make the log useful by including the session identity in log_line_prefix, for example '%m [%p] %q%u@%d app=%a ' (time, process ID, user, database and application name). On PostgreSQL 13 and later, log_min_duration_sample with log_statement_sample_rate lets you log only a fraction of statements above a lower threshold on busy servers.

Setting the threshold to 0 logs every statement with its duration. That is useful for a short investigation but costs I/O on a busy server.

4. Why it is slow: auto_explain and EXPLAIN

Once you know which statement is slow, you need its execution plan. EXPLAIN shows the plan the planner chooses; EXPLAIN (ANALYZE, BUFFERS) runs the statement and adds actual row counts, timings and buffer usage.

EXPLAIN ANALYZE really executes the statement. For INSERT, UPDATE or DELETE, wrap it in a transaction you roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE sales.orders SET status = 'paid' WHERE id = 42;
ROLLBACK;

The trouble is that a statement that was slow in production is often fast when you run it by hand: the cache is warm, the parameters differ, or the lock is gone. auto_explain logs the plan of slow statements at the moment they ran:

# postgresql.conf (needs a restart)
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off      # keeps per-node timing overhead low
auto_explain.log_format = json

With log_analyze on, actual row counts are collected for every statement, not just the slow ones, so there is some overhead. Turning log_timing off avoids the most expensive part. For a quick test in one session, a superuser can run LOAD 'auto_explain'; and set the parameters with SET.

In the plan, look for:

• Estimated vs. actual rows that differ by orders of magnitude. Often fixed with ANALYZE or extended statistics.
• Sequential scans that remove most rows with a filter: a candidate for an index.
• Sorts or hashes that spill to disk (Sort Method: external merge): work_mem may be too low for that query.
• Nested loops over many rows where a hash join was expected, usually a result of bad estimates.

Putting it together

1. Something is slow now: pg_stat_activity, and check wait_event before blaming the query.
2. The server is busy in general: pg_stat_statements, sorted by total time.
3. You need the exact slow calls with parameters: log_min_duration_statement.
4. You need to know why: EXPLAIN (ANALYZE, BUFFERS) by hand, or auto_explain for plans captured in production.

---

If you would rather see all of this in one window: the Query Profiler in PgFlow-ai combines the server log, pg_stat_activity and pg_stat_statements into a live trace with durations and bind parameters, and opens the execution plan of any captured statement. Its Activity_Sampler template needs no server changes. There is a 14-day trial with no account needed.