Reading pg_stat_statements: A Practical SMB Workflow

Reading pg_stat_statements: A Practical SMB Workflow

For a small engineering team without a dedicated DBA, `pg_stat_statements` is often the fastest path from "the app feels slow" to "here's the query causing it." The extension tracks execution statistics for every normalized query PostgreSQL runs, but raw output can be overwhelming without a clear workflow. The following process gives 2–5 person teams a repeatable way to find the queries actually worth fixing, rather than chasing noise.

Confirm the Extension Is Enabled and Reset the Baseline

Before analysis is useful, confirm `pg_stat_statements` is loaded via `shared_preload_libraries` and that the view exists in the target database. Run:

```sql

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

```

If it's missing, it requires a config change and restart, so plan that during a low-traffic window. Once confirmed, reset stats with `SELECT pg_stat_statements_reset();` before a representative traffic period — a full business day is usually enough for SMB workloads. Resetting matters because cumulative stats since last restart can mix old, already-fixed queries with current problems, skewing priorities.

Sort by Total Time, Not Just Slowest Query

The most common mistake is sorting by `mean_exec_time` alone. A query that runs once and takes 4 seconds is annoying, but a query that runs 50,000 times a day at 40ms each consumes far more total database time and often indicates a missing index or an N+1 pattern in application code. The more useful query is:

```sql

SELECT query, calls, total_exec_time, mean_exec_time, rows

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 20;

```

This surfaces the queries with the highest cumulative cost — the ones that, if optimized, actually move the needle on database load and response times.

Cross-Reference with Row Counts and Call Frequency

Once the top 20 list is in hand, look at `rows` relative to `calls`. A query returning thousands of rows per call may be missing a `LIMIT` or filtering client-side instead of in SQL. A query with very high `calls` and low `mean_exec_time` might still be worth batching if it's called in a loop. This is also the point to run `EXPLAIN (ANALYZE, BUFFERS)` on the top three or four candidates to see whether the planner is doing sequential scans where an index would help, or whether statistics are stale after a large data change.

Teams without much day-to-day query planning experience often find it faster to run this triage with a tool that automates the sorting, flags obvious missing-index patterns, and tracks whether a query's cost trends up or down after a fix — [Querk](