Comma

pg_stat_statements

pg_stat_statements is a PostgreSQL extension that keeps running statistics for every statement the server executes: how many times it ran, the total and average time it took, how many rows it returned and how many pages it read from cache or disk. It ships with PostgreSQL (in contrib) and is the usual starting point for the question "which queries should we optimise?" — before EXPLAIN ANALYZE answers "why is this one slow?"

Normalised queries

The extension doesn't store each execution. It strips constants out of the statement and groups by the result, so

SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 7;

become one row, SELECT * FROM orders WHERE customer_id = $1, with calls = 2. Each row is identified by userid, dbid and queryid (a hash of the parsed query), so the same text run by two roles is two rows.

Enabling it

The library has to be loaded when the server starts, because it allocates shared memory:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Restart, then in the database you'll query it from:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Many managed PostgreSQL services already preload the library; there only the CREATE EXTENSION step is needed. Useful settings: pg_stat_statements.max (statements tracked, default 5000 — the least used are evicted beyond that), pg_stat_statements.track (top by default; all also counts statements inside functions) and pg_stat_statements.track_planning (off by default; turn it on to fill the planning-time columns).

The columns that matter

Column Meaning
query Normalised statement text, constants as $1, $2
calls How many times it ran
total_exec_time Total execution time, milliseconds
mean_exec_time Average per call (also min_, max_, stddev_exec_time)
rows Total rows returned or affected
shared_blks_hit / shared_blks_read Pages found in PostgreSQL's cache vs fetched from the OS or disk
temp_blks_written Sorts and hashes that spilled to disk (work_mem too small)
total_plan_time Planning time — only filled with track_planning = on

PostgreSQL 13 renamed total_time and mean_time to total_exec_time and mean_exec_time, so older blog posts and dashboards fail with column "total_time" does not exist.

The top-queries query

SELECT calls,
       round(total_exec_time)                        AS total_ms,
       round(mean_exec_time::numeric, 2)             AS mean_ms,
       round(100 * total_exec_time
             / sum(total_exec_time) OVER (), 1)      AS pct,
       rows,
       left(query, 100)                              AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total time to find what costs the server most — a 2 ms lookup called a million times outranks a 5-second report run twice. Sort by mean time when you care about one request's latency. Two more views worth a glance: queries with a poor cache ratio (shared_blks_read large next to shared_blks_hit), and anything with temp_blks_written > 0.

To measure a change, reset first and compare a clean window:

SELECT pg_stat_statements_reset();

What it can't tell you

  • The plan. It records how long a statement took, not how it ran. Take the query text, fill in realistic values for $1, and run EXPLAIN (ANALYZE, BUFFERS) — or enable auto_explain to log the plans of slow statements as they happen.
  • One slow execution. Averages hide outliers; max_exec_time shows the worst, but not when or with which parameters.
  • Lock waits as a cause. Time spent waiting for a lock is counted in execution time; pg_stat_activity and pg_locks show who blocked whom.
  • History. The numbers accumulate from the last reset or restart. For trends, snapshot the view on a schedule and diff the snapshots.

From the top query to a fix

Once pg_stat_statements has named the query, EXPLAIN ANALYZE it and paste the plan into Comma's free Postgres EXPLAIN visualizer to see which node the time goes to. A Seq Scan reading a whole table to keep a few rows usually means an index that the query can't use — the causes are in Postgres not using the index. Publish the plan as a link for whoever approves the fix, and publish it again afterwards to keep the before and after side by side.

Related