Analyze PostgreSQL Query Times with pg_stat_statements

calendar Aug 29, 2024 / · 8 min read · postgresql performance performance tuning pg_stat_statements pg_database pg_roles  ·
Share on: linkedin copy

Analyze PostgreSQL Query Times with pg_stat_statements

PostgreSQL 13 renamed the timing columns of pg_stat_statements: total_time became total_exec_time, mean_time became mean_exec_time, and the same suffix went onto the minimum, maximum and standard deviation columns. A query copied from an older tutorial fails on a current server with a missing-column error, and the reverse is true for new queries on old servers. The script below uses the current names and ranks every tracked statement by how long it takes to execute.

Purpose and Overview

pg_stat_statements is the historical counterpart to the live pg_stat_activity view. Where the live slow-query monitor shows what is running at this instant, this view shows what the server has spent its time on since statistics were last reset. It keeps one row per distinct combination of database, role, normalized statement and (from PostgreSQL 14) top-level flag, and each row accumulates the number of calls, the rows returned, and the time spent.

Normalization is what makes the view useful. Statements that differ only in literal constants are folded into one entry, with the constants shown as parameters such as $1. A thousand variations of WHERE id = 42 appear as a single row with a thousand calls, so the ranking reflects query shapes rather than individual executions.

The script returns the call count, rows, and total, mean, minimum, maximum and standard deviation of execution time converted to seconds, followed by the query text. The standard deviation is the interesting column: a low mean with a high deviation describes a statement that is usually fast and sometimes terrible, which a mean alone hides.

Sample Code

 1CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 2
 3SELECT
 4  calls,
 5  rows,
 6  ROUND((total_exec_time::numeric / 1000), 4)  AS total_secs,
 7  ROUND(mean_exec_time::numeric / 1000, 4)     AS average_secs,
 8  ROUND(min_exec_time::numeric / 1000, 4)      AS min_secs,
 9  ROUND(max_exec_time::numeric / 1000, 4)      AS max_secs,
10  ROUND(stddev_exec_time::numeric / 1000, 4)   AS stddev_secs,
11  query
12FROM
13  pg_stat_statements
14ORDER BY
15  average_secs DESC,
16  calls DESC,
17  rows DESC
18LIMIT 100;

