Every Postgres instance ships with a built-in query performance profiler. It runs by default on most managed databases, costs almost nothing to enable on self-hosted instances, and surfaces exactly the data you need to find slow queries before users start complaining. Almost nobody reads it.
Extensionpg_stat_statements (contrib)Load viashared_preload_librariesResetpg_stat_statements_reset()Available sincePostgres 8.4 (improved significantly in 9.2+)
Enabling pg_stat_statements
On self-hosted Postgres, add to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000Then restart Postgres and run:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;On managed services (RDS, Aurora, Cloud SQL, Supabase), the extension is usually pre-loaded. Just run the CREATE EXTENSION command in your target database.
The track = all setting captures queries from nested function calls, not just top-level statements. The default track = top misses PL/pgSQL procedures that are doing expensive work inside functions you're calling from application code.
The Key Columns
The view has many columns. These are the ones that matter for day-to-day performance work:
calls — how many times this query has been executed since the last reset. High call count with low total time means you have a fast query called very often; this is usually fine. Low call count with high total time means you have a rare but expensive query.
total_exec_time — cumulative execution time in milliseconds. This is the number to sort by when you want to find what's costing you the most overall. One query that runs 100ms and is called 10,000 times contributes 1,000,000ms to total load. Sort descending by this column first.
mean_exec_time — average execution time per call. Use this alongside total_exec_time. A query with high mean_exec_time but low calls is worth investigating for an index or query rewrite. A query with low mean_exec_time but enormous calls is a different class of problem (usually an N+1 pattern or missing caching).
rows — total rows returned or affected. Dividing rows / calls gives average row count per execution. A query returning 50,000 rows on average when you expect 10 is a filter or join selectivity problem.
shared_blks_hit — cache hits against shared_buffers. What you want: most data served from cache.
shared_blks_read — cache misses; these go to OS page cache or disk. High shared_blks_read relative to shared_blks_hit for a specific query is a strong signal for a missing index or working set larger than shared_buffers.
The Diagnostic Query You Actually Run
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((shared_blks_read * 8 / 1024.0)::numeric, 2) AS read_mb,
round((shared_blks_hit * 100.0 / NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct,
left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 5
ORDER BY total_exec_time DESC
LIMIT 20;This gives you the top 20 queries by total time, with their cache hit rate. Queries with cache_hit_pct below 90% are worth examining for index improvements. The calls > 5 filter removes one-off migrations and setup queries that appear expensive but aren't representative.
pg_stat_statements_reset
The view accumulates since the last reset. On a busy system, the counters grow for weeks and the data reflects aggregate history, not recent behavior. After a schema change or a new index, reset the counters to start fresh:
SELECT pg_stat_statements_reset();You can also reset a single query by passing its queryid. This is useful when you've fixed one specific problem and want to verify the fix without losing data on other queries.
The dealloc Column
When pg_stat_statements.max is reached, the extension starts dropping the least-executed queries to make room. The dealloc column in pg_stat_statements_info (Postgres 14+) shows how many entries have been dropped. If dealloc is non-zero and growing, increase pg_stat_statements.max. The default of 5000 is often too low for applications with many distinct query shapes.
queryid Normalization
pg_stat_statements normalizes queries by replacing literal values with parameters. SELECT * FROM users WHERE id = 1 and SELECT * FROM users WHERE id = 99 share the same queryid. This is what you want: it groups identical query shapes together regardless of the specific values passed.
The implication: if you're building dynamic SQL with literal values interpolated into query strings (not parameterized), each distinct value combination creates a separate entry. A query like WHERE status = 'pending' hardcoded in different parts of the codebase may appear as different entries. This is also a code smell worth fixing separately.
What pg_stat_statements Does NOT Show
The extension captures aggregate statistics, not individual query executions. It will not tell you:
- Per-execution plans. The same query can use different plans on different executions. pg_stat_statements shows the aggregate;
EXPLAIN ANALYZEshows a single run. - Parameter values. The query is normalized. You see the query shape, not which specific user IDs or dates triggered the slow execution.
- Lock wait time. Queries blocked on locks look idle to pg_stat_statements during the wait.
pg_stat_activityandpg_locksshow the lock contention separately. - Which session ran the query. The view aggregates across all connections. For per-session analysis, look at
pg_stat_activitywhile queries are running.
The Pattern
pg_stat_statements is most useful as a weekly habit rather than an incident response tool. Enable it, let it accumulate, and run the diagnostic query every week. The queries that migrate to the top of the list — especially those with rising total_exec_time and falling cache_hit_pct — are telling you something about data growth patterns before users notice the slowdown. That's the window where adding an index takes 5 minutes instead of 5 hours of emergency work during an outage.
Building in public at builds.anethoth.com.