Because pg_stat_statements aggregates every execution of one query shape, it turns a vague “the page is slow” into a ranked table. The setup is a config line and a restart; the payoff is knowing which of your query shapes actually owns the clock.

The one decision that matters is which column you sort by. Sorting by mean time reproduces what the slow log already told you; sorting by total time is what finds the query that has been quietly consuming a quarter of your database.

Normalize, then aggregate

The extension does not store your SQL verbatim. Constants are folded into parameter symbols, so WHERE tenant_id = 42 and WHERE tenant_id = 8712 collapse into one entry, displayed as the text of the first query that produced that queryid. The numbering rule is positional — symbols start at the next number after the highest $n already present, which is why a PL/pgSQL statement like SELECT i + 1 INTO j shows up as SELECT i + $2. Lists behave the same way: two queries differing only in the number of IN elements get squashed into IN ($1 /*, ... */) with a call count of two.

The effect is dramatic: a modelled workload of 1,420,052 executions, each with different literals, collapsed into 6 rows — 236,675 raw statements per row. Two caveats come with it: queryid is a hash of the post-parse-analysis tree, so identical text can land in different entries when search_path differs, unrelated queries can collide into one entry, and the value is not promised to be stable across major versions. Treat queryid as a better identifier than the text, not as a permanent key.

Total time, not mean time

The columns that matter are calls, total_exec_time, mean_exec_time, rows, and the block counters. min/max/stddev_exec_time tell you whether a query is uniformly slow or occasionally long, which points at bad plans versus lock contention respectively.

Ranking by mean answers a different question from ranking by total, and the gap is large. In the same workload the query with the worst mean (9,689 ms, 12 calls) is 22.4% of measured time; the query with the worst total is 25.9% but averages 0.749 ms per call. The worst-by-total query never appears in a slow log at any sane log_min_duration_statement setting, and the three worst by total together are 65.5% of all measured time. A 0.099 ms lookup called 900,000 times ranks sixth by mean and third by total, at 89,217 ms.

Counters accumulate, so compare windows

Counters run from the moment statistics started, so a query that used to be slow never fades out of the top of the table on its own. The fix is either a scoped reset — pg_stat_statements_reset(userid, dbid, queryid, minmax_only) takes all four, so you can clear one query, one database, or just the min/max fields — or a delta between two snapshots.

The block counters deserve their own reading. shared_blks_hit and shared_blks_read are always tracked; track_io_timing adds the *_blk_read_time and *_blk_write_time columns, not the counts. Hit ratio is a diagnostic, not a cost: the query with the best hit ratio in the workload (98.89%) was also the largest block consumer at 81,000,000 blocks, while the query with the worst ratio (62.22%) moved 37x fewer.

When it breaks

The first failure is that the extension is simply not there. It must be listed in shared_preload_libraries because it needs shared memory, which means adding or removing it requires a restart, compute_query_id must be auto or on, and the views only exist in databases where CREATE EXTENSION pg_stat_statements was run. Miss any of those and every query against it fails, so the monitoring you thought you had has been empty for months.

The second failure is a reset that destroys the comparison. Reset the counters mid-window and every delta comes out negative: in the fixture, 6 of 6 shapes reported negative time. The arithmetic is fine; the window is not. pg_stat_statements_info.stats_reset exists precisely for this — check it before trusting any subtraction, and if the reset was intentional, start the window after it rather than trying to reconcile across it.

Check it quickly

Run both rankings and see how little they agree:

psql -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time,3) AS mean_ms, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
psql -c "SELECT calls, round(mean_exec_time,2) AS mean_ms, round(total_exec_time) AS total_ms FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;"
psql -c "SELECT stats_reset FROM pg_stat_statements_info;"
psql -c "SELECT sum(calls) AS calls, round(sum(total_exec_time)/1000) AS total_s, round(sum(shared_blks_read)*8.0/1024/1024,1) AS read_gib FROM pg_stat_statements;"
psql -c "SELECT pg_stat_statements_reset(0, 0, queryid) FROM pg_stat_statements WHERE query LIKE 'SELECT%';"

The first two lists should disagree, and the first is the one to work from. stats_reset is the timestamp that decides whether a delta window is valid. The totals line is the sanity check on magnitude — if total_s is larger than the wall-clock age of your statistics, you are reading across a restart. The last command resets selectively; run it deliberately, because it is exactly what makes the next comparison wrong.

Sources