Finding the statements that cost the most with pg_stat_statements

Goal

Rank the statements a server executes by their total cost, so that tuning effort goes to what dominates load rather than to the query that happened to be noticed.

Prerequisites

The module in shared_preload_libraries (the documentation states a restart is needed to add or remove it), compute_query_id at auto or on, and CREATE EXTENSION pg_stat_statements in the database from which the view is read. Seeing other users' query text needs the pg_read_all_stats role or superuser.

Steps

1. Establish the window: SELECT stats_reset FROM pg_stat_statements_info. Counters are cumulative since that time; if the window is unknown or spans a deploy, run SELECT pg_stat_statements_reset() and wait for a representative period.
2. Rank by total time: SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. A fast statement called millions of times often outranks the slow report everyone complains about.
3. Rank by calls separately. Very high counts with one row per call point at per-row loops in application code rather than at the database.
4. Compare mean_exec_time with max_exec_time. A large gap suggests parameter-dependent plans or lock waits, not a uniformly slow statement.
5. Read the block columns: shared_blks_read against shared_blks_hit separates I/O-bound statements from cached ones; temp_blks_written shows sorts and hashes spilling to disk.
6. Take the top statement's text, replace the $n placeholders with realistic values, and continue with EXPLAIN (ANALYZE, BUFFERS).
7. After a change, reset, wait the same period, and compare the same rows by queryid, not by query text.

Expected result

A short list of statements that account for most of the execution time, each with a stated reason (call count, per-call cost or I/O), and a before-and-after comparison per queryid for every change made.

Limits and test basis

The documentation states that the view keeps at most pg_stat_statements.max entries (default 5000) and discards the least-executed ones beyond that, so rare statements may be missing; that queryid is not stable across major versions or between logically replicated servers; and that only successful executions update the execution counters. Constants are normalised, and the documentation states that queries differing only in the number of elements in a list of constants are squashed into one entry, shown as IN ($1 /*, ... */). The procedure yields relative rankings; no timings are claimed.