How to Find Slow Postgres Queries with pg_stat_statements

Every Postgres DBA eventually hits the same wall: the database is slow, nobody knows which query is responsible, and the monitoring dashboard shows CPU at 100% but not which statement caused it. pg_stat_statements — the extension that ships inside Postgres itself — is the fastest way through that wall.

It records every query that runs on your instance, along with the calls, timings, and rows each one produced. This guide shows you how to enable it, which columns actually matter, and how to turn its raw output into a short list of indexes and rewrites worth doing.

What pg_stat_statements actually records

The extension keeps a rolling table of normalized statements: the literal values are stripped out, so SELECT * FROM orders WHERE id = 42 and SELECT * FROM orders WHERE id = 7 collapse into a single entry. That normalization is what makes it possible to rank "the slowest queries" rather than "the slowest query executions".

Each entry tracks, among other things:

• calls — how many times the statement ran in the window
• total_exec_time / mean_exec_time — total and average execution time
• rows — rows returned or affected per execution
• shared_blks_hit / shared_blks_read — how much of the work hit the buffer cache versus disk
• queryid — a stable fingerprint you can join against other stats

Because it is a fixed-size table, old entries roll off — the default is 5000 statements. Long-running instances should reset it periodically (the query list in pg_stat_statements_info tells you when the last reset happened).

Enabling it

Add the extension to shared_preload_libraries in postgresql.conf and restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

After the restart, create the view and you are live:

CREATE EXTENSION pg_stat_statements;

Two settings worth tuning while you are there: pg_stat_statements.track (all or top) and pg_stat_statements.max, the number of statements retained.

Your first triage query

This is the query most teams run first — it ranks statements by total time, the best proxy for "what is costing us the most":

SELECT
  queryid,
  round(total_exec_time / 1000, 1)  AS total_ms,
  calls,
  round(mean_exec_time / 1000, 1)   AS mean_ms,
  round(shared_blks_read * 8 / 1024, 1) AS read_mb,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Two numbers deserve attention, not one. A query with a huge total_ms but a modest mean_ms is called a lot — the win is shaving milliseconds off a hot path. A query with a high mean_ms and low calls is a slow outlier — the win is fixing one plan.

From stats to decisions

Now rank your top statements and check the plan for the usual suspects:

• Seq scans on large tables. The classic missing-index signal. An index on the filtered column usually converts the scan to an index lookup.
• Hash aggregates over wide ranges. Often fixable with a composite index matching the filter, or a query rewrite.
• High shared_blks_read. The buffer cache is missing the working set — a caching or memory configuration problem rather than a SQL one.
• SELECT * on wide tables. Narrowing the column list can cut transfer size dramatically, especially when bytea or JSON columns are involved.

Where pg_stat_statements falls short

The extension tells you what ran and how long it took. It does not tell you:

• whether the plan changed between yesterday and today,
• what an optimization would be worth before you apply it,
• or what a slow query is costing you in actual dollars.

That is the gap Praxion fills, and nothing more than that. The agent runs inside your network, reads pg_stat_statements from your read-only replica, and turns the raw stats into suggestions ranked by predicted saving × confidence — the table it touches, the money it is predicted to be wasting per month, how sure the system is, and the DDL to fix it. Row values never leave your network; what travels is anonymized metadata. The triage you would have done in a spreadsheet is the part that gets done for you.