pg_stat_statements Tutorial: Find Slow Queries

There are two built-in ways to find slow queries in Postgres, and they answer different questions. The postgres slow query log (`log_min_duration_statement`) records individual statements that cross a duration threshold, which is useful for catching a specific four-second offender in the act. The `pg_stat_statements` extension does something more strategic: it aggregates every execution of every query shape, so a 30ms query that runs 50,000 times a minute finally shows up as the real problem it is. The log tells you what was slow once; the view tells you what is costing you the most over time.

Enabling the extension

`pg_stat_statements` is a contrib module, so it has to be loaded at server start. Add it to `shared_preload_libraries` in `postgresql.conf` (for example, `shared_preload_libraries = 'pg_stat_statements'`), restart Postgres, then run `CREATE EXTENSION pg_stat_statements;` in the database you want to inspect. Two settings are worth a look: `pg_stat_statements.max` caps how many distinct query shapes are tracked (the default 5,000 suits most workloads), and `pg_stat_statements.track = top` records top-level statements without double-counting nested function calls. On managed Postgres such as RDS or Cloud SQL, the extension is usually enabled through a parameter-group toggle rather than an editable config file.

The columns that matter

Once data accumulates, a handful of columns carry most of the signal. `calls` is the execution count. `total_exec_time` is cumulative milliseconds spent across all of those calls, and it is the single best proxy for what is actually hurting the database. `mean_exec_time` is total divided by calls, which separates a slow-but-rare report query from a fast-but-relentless one. `rows` shows how many rows each shape returned in aggregate, and `stddev_exec_time` flags queries whose timing is unstable enough to deserve a closer look. Sort by `total_exec_time DESC` and limit to ten, and you are usually staring at the list that matters.

A subtlety trips people up here: each row is a normalized query, not a literal one. `pg_stat_statements` replaces constants with placeholders, so `WHERE id = 42` and `WHERE id = 99` collapse into one entry keyed by `queryid`. That normalization is what makes aggregation meaningful, but it also means you see the shape rather than the exact literal values.

A triage workflow for small teams

Without a dedicated performance DBA, the aim is a repeatable loop rather than heroics. Pull the top ten by `total_exec_time`, run `SELECT pg_stat_statements_reset();` after a deploy so you compare clean windows, and pick the single worst offender. Reconstruct a representative query from its normalized text, run `EXPLAIN (ANALYZE, BUFFERS)` on it, and read what the planner actually chose; how the Postgres planner picks a plan explains why a sequential scan sometimes wins and when an index would change the math. If a shape returns far more rows than the application needs, the fix is often a sharper predicate or a missing index. Pasting that plan into an analyzer such as Querk returns a diagnosis and candidate index DDL in seconds, and you can run a query through the free analyzer without an account. When the real culprit is dead-tuple overhead instead, bloat and autovacuum tuning is the better next stop.