Reading query workload with pg_stat_statements

Understand

Mental model

Statement statistics aggregate normalized query fingerprints. Total execution time identifies workload weight, mean time describes the typical call, calls reveal frequency, and block or temporary counters show resource demand. These aggregates tell you where to investigate; an execution plan explains why a particular execution behaved as it did.

Calibrate

What healthy usually looks like

The statements consuming the most total time are known and expected for the workload.
High-frequency statements remain cheap enough that their aggregate cost is controlled.
Temporary block writes and physical reads are understood rather than silently growing.

Observe

Ask the database a bounded question

Find the statements responsible for the largest share of database execution time in the current statistics window.

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 1) AS total_exec_time_ms,
  round(mean_exec_time::numeric, 2) AS mean_exec_time_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  temp_blks_written,
  left(query, 160) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

low observation cost

The extension must be loaded through `shared_preload_libraries`, query IDs must be enabled, and `CREATE EXTENSION pg_stat_statements` must be run in the database.

Interpret

Read the output

Always capture `pg_stat_statements_info.stats_reset`; a ranking without the observation window can be misleading.
Use total time for capacity work, mean and variability for latency work, and calls for workload-shape work.
Compare query fingerprints across deploys to detect regressions, then compare plans and row-estimate accuracy.

Avoid

Common traps

Sorting only by maximum or mean latency and ignoring total workload cost.
Creating an index from the query text without checking the plan, data distribution, write cost, and existing indexes.
Comparing statistics captured across different reset windows.

Verify

Check your reasoning

Continue

Related runbooks

Verify independently

Sources

PostgreSQL 18: pg_stat_statements