pg_stat_statements Tutorial: Find Slow PostgreSQL Queries

When your app gets slow, the question is: which queries are responsible? Guessing is unreliable. pg_stat_statements is a Postgres extension that tracks every distinct query the database runs — how often, how long, how many rows — so you can see exactly where time goes.

Enable it

It ships with Postgres but must be loaded at server start. In postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

Restart Postgres, then in your database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Managed providers (Supabase, Neon, RDS and others) usually have it enabled or a toggle for it.

How it groups queries

Queries are normalised: constants are replaced by placeholders, so these count as one entry:

SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 97;
-- → SELECT * FROM orders WHERE customer_id = $1

The most useful query: where does the time go?

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

Sort by total time first. The query to fix is often not the slowest single query but a fast one called millions of times — a 3 ms query run 2 million times a day costs more than a 2-second report run twice.

Other views worth checking

Slowest on average (bad for user-facing latency):

SELECT calls, round(mean_exec_time::numeric, 1) AS mean_ms, left(query, 120)
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 15;

Most called (candidates for caching, or N+1 problems):

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 120)
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 15;

A simple SELECT ... WHERE id = $1 at the top with an enormous call count often means an N+1 loop in your code. (The N+1 query problem)

Reading from disk instead of memory:

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

Lots of shared_blks_read means data isn't cached — large scans, or a server short on memory.

From finding to fix

For each expensive query:

Copy it, substitute realistic values, run EXPLAIN (ANALYZE, BUFFERS). (Reading EXPLAIN ANALYZE)
Look for sequential scans on big tables, big sorts, bad row estimates.
Typical fixes:
- add the right index (Database indexes, Postgres index types)
- fix N+1 patterns with a join or batch query
- add pagination or limits (API pagination)
- cache results (What is caching?)
- precompute reports (Materialized views)
- update stale statistics with ANALYZE (Planner statistics)

Measure before and after

Reset the statistics after a fix so new numbers aren't mixed with old:

SELECT pg_stat_statements_reset();

Or snapshot the view into a table periodically and compare over time.

Notes

Stats survive restarts (saved to disk on clean shutdown).
The number of tracked statements is capped (pg_stat_statements.max, default 5000); rarely-run queries get evicted.
Query text can contain sensitive literals in some cases; restrict access to admins.
Overhead is small and generally considered safe for production.

Complement it with

log_min_duration_statement = 500 to log individual slow queries with their actual parameters.
pg_stat_activity for what's running right now. (504 Gateway Timeout)

EasySpawn gives each app its own Postgres with Claude Code on the same server — ask it which queries cost the most, and it can read pg_stat_statements, explain the plan and propose the index. See how it works or join the waitlist.

pg_stat_statements: Find the Queries Slowing Down Your Postgres