Observation and diagnostics · Intermediate

Reading query trends with pg_stat_statements

Compare normalized SQL counts and execution-time deltas over a matched interval to distinguish accumulated workload cost from individual-request latency.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Study recurring work rather than one current statement
  2. Establish collection and grouping
  3. Save two observations of the same population
  4. Calculate interval priorities from counter changes
  5. Detect resets and entry replacement
  6. Investigate parameters and hidden latency tails
  7. Validate the workload objective after a change
  8. Ask Logeia about this articleCheck your understanding

Study recurring work rather than one current statement

Pg_stat_statements accumulates information about repeated SQL work, including call counts and execution duration. It differs from pg_stat_activity, which describes what backends are doing now. Before ranking statements, decide whether the objective is shorter individual responses, less aggregate resource demand, or fewer missed deadlines. A statement averaging a few milliseconds can dominate accumulated cost when called millions of times. An infrequent statement may instead matter because it blocks an important user task for seconds. Total time, mean time and frequency therefore identify different candidates. Treat their rankings as evidence for an investigation rather than as an automatic business-priority list.

Establish collection and grouping

Availability depends on server loading through shared_preload_libraries, query-identifier calculation and database extension setup. A missing view is a configuration condition, not a measurement of zero work. Plan any necessary configuration change or restart through the ordinary deployment process. A record is grouped by more than its query identifier: database, user and whether the command is top level also matter. Decide whether those groups should be compared separately or aggregated for the question being asked. Representative SQL text describes a normalized population rather than a complete record of one invocation. Check the installed version’s columns, tracking settings and monitoring privileges before interpreting missing or zero-valued information.

Save two observations of the same population

At the beginning and end of an observation window, save timestamped counter values for the same grouping keys. Keeping existing history and calculating deltas often coexists better with other investigations than resetting shared statistics. Use consistent database and user filters, and record whether the connection reaches a primary or a replica. Query text can contain information that should not appear in a widely shared report, so preserve only the necessary detail. Align the interval with application request counts, errors, CPU observations and I/O. Those additional facts distinguish increasing totals caused by more useful traffic from deterioration in each call under comparable demand.

sql
SELECT dbid, userid, queryid, toplevel, calls, total_exec_time,
       mean_exec_time, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC LIMIT 20;

Calculate interval priorities from counter changes

Consider an invented example in which A’s calls rise from 1000 to 1600 and total_exec_time rises from 5000 to 9200 milliseconds. Six hundred interval calls accumulated 4200 milliseconds, giving a seven-millisecond mean. This is different from the lifetime mean. If B made twenty calls and accumulated 3000 milliseconds in the same window, its mean is 150 milliseconds. A is the larger aggregate candidate while B is slower individually. These figures demonstrate arithmetic rather than report a benchmark. Durations of concurrent executions can add to more than the window’s elapsed wall time without indicating a broken counter. Execution duration is not CPU duration. If the interval contains no calls, report an unavailable mean rather than manufacturing a zero-latency execution.

Detect resets and entry replacement

Counter continuity must be established, not assumed. Resets, state changes around restarts and eviction under the statement-retention limit can interrupt the history. An entry disappearing and later returning, or a negative counter difference, should not be treated as an ordinary interval. Inspect pg_stat_statements_info and relevant timestamp information available in the running version. Also distinguish planning from execution: a zero planning-time field may mean planning tracking is disabled. It does not prove planning was free. Record enabled collection rather than combining unlike measurements into one cost. Repeatedly resetting the extension for convenient comparisons can remove history needed by another operator and makes the resulting evidence harder to reproduce.

Investigate parameters and hidden latency tails

Normalization can combine calls whose literal values lead to different selectivity or plan behavior. A very large customer’s slow request can disappear inside a healthy average for ordinary customers. Mean and maximum alone cannot reconstruct a ninety-fifth percentile or the sequence of individual requests. After selecting a candidate, examine representative and problematic parameter populations with execution plans and application traces. EXPLAIN ANALYZE executes its statement, so use suitable conditions for expensive or modifying investigations. A query identifier is not a permanent business identifier across every version and definition change. Let aggregates choose the next question, then use individual observations to establish which mechanism explains the problematic case.

Validate the workload objective after a change

After changing SQL or indexes, compare the same business operation under matched input distributions, request volumes, concurrency and observation windows. Less accumulated time is not success if substantially more requests were rejected. A better mean can also coexist with more missed deadlines for important traffic. An index that improves reading may add write amplification, WAL and maintenance cost elsewhere, so inspect those paths too. Preserve the observation scope, continuity checks, candidate selection, additional causal evidence and final business metric in the review record. The extension is a powerful investigation entry point; one aggregate row cannot prove every mechanism behind latency or establish that a coincident improvement was caused by the proposed change.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path