pg_stat_statements explained: how it works, its limits, and what PWR adds
8 min read
How the pg_stat_statements extension works, its essential columns, its concrete limits (no history, no execution plans), and what PWR adds on top for a complete diagnosis.
pg_stat_statements in one sentence
pg_stat_statements is PostgreSQL's official extension that aggregates, in memory, the execution statistics of every normalized query (query text with constants replaced by parameters), since the last restart or reset — never storing the queries' actual results, only their execution metrics.
Installation and activation
The extension must be declared in shared_preload_libraries (which requires restarting the PostgreSQL service), then explicitly created on the target database. Without this step, no data is collected: it's the technical prerequisite most often forgotten before a first diagnosis.
shared_preload_libraries = 'pg_stat_statements'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;The essential columns to know
queryid stably identifies a normalized query (useful for finding it again between two readings). calls counts the number of calls. total_exec_time and mean_exec_time give the cumulative and average time spent executing the query. rows counts the rows processed. shared_blks_hit and shared_blks_read distinguish blocks served from cache from those read from disk — a key ratio for spotting a query that hits the disk excessively. temp_blks_written signals a sort or hash join spilling onto temporary disk for lack of sufficient work_mem.
A concrete example: isolating queries that weigh on the cache
This query ranks queries by disk read volume rather than execution time — useful for specifically targeting the queries responsible for a cache hit ratio degradation.
SELECT query, calls, shared_blks_read, shared_blks_hit,
round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;The concrete limits of pg_stat_statements
pg_stat_statements keeps no history over time: its counters are cumulative since the last reset, with no notion of "before" and "after" a specific incident — a pg_stat_statements_reset() wipes out the entire available history at once, including that of an incident you were precisely trying to analyze after the fact.
The extension records no execution plan (EXPLAIN must be run separately), no information about locks, and no time granularity: it's impossible to know, from pg_stat_statements alone, whether a query's average time doubled overnight or gradually over several weeks.
Finally, query normalization can group queries with very different performance profiles under the same queryid if only the textual structure is identical, which can hide expensive edge cases inside a reassuring overall average.
What PWR adds on top of pg_stat_statements
PWR periodically captures the state of pg_stat_statements into persisted, timestamped snapshots, never depending on a reset that would erase the history. Comparing two snapshots (before/after a deployment, or on either side of an incident) lets you compute an exact delta — how many calls, how much CPU time, how many rows — for that precise window, where pg_stat_statements alone only gives an overall cumulative total since the last restart.
The PWR report also cross-references this data with pg_stat_activity (active sessions, locks) and pg_buffercache, to distinguish a genuinely expensive query from one simply blocked by a lock — a nuance pg_stat_statements alone cannot provide. The Query History popup goes further by tracing, query by query, its evolution snapshot after snapshot, to precisely date when a regression appeared.