Ranking by mean time finds statements that are slow every time. Ranking by cumulative time finds the statements that consume the most of the server, with the database and role resolved to names:

 1SELECT
 2  d.datname,
 3  r.rolname,
 4  s.calls,
 5  ROUND(s.total_exec_time::numeric, 1)  AS total_ms,
 6  ROUND(s.mean_exec_time::numeric, 2)   AS mean_ms,
 7  ROUND((100 * s.total_exec_time / SUM(s.total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
 8  LEFT(s.query, 80)                     AS query
 9FROM pg_stat_statements s
10JOIN pg_database d ON d.oid = s.dbid
11JOIN pg_roles    r ON r.oid = s.userid
12ORDER BY s.total_exec_time DESC
13LIMIT 20;

On PostgreSQL 12 and earlier, the first query needs the old column names:

 1SELECT
 2  calls,
 3  rows,
 4  ROUND((total_time / 1000)::numeric, 4)   AS total_secs,
 5  ROUND((mean_time / 1000)::numeric, 4)    AS average_secs,
 6  ROUND((min_time / 1000)::numeric, 4)     AS min_secs,
 7  ROUND((max_time / 1000)::numeric, 4)     AS max_secs,
 8  ROUND((stddev_time / 1000)::numeric, 4)  AS stddev_secs,
 9  query
10FROM pg_stat_statements
11ORDER BY average_secs DESC
12LIMIT 100;

Notes: The first two queries need PostgreSQL 13 or later; the third uses the pre-13 column names. The timing columns are in milliseconds, so dividing by 1000 gives seconds. Seeing the query text and queryid of other roles' statements requires a superuser or a role with pg_read_all_stats.

Code Breakdown

CREATE EXTENSION is not enough

CREATE EXTENSION pg_stat_statements creates the view and functions in one database. Collection itself happens in a module that must be loaded at server start through shared_preload_libraries in postgresql.conf, because it reserves shared memory. Adding or removing the module therefore needs a restart.

Query identifier calculation must also be on. It switches on automatically when compute_query_id is auto or on, or when a third-party module computes identifiers. Statistics span every database on the server, even though the view is installed per database.

Millisecond columns and the rounding

Every *_exec_time column is double precision and measured in milliseconds. The two-argument ROUND() takes numeric, not double precision, which is why each expression casts with ::numeric first. The cast and the division by 1000 only change presentation, and the second query keeps milliseconds because short statements read better that way.

Min, max and standard deviation

min_exec_time and max_exec_time are single observations: the fastest and slowest execution since statistics began. stddev_exec_time is a population standard deviation. A statement with a mean of 20 ms and a maximum of 40 seconds is a very different problem from one with a steady 20 ms, and only the spread columns tell them apart.

Ordering and limit

The first query sorts by mean, then by call count, then by rows, and keeps 100 rows. Sorting by mean puts rarely executed statements at the top, including something that ran once during a deployment. Sorting by total_exec_time weights frequency, which is closer to what a busy server feels.

Share of total time

The window expression SUM(...) OVER () sums the total across all rows before LIMIT applies, so pct_of_total is a true share of tracked execution time. It answers the practical question of how much load disappears if one statement is fixed.

Key Statistics Concepts

Cumulative since the last reset

Every counter is a running total. Nothing in the view records when a slow execution happened, and a statement that has run for months dilutes a recent regression. pg_stat_statements_reset() discards the statistics, and the pg_stat_statements_info view (PostgreSQL 14 and later) records when that last happened. Taking two snapshots into a temporary table and comparing them gives a rate over a window without discarding anything.

Only completed executions

For a query that is still running, pg_stat_activity is the view to check; pg_stat_statements is for what has already run.

The query text and queryid

The text stored in each row is that of the first statement seen with a given queryid. The identifier is a more stable key than the text, since the same text can mean different things under different search_path settings. Its stability is limited, though: values are generally stable between minor releases on the same machine architecture and are not safe to compare across major versions.

Planning time and track_planning

PostgreSQL 13 added planning counters, including plans, total_plan_time and mean_plan_time. They stay at zero unless pg_stat_statements.track_planning is enabled, which is off by default and can cost noticeable performance when many connections run statements of identical structure at once.

Practical Applications

Finding the statements worth tuning first

Run the second query after a representative period of load. The top row with a large pct_of_total is the best return on tuning effort even if its mean looks modest, because it runs often. Take that statement to EXPLAIN (ANALYZE, BUFFERS) with realistic parameter values.

Validating a change

After adding an index or rewriting a query, reset the statistics for that statement with pg_stat_statements_reset(userid, dbid, queryid) and wait for real traffic. The new mean and maximum describe the changed statement alone, with no contribution from before the change.

Detecting unstable plans

A large gap between mean_exec_time and max_exec_time, together with a high stddev_exec_time, is the signature of a plan that flips depending on parameter values or statistics. That statement is a candidate for EXPLAIN on both the fast and slow input.

Capacity and trend reporting

Sampling the view on a schedule and storing the deltas turns a cumulative view into a time series. Per-database and per-role totals from the second query make a useful weekly summary.

Version Compatibility

The timing columns changed names in PostgreSQL 13. Up to 12 they are total_time, min_time, max_time, mean_time and stddev_time; 13 renamed them to the _exec_time forms and added the planning columns and wal_bytes. PostgreSQL 14 added the toplevel column, which separates statements run directly from those run inside functions, and 15 added the JIT counters such as jit_functions.

PostgreSQL 17 adds stats_since and minmax_stats_since, splits the block I/O timing columns into names like shared_blk_read_time, and gives pg_stat_statements_reset() a minmax_only argument that clears only the minimum and maximum values. Since the minimum and maximum are single observations, being able to reset them without discarding totals helps when watching for new spikes.

On any version, SELECT * FROM pg_stat_statements LIMIT 0 returns the exact column list for the server in front of you, which settles a naming question in seconds.

Best Practices

  • Preload the module early — add pg_stat_statements to shared_preload_libraries at build time, since enabling it later costs a restart.
  • Rank by more than one column — total time finds the heaviest load, mean time finds the slow statements, and standard deviation finds the unpredictable ones.
  • Reset deliberately — note when pg_stat_statements_reset() ran, and on PostgreSQL 14 and later read pg_stat_statements_info before trusting a comparison.
  • Watch for eviction — the dealloc column in pg_stat_statements_info counts how often entries for the least-executed statements were dropped because more distinct statements than pg_stat_statements.max were seen.
  • Grant read access narrowly — the pg_read_all_stats role exposes statement text, and statements that cannot be normalized can show literal constants.
  • Follow up with the live view — a suspicious row is a lead, and pg_stat_activity shows whether the same statement is slow right now.

References

Posts in this series