How to find slow queries in PostgreSQL

Two kinds of slow

“The database is slow” usually means one of two things:

• Something is slow right now. A query is running long, or many queries are waiting. You need live activity.
• Something has been slow for a while. One query shape, run thousands of times, uses most of the database's time. You need history.

PostgreSQL answers the first with pg_stat_activity and the second with the pg_stat_statements extension. EXPLAIN then tells you why a given query is slow.

1. What is slow right now

SELECT pid,
       now() - query_start AS duration,
       state,
       wait_event_type,
       wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY duration DESC;

Look for long duration values. A state of idle in transaction is a session that started a transaction and stopped. It can hold locks and block others while doing nothing.

2. Waiting or working?

A slow query is either doing work or waiting. wait_event_type tells you which:

• Lock: blocked by another session. See who is blocking it below.
• IO: reading from disk, often a large scan or a cold cache.
• Empty (null) while active: running on CPU.

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

3. What has been slow over time

pg_stat_statements groups queries by shape, so the same query with different values counts as one, and records calls and time for each.

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       rows,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total time first. A 5 ms query called a million times can matter more than one 10-second report. On PostgreSQL 12 and older the columns are named total_time and mean_time.

4. Why a query is slow

Take the query from step 3, put real values in, and ask PostgreSQL how it runs it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'PENDING'
  AND created_at > now() - interval '1 hour';

What to look for:

• Seq Scan on a large table where you expected an index.
• Estimated rows far from actual rows: statistics may be stale. Run ANALYZE orders;.
• High “shared read” buffers: data came from disk, not memory.
• Sorts or hashes that spill to disk (“external merge”).

5. Tables read the hard way

Tables read mostly by sequential scans are where indexes help most.

SELECT relname AS table_name,
       seq_scan,
       idx_scan,
       n_live_tup AS approx_rows
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

A large table with many sequential scans and few index scans is a candidate for an index on the columns in its WHERE clauses. Weigh it against write cost: every index slows down inserts and updates on that table.

6. Fix, then measure again

1. Make one change: an index, a rewritten query, or fresh statistics.
2. Run the same EXPLAIN (ANALYZE, BUFFERS) and compare the plan.
3. Compare pg_stat_statements for the same query over a similar period with real traffic. Reset the counters first with SELECT pg_stat_statements_reset(); if you want a clean window.

A faster plan on your test values is a good sign. Lower total time for that query in production is the proof.

The same steps in Quorum

Quorum reads pg_stat_statements and live activity for you, ranks queries by observed impact and shows waits beside them, inside the same window as your editor.

• Wait-time analysis: where time goes, whether locks, I/O or CPU, per statement and per window.
• Plan history and regressions: compare equivalent windows, with estimated against actual rows explained in plain English.
• Index advice weighed honestly: ranked by measured impact, with storage and write overhead shown.
• Baselines: compare before and after a change with the same checks.