What pg_stat_statements tells you (and what it does not)
Quick answer:
pg_stat_statementsrecords one row per normalized query (per database, user, and top-level/nested context): the query text with constants replaced by placeholders, plus cumulativecalls, 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.
How do I turn it on?
Section titled “How do I turn it on?”It ships with PostgreSQL but is not active by default. Because it needs shared memory, enabling it requires a restart:
- Add it to
shared_preload_librariesinpostgresql.conf:shared_preload_libraries = 'pg_stat_statements' - Restart PostgreSQL (a reload is not enough; the module loads at server start).
- 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.
What it captures
Section titled “What it captures”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:
| Column | What it tells you |
|---|---|
query | The normalized SQL, with literals and parameters replaced with $1, $2, … |
queryid | A hash identifying that normalized statement, stable within a server, but not guaranteed across major versions or machine architectures |
calls | How many times it ran |
total_exec_time / mean_exec_time | Cumulative and average execution time (ms) |
min_exec_time / max_exec_time / stddev_exec_time | Spread of execution time |
rows | Total rows returned or affected across all calls |
shared_blks_hit | Blocks served from the shared buffer cache |
shared_blks_read | Shared-buffer misses (OS read(), page cache or disk; Postgres cannot tell) |
wal_records / wal_bytes | Write-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.”
What it does not capture
Section titled “What it does not capture”- Individual parameter values. Normalization combines
WHERE id = 42andWHERE id = 99intoWHERE id = $1. You learn which shapes cost a lot, but not whetherid = 42specifically was slow. - Query plans. There is no
EXPLAINoutput here. A statement can be slow because the planner chose a sequential scan, butpg_stat_statementswill not show that. Use theauto_explainmodule to log plans, or runEXPLAINyourself. - 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_timemight represent one bad week or three good months. Callpg_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.
How do I use it well?
Section titled “How do I use it well?”- Reset, wait a representative window, then read the results so the totals correspond to a period you understand.
- Rank by
total_exec_timeto find the biggest aggregate cost (see identifying slow queries). - When a shape looks slow, pull the plan separately with
EXPLAIN (ANALYZE, BUFFERS).pg_stat_statementstells 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.