Skip to content

What pg_stat_statements tells you (and what it does not)

Last updated

View as Markdown

Quick answer: pg_stat_statements records one row per normalized query (per database, user, and top-level/nested context): the query text with constants replaced by placeholders, plus cumulative calls, execution time, rows, and shared-buffer cache hits since the last reset. It does not record individual parameter values, query plans, or a point-in-time snapshot. It shows which query shapes cost the most, not which specific execution was slow.

It ships with PostgreSQL but is not active by default. Because it needs shared memory, enabling it requires a restart:

  1. Add it to shared_preload_libraries in postgresql.conf:
    shared_preload_libraries = 'pg_stat_statements'
  2. Restart PostgreSQL (a reload is not enough; the module loads at server start).
  3. Create the extension in each database you want to inspect:
    CREATE EXTENSION pg_stat_statements;

On managed platforms (RDS, Cloud SQL, Azure), it is often preloaded already. You may only need step 3.

Each row aggregates every execution of one normalized statement by a given user in a given database. Top-level and nested (in-function) calls are counted separately:

ColumnWhat it tells you
queryThe normalized SQL, with literals and parameters replaced with $1, $2, …
queryidA hash identifying that normalized statement, stable within a server, but not guaranteed across major versions or machine architectures
callsHow many times it ran
total_exec_time / mean_exec_timeCumulative and average execution time (ms)
min_exec_time / max_exec_time / stddev_exec_timeSpread of execution time
rowsTotal rows returned or affected across all calls
shared_blks_hitBlocks served from the shared buffer cache
shared_blks_readShared-buffer misses (OS read(), page cache or disk; Postgres cannot tell)
wal_records / wal_bytesWrite-ahead-log volume generated (PostgreSQL 13+)

(On PostgreSQL 12 and earlier, the timing columns are total_time / mean_time; 13 renamed them to *_exec_time and added planning-time columns.)

The cache hit ratio for a statement is shared_blks_hit / (shared_blks_hit + shared_blks_read). A ratio near 1.0 means the data was already in shared buffers. A low ratio means many buffer misses; treat that as “missed Postgres buffers,” not “hit the disk.”

  • Individual parameter values. Normalization combines WHERE id = 42 and WHERE id = 99 into WHERE id = $1. You learn which shapes cost a lot, but not whether id = 42 specifically was slow.
  • Query plans. There is no EXPLAIN output here. A statement can be slow because the planner chose a sequential scan, but pg_stat_statements will not show that. Use the auto_explain module to log plans, or run EXPLAIN yourself.
  • A point-in-time snapshot. Every number is a cumulative total since the last reset (a clean restart keeps them by default). A big total_exec_time might represent one bad week or three good months. Call pg_stat_statements_reset() and measure a known window if you need “right now.”
  • Currently-running queries. For live activity, including what is executing this second and what is blocked, use pg_stat_activity, not this view.
  • Reset, wait a representative window, then read the results so the totals correspond to a period you understand.
  • Rank by total_exec_time to find the biggest aggregate cost (see identifying slow queries).
  • When a shape looks slow, pull the plan separately with EXPLAIN (ANALYZE, BUFFERS). pg_stat_statements tells you which query to investigate; the plan tells you why.

Why does this matter for AI-generated code?

Section titled “Why does this matter for AI-generated code?”

An AI coding agent reasons over your source, where each query is a concrete string with real values. pg_stat_statements shows aggregated shapes without values, using cumulative data from real traffic. The agent cannot know that one normalized shape it wrote accounts for 40% of database time until the code has run against production data at production scale.

  • Does pg_stat_statements show query plans?

    No. It records execution statistics, not plans. To capture plans automatically, use the auto_explain module; to see the plan for one query, run EXPLAIN yourself.

  • Can pg_stat_statements show which parameter value was slow?

    No. It normalizes queries, constants and parameters are replaced with placeholders like $1, so all calls with the same shape aggregate into one row. You see the query shape and its totals, not which specific value was slow.

  • How do I compute the cache hit ratio from pg_stat_statements?

    Use shared_blks_hit / (shared_blks_hit + shared_blks_read) per statement. A ratio near 1 means the data was in shared buffers. A low ratio means buffer misses, an OS read, not proof of disk I/O (the page may still be in the OS cache).

  • Is pg_stat_statements a live view of running queries?

    No. It is cumulative aggregate history since the last reset. For queries running right now, use pg_stat_activity instead.