Find Your Worst Postgres Query in 15 Minutes with pg_stat_statements

If your app has a slow endpoint and you're staring at application logs trying to guess which query is the culprit, stop. Postgres already tracked every query it ran, how long each one took, and how often — you just haven't asked it yet. pg_stat_statements is a built-in extension that turns "something feels slow" into "this exact query, called 40,000 times a day, is burning 60% of your database's CPU." Fifteen minutes from now you'll have a ranked list of your worst offenders and a fix for the top one.

Step 1: Turn it on (2 minutes)

pg_stat_statements ships with Postgres but isn't loaded by default. It needs to be in shared_preload_libraries, which means a config change and a restart — this is the one part of this technique you can't do without a brief window of downtime or a failover if you're on a managed HA setup.

Check if it's already loaded:

SHOW shared_preload_libraries;

If you don't see pg_stat_statements in the output, add it:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Restart Postgres, then create the extension in the database you care about:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

If you're on a managed provider (RDS, Cloud SQL, Vultr Managed Databases), this is usually a checkbox in a "shared preload libraries" or "extensions" panel rather than a config file edit — the SQL step is the same either way.

One caveat that trips people up: query text with literal values gets normalized into placeholders ($1, $2) automatically. Depending on your Postgres version and pg_stat_statements.track_utility setting, normalization behavior can vary slightly — don't worry about it, the ranking logic below works the same regardless.

Step 2: Find the worst offender (3 minutes)

The extension exposes a view called pg_stat_statements. The two columns that matter most are total_exec_time (how much cumulative time this query has cost the database) and mean_exec_time (how long a single call takes on average). They answer different questions, and conflating them is the most common mistake people make here.

To find what's costing you the most in aggregate — the query worth fixing first for overall database load:

SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_of_total,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

That pct_of_total column is the one to watch. It's common to find a single query responsible for 30-50% of total database time — not because it's slow per call, but because something is calling it far more often than it needs to (a classic N+1 pattern, a missing cache, a loop that should be a batch query).

To find your worst tail-latency offenders — queries that are individually slow and likely to trip timeout thresholds or make a specific page feel broken:

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

The calls > 10 filter matters — without it you'll get a one-off migration query or an analyst's ad-hoc SELECT * polluting the top of your list. You want repeated production traffic, not noise.

Step 3: Confirm and fix (8 minutes)

Take the query text from whichever list surfaced your real problem, substitute realistic values for the $1/$2 placeholders, and run it through EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 48213 AND status = 'pending'
ORDER BY created_at DESC;

Look for two things in the output: a Seq Scan on a table with more than a few thousand rows, and a high Buffers: shared read count relative to shared hit (that's disk I/O, not cache — expensive). If you see a sequential scan on a filtered, frequently-run query, that's almost always a missing index:

CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status, created_at DESC);

CONCURRENTLY matters here — it builds the index without taking a lock that blocks writes to the table, which is the difference between a routine change and a production incident on a busy table. It takes longer to build, but that's the trade you want.

Re-run the EXPLAIN ANALYZE afterward and compare the Execution Time line before and after. On a table with meaningful row counts, going from a sequential scan to an index scan on a filtered query commonly drops execution time by one to two orders of magnitude — a 200ms query landing under 2ms is a normal outcome, not an exceptional one.

Keep it honest over time

Stats accumulate from the moment the extension was enabled (or last reset), so a query that was fixed six months ago still shows up with its historical totals unless you clear them:

SELECT pg_stat_statements_reset();

Run that after you deploy a fix, then re-check the top-10 list a day later to confirm the query actually dropped out of the rankings — not just that the index exists, but that it's the one Postgres's planner is choosing to use. A missing ANALYZE on the table after a large data change can leave the planner working from stale statistics and ignoring a perfectly good new index.

If you want this to stay a five-minute weekly habit instead of a one-time fire drill, save the two ranking queries above as a psql script or a saved query in whatever database GUI your team uses, and run it every Monday. The worst query in your database changes as your data grows and your traffic patterns shift — the fifteen minutes you spend today buys you a habit, not just one fix.