how to find the slowest queries in Postgres

TL;DR

Enable pgstatstatements, then query it ordered by total time to find the queries costing you the most. Total time (mean time times call count) beats slowest-single-run for prioritization, because a 50ms query running a million times hurts more than one 5-second query.

how to find the slowest queries in Postgres

Use this when

• The database is slow and you need evidence, not guesses
• You want a ranked list of queries by cost
• You are deciding which query to optimize first

Not for this skill when

• A query is blocked right now (check pglocks and pgstat_activity instead)
• Transactions are deadlocking
• The problem is too many connections, not slow queries

Steps

1. Enable the extension and confirm it is collecting:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT count(*) AS tracked_queries FROM pg_stat_statements;

Expected output: a nonzero count after some traffic. On managed Postgres this usually needs a parameter-group change plus a restart, then the CREATE EXTENSION.

1. Rank queries by total time, the number that actually matters:

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

Expected output: the top 15 queries with call counts, totals, means, and share of all query time. The pct column shows concentration: if one query is 60 percent, start there.

1. Also check the slowest per-call queries, which the total-time ranking can hide:

SELECT calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       round(max_exec_time::numeric, 1) AS max_ms,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 15;

Expected output: queries with the worst average latency. The calls > 10 filter keeps one-off monsters from dominating.

1. Take the top offender and get its actual execution plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

Expected output: the plan with real timings per node. Look for Seq Scan on large tables, huge rows-removed-by-filter counts, and nested loops over big inputs. Thats your optimization target.

1. Reset stats after a fix to measure the improvement cleanly:

SELECT pg_stat_statements_reset();
-- let traffic run, then rerun the ranking queries from steps 2-3

Expected output: fresh counters. Compare the new top-15 against the old one to prove the fix worked instead of assuming it.

Variant phrasings

postgres slow query log analysis

log_min_duration_statement plus pgBadger is the log-based alternative. pgstatstatements is better for live ranking; logs are better for full query text and bind parameters.

how to see currently running slow queries

SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC; Thats the right-now view; pgstatstatements is the historical ranking.

pgstatstatements not showing queries

Usually the extension isnt in sharedpreloadlibraries (needs restart) or stats reset recently. Check SHOW shared_preload_libraries; first.

Why it happens

Postgres doesnt rank queries by cost anywhere by default; pgstatactivity shows the present moment only. pgstatstatements normalizes query text (literals become parameters) and accumulates timing per normalized query, which turns "the database feels slow" into a ranked list you can act on. Without it, optimization is guesswork driven by whoever complains loudest.

Edge cases

• Normalized queries hide which parameter values were slow; pair with the slow query log for full text.
• Utility statements (VACUUM, DDL) appear in the stats too; filter on query type if they pollute the ranking.
• Stats are per-database and reset on restart; for trend data, snapshot the view into a history table on a schedule.
• Very high max_connections dilutes per-query stats with noise; the pct column still points at the real offenders.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_JKIaCN6klIChpq6f0sO-cA