Skip to main content

What pg_stat_statements tells you (and what it doesn't)

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 tells you which query shapes cost the most, not which specific execution was slow.

How do I turn it on?​

It ships with PostgreSQL but isn't active by default, and it needs shared memory — which means a restart:

  1. Add it to shared_preload_libraries in postgresql.conf: 
    shared_preload_libraries = 'pg_stat_statements'
  2. Restart PostgreSQL (a reload isn't 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's often preloaded already — you may only need step 3.

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:

ColumnWhat it tells you
queryThe normalized SQL — 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 can't 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) — near 1.0 means the data was already in shared buffers. A low ratio means lots of buffer misses; treat that as "missed Postgres buffers," not "hit the disk."

What it does not capture​

This is where people get misled:

  • Individual parameter values. Normalization is the whole point: WHERE id = 42 and WHERE id = 99 collapse into WHERE id = $1. You learn the shape costs a lot; you cannot learn that 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, and pg_stat_statements will not tell you 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 (or server start). A big total_exec_time might be 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 — what's executing this second, what's blocked — use pg_stat_activity, not this view.

How do I use it well?​

  • Reset, wait a representative window, then read — so the totals map 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?​

An AI coding agent reasons over your source, where every query is a concrete string with real values. pg_stat_statements is the opposite view: aggregated shapes, no values, cumulative over real traffic. The two never meet. The agent cannot know that one normalized shape it wrote now accounts for 40% of database time — that fact only exists in the aggregate, after the code has run against production data at production scale.

How DBGorilla helps​

DBGorilla connects read-only and hands your AI coding agent (Claude Code, Cursor) the real pg_stat_statements rows — the normalized shapes, call counts, timing, and cache-hit numbers — so it can reason about production cost instead of the source alone, and tell you which shape to pull a plan for next. It surfaces and explains; it never writes to your database. Get started free →

FAQ​

Does pg_stat_statements show query plans? No — it records execution statistics, not plans. Use auto_explain to log plans automatically, or run EXPLAIN on the query yourself.

Can pg_stat_statements show which parameter value was slow? No. It normalizes queries (WHERE id = $1), so all calls of the same shape aggregate into one row. You get the shape and its totals, not the specific value.

How do I compute the cache hit ratio? Per statement, shared_blks_hit / (shared_blks_hit + shared_blks_read). Near 1.0 means served from shared buffers; low means buffer misses — not proof of disk I/O.

Is pg_stat_statements a live view of running queries? No — it's cumulative history since the last reset. For live queries use pg_stat_activity.