How to Find the One Query That Is Actually Killing You

When a database is struggling, the instinct is to hunt for the slow query. It is usually the wrong hunt.

A report that takes twenty seconds and runs twice a day costs your database forty seconds. A lookup that takes three milliseconds and runs two million times an hour costs it a hundred minutes. The second one never appears in a slow query log, and it is the one flattening your server.

Ranking by total time instead of by duration is the whole technique, and Postgres ships the tool for it.

Turn it on

pg_stat_statements is a contrib module. It needs shared memory, so it has to be preloaded, which means one restart.

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

Then, once, in the database you care about:

CREATE EXTENSION pg_stat_statements;

Two defaults are worth knowing, because both quietly give you less than you expect.

pg_stat_statements.max defaults to 5000. That is the number of distinct statements kept; once it is full, the least executed entries are discarded. On a busy application with many query shapes, raising it costs a little shared memory and stops your evidence being evicted before you look at it.

pg_stat_statements.track_planning defaults to off. The planning columns exist either way, and they will read zero until you turn it on. If you are chasing a suspicion that planning rather than execution is the cost, enable it deliberately; it is off by default because it has overhead.

The query that finds the query

SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not mean_exec_time. Mean tells you which query feels slow. Total tells you where the server's time went, and those are different lists more often than not.

Reset the counters before a representative window so you are measuring today rather than everything since the last restart:

SELECT pg_stat_statements_reset();

Leave it an hour under normal load, then run the ranking. The top three usually account for most of the pain, and the first one is often a surprise.

Reading the result

High calls, low mean_ms. An N+1. Something in the application is looping over rows and querying once per row. The fix is in the application, not the database: batch it into one query with IN, or fetch the relation up front. No index will save you from ten thousand round trips.

Low calls, high mean_ms. A genuine slow query. This one is worth an EXPLAIN.

rows far larger than anything a screen shows. Something is fetching everything and filtering in application code. Move the filter into SQL.

Two entries that look identical. They differ in a literal that was not parameterised, so they are separate shapes. That is a signal in itself: parameterise, and the plan gets cached.

Then explain it

Take the top query and ask the planner what it actually did.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

ANALYZE runs the query and reports real timings rather than estimates. BUFFERS shows how many blocks came from cache and how many from disk, which is often the whole story: the same query is fine at three in the afternoon and terrible at nine in the morning because the cache is cold.

Look for a sequential scan on a large table, an estimated row count wildly different from the actual one, and a sort that spilled to disk.

Be careful with EXPLAIN (ANALYZE) on writes. It executes the statement. Wrap it in a transaction you roll back.

Catch the ones that only misbehave in production

Some queries are only slow with production data volumes and production cache pressure. auto_explain logs the plan for anything over a threshold, so the evidence is waiting for you rather than needing to be reproduced.

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on

log_analyze adds real execution costs to the logged plan, and adds overhead to every statement that crosses the threshold. Set the threshold high enough that it only catches genuine outliers.

The order

Turn on the module, reset, wait an hour under real load, rank by total time, explain the top three. That is usually ten minutes of work and it replaces a week of guessing.

If the answer turns out to be the server rather than the query, that is a different job, and it is the one our server optimization work starts with.