What is pg_stat_statements and How to Use It

pg_stat_statements is the closest thing PostgreSQL has to Oracle's AWR for SQL. It's a bundled extension that records aggregated execution statistics for every query the server runs — total time, calls, rows, I/O, planning time — grouped by a normalized query fingerprint. If you want to know which statements are actually costing you, this is where you look.

It's the de-facto first stop for PostgreSQL performance work, and it's free in every distribution.

How it works

The extension normalizes each query by stripping constants, so these two:

SELECT * FROM orders WHERE id = 42;
SELECT * FROM orders WHERE id = 99;

collapse into a single tracked entry: SELECT * FROM orders WHERE id = $1. It then accumulates counters for that fingerprint across all executions. The view is cumulative since the last reset, exactly like Oracle's v$ and AWR counters.

Installing it

pg_stat_statements must be loaded at server start, so it goes in shared_preload_libraries. Edit postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top      # top-level statements (default)
pg_stat_statements.max  = 5000      # number of statements tracked

Restart PostgreSQL (this parameter requires a full restart), then create the extension in the database you want to inspect:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify it's live:

SELECT count(*) FROM pg_stat_statements;

The columns that matter

The view has many columns; these are the ones you'll use constantly:

Version note: before PostgreSQL 13 the timing columns were named total_time / mean_time and didn't separate planning from execution. On 13+ you get total_exec_time and total_plan_time as distinct columns.

Example queries

Top 10 statements by total time

This is the single most valuable query — it surfaces the SQL consuming the most server time overall, which is what you should tune first.

SELECT round(total_exec_time::numeric, 1) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

Slowest queries on average

Different question — which statements are slow per execution, even if rarely called:

SELECT round(mean_exec_time::numeric, 2) AS mean_ms,
       calls,
       query
FROM   pg_stat_statements
WHERE  calls > 50            -- ignore one-off noise
ORDER  BY mean_exec_time DESC
LIMIT  10;

Worst cache hit ratio

Statements doing the most physical I/O are prime indexing candidates:

SELECT query,
       shared_blks_read,
       shared_blks_hit,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM   pg_stat_statements
WHERE  shared_blks_read > 0
ORDER  BY shared_blks_read DESC
LIMIT  10;

Exporting the data

To analyze offline or hand it to a tool, dump the view to CSV from psql:

\copy (SELECT * FROM pg_stat_statements) TO 'pgss.csv' WITH CSV HEADER

Resetting the counters

Because the stats are cumulative, reset them before a controlled test so the numbers reflect only the window you care about:

SELECT pg_stat_statements_reset();

Reading it well

A long list of statements and counters is raw material, not a diagnosis. The work is interpreting it: is the top query slow because it's missing an index, scanning too many rows, or simply called a million times? DBA Copilot takes a pg_stat_statements export and does that interpretation for you — ranking the real offenders, explaining why each is expensive, and proposing concrete fixes you can apply.