Confession: for about two years, my approach to a slow Postgres was to open the app, click around until something felt sluggish, then add an index to whatever table I’d guessed at. Sometimes it worked. When it worked I told people I’d tuned the database, which was a generous description of throwing darts.
What broke the habit was a client app where the slow page wasn’t slow because of the query I was staring at. It was slow because a tiny lookup query, one that took 0.8ms, ran eleven thousand times per page load. No amount of squinting at the slow page was ever going to show me that. pg_stat_statements showed me in about forty seconds.
So this is the short version of what I now run before I touch a single index.
Turning it on, which is the annoying part
pg_stat_statements is a contrib module, not a default. It needs shared memory, which means it has to load at server start, which means a restart. From the official Postgres docs:
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
Then restart, then create the extension in the database you care about:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
track = top records statements issued directly by clients. track = all also records statements inside functions and procedures, which is what you want if a lot of your logic lives in PL/pgSQL, and noise otherwise. I default to top and switch when I have a reason.
One thing that catches people: on managed Postgres, this is usually already enabled and you just need the CREATE EXTENSION. Check before you schedule a restart window you didn’t need.
The other thing worth understanding before you read a single row of output is normalisation. Postgres doesn’t store your queries verbatim. It strips out constants and replaces them with placeholders, so a thousand executions of SELECT * FROM users WHERE id = 1, = 2, = 3 collapse into one row with calls = 1000. That’s the entire reason the extension is useful, and it’s also why you can’t answer “which specific user caused the slow request” from this data. You get shapes, not instances. If you need the instance, that’s what log_min_duration_statement and the slow query log are for, and the two tools answer genuinely different questions.
A consequence people trip over: queries that differ only in the number of items in an IN clause normalise separately in older versions, so IN ($1, $2) and IN ($1, $2, $3) show up as distinct rows. If your top-fifteen list looks like the same query repeated with slightly different lengths, that’s what you’re seeing, and the real total is the sum of them.
Query one: what is eating the wall clock
This is the only query most people ever run, and it’s the right one to start with.
SELECT
substring(query, 1, 90) AS q,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
Total time, not mean time. That ordering is the whole point. A query averaging 900ms looks like the villain until you notice it runs four times a day, while the 0.8ms query running eleven thousand times is quietly burning nine seconds of every page load.
I got this backwards for a long time. I sorted by mean_exec_time, found the slowest single query, optimised it beautifully, and moved the needle by nothing. If you fix one thing today, fix the sort order.
Query two: the ones called absurdly often
Once total time points somewhere, this confirms the shape of the problem:
SELECT
substring(query, 1, 90) AS q,
calls,
round(mean_exec_time::numeric, 3) AS mean_ms,
round(total_exec_time::numeric, 1) AS total_ms
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY calls DESC
LIMIT 15;
High call count with a low mean is the signature of an N+1. The database is fine. Your ORM is in a loop. Adding an index here does nothing, because each individual query is already fast; there are just thousands of them where there should be one.
I wrote up how I catch N+1 queries in Laravel separately, and the fix is almost always eager loading rather than anything database-side. pg_stat_statements is how I find out the problem exists at all; the ORM is where I fix it.
Query three: who is going to disk
Buffer statistics tell you whether a query is reading from memory or from storage, and the ratio is more useful than either number alone.
SELECT
substring(query, 1, 70) AS q,
calls,
shared_blks_hit,
shared_blks_read,
round(
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0),
1
) AS hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 15;
A hit percentage in the high nineties across the board usually means your working set fits in memory and your problem is elsewhere. A query with millions of shared_blks_read and a hit rate in the sixties is doing sequential scans over something that no longer fits, and that one probably does want an index, or a partial index, or a rethink of the query.
Query four: temp file spills
This one I only learned to check after a very embarrassing afternoon.
SELECT
substring(query, 1, 70) AS q,
calls,
temp_blks_written,
round(total_exec_time::numeric, 1) AS total_ms
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;
If a sort or hash join doesn’t fit in work_mem, Postgres writes it to disk. The query still returns correct results, so nothing looks broken, it’s just slow in a way that no index will fix. Sometimes the answer is bumping work_mem for that one session. Sometimes it’s that you’re sorting a million rows to show the user twenty. Crunchy Data have a good writeup on reading these numbers alongside plan output, and the plan is where you go next.
What it will never tell you
Worth knowing the edges, because I’ve seen people stare at this data waiting for an answer it structurally cannot give.
It doesn’t store plans. You get timing and buffer counts, never the execution plan, so it tells you which query to investigate and nothing about why that query is slow. EXPLAIN (ANALYZE, BUFFERS) is the next step and there’s no shortcut around it.
It doesn’t store parameter values, for the normalisation reason above. So “this query is slow only for one enormous tenant” is invisible here. You’ll see a mean that looks acceptable and a stddev_exec_time that’s enormous, and that standard deviation column is the tell. I check it whenever a mean looks suspiciously reasonable.
It also can’t distinguish a query that’s slow from a query that’s waiting. Time spent blocked on a lock counts toward execution time, so a perfectly efficient UPDATE sitting behind another transaction shows up looking expensive. If your top-by-total-time list is full of simple writes, the problem is contention, and pg_locks and pg_stat_activity are where you go instead.
And it’s capped. Once you exceed pg_stat_statements.max, the least-executed entries get evicted, so a rarely-run but genuinely awful query can vanish from the table entirely on a busy server. The default of 5000 is low for an application with a lot of distinct query shapes. I set 10000 and don’t think about it again.
Resetting, and why you should
Statistics accumulate since the last reset, which means after six months your top-by-total-time list is a history lesson rather than a diagnosis.
SELECT pg_stat_statements_reset();
My habit: reset, let it run through a normal business day, then look. Reset again before and after a change so the comparison is honest. Without that, you’re comparing yesterday’s numbers to numbers that include yesterday.
There’s a subtlety in what “a normal business day” means, and I got burned by ignoring it. If your app has a nightly batch job, a Monday morning report, or a weekly invoice run, sampling a random Tuesday afternoon tells you nothing about the load that actually causes your incidents. I now sample twice: a quiet window and a peak window, on the days those things run, and I compare the two lists rather than reading either one alone. Queries that only appear in the peak list are the ones worth engineering for.
If you’d rather not reset at all, the alternative is snapshotting: dump the table into a regular table on a schedule, then diff two snapshots to get the deltas for that interval. That’s more work to set up and much better for anything ongoing, because you keep the history instead of destroying it every time you want a clean reading.
Where a dashboard starts to make sense
Four queries in a terminal work fine for one database. They stop working when you have a primary and three replicas, because most Postgres statistics are per-instance and aren’t replicated. An index that looks unused on the primary may be serving every read on a standby, and dropping it based on a single-host view is how you cause an incident while trying to prevent one.
There’s a newer open source option here worth knowing about: Dasha, announced through the Postgres community news feed, reads every host of a cluster and reasons over the combined picture rather than one instance at a time. It connects with a read-only role and installs nothing on the database hosts, which is the property I care about most on a box I’d rather not touch.
I haven’t run it long enough to recommend it without hedging. But the design decision it’s built on, that per-instance stats lie about a cluster, is correct, and it’s the thing my four queries above genuinely can’t tell you.
What to do this week
Turn the extension on if it isn’t already. Run query one. Look at the top five rows and ask, for each: does the call count make sense to me?
That’s it. Not the plan, not the index, not work_mem. Just whether the number of times a query runs matches your mental model of the application. In my experience the answer is no about half the time, and the gap between those two numbers is where the easy wins live. This is usually the first thing I check on performance work I take on, and it’s found more problems than every clever index I’ve ever written.
