## TL;DR
Enable pg_stat_statements, 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.

```text
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 pg_locks and pg_stat_activity instead)
- Transactions are deadlocking
- The problem is too many connections, not slow queries

## Steps

1. Enable the extension and confirm it is collecting:

```sql
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.

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

```sql
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.

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

```sql
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.

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

```sql
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.

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

```sql
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. pg_stat_statements 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; pg_stat_statements is the historical ranking.

### pg_stat_statements not showing queries
Usually the extension isnt in shared_preload_libraries (needs restart) or stats reset recently. Check `SHOW shared_preload_libraries;` first.

## Why it happens
Postgres doesnt rank queries by cost anywhere by default; pg_stat_activity shows the present moment only. pg_stat_statements 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