Find Slow PostgreSQL Queries with pg_stat_statements

Enable the extension on a self-managed server

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# Restart PostgreSQL, then run:
create extension if not exists pg_stat_statements;

shared_preload_libraries requires a server restart. Managed providers may preload or expose the extension through their own settings.

Find the largest cumulative cost

select
  queryid,
  calls,
  round(total_exec_time::numeric, 1) as total_ms,
  round(mean_exec_time::numeric, 1) as mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  query
from pg_stat_statements
where calls > 0
order by total_exec_time desc
limit 20;

Start with total time, then change the question

A query that averages 8 milliseconds but runs ten million times can cost more than a report that takes three seconds once a day. Total execution time finds cumulative load. Mean execution time finds painful individual calls. Call count explains how those two measures interact.

Do not optimize solely by rank. Tie each query to an application path, business importance, and acceptable latency before changing SQL or indexes.

Use buffers as a clue

shared_blks_read counts blocks read into shared buffers. shared_blks_hit counts requests satisfied there. High reads can point to large scans or a working set that does not stay cached; high hits can still represent excessive CPU and repeated memory work.

The counters are cumulative for the tracked entry. Divide by calls for a rough per-execution view, but remember that workloads and cache state change during the interval.

Compare work per call

select
  queryid,
  calls,
  round((total_exec_time / calls)::numeric, 2) as ms_per_call,
  round((shared_blks_read::numeric / calls), 2) as reads_per_call,
  round((shared_blks_hit::numeric / calls), 2) as hits_per_call,
  query
from pg_stat_statements
where calls >= 100
order by reads_per_call desc
limit 20;

Know when statistics started

Results are meaningful only with a known observation window. Server restarts, extension resets, entry eviction, deployments, and traffic changes can all alter the sample. Record the reset time with every exported report.

Use pg_stat_statements_reset() only when you intentionally want a new baseline and have permission to discard the existing history. A weekly comparison is usually more useful than clearing the view whenever a query changes.

Move from a query pattern to a plan

pg_stat_statements normalizes literal values, so copy the query and substitute representative parameters. Use EXPLAIN without ANALYZE first for writes. For reads, EXPLAIN (ANALYZE, BUFFERS) shows where estimates, loops, and block activity diverge.

Follow the EXPLAIN ANALYZE reading guide and save the before-and-after plans with the SQL change.

Keep query text exposure in mind

The view contains normalized SQL text and can reveal table names and application behavior. PostgreSQL restricts some fields based on privileges, but database monitoring access should still be treated as sensitive.

Use an administrative connection, avoid publishing raw query exports, and remove comments or identifiers that contain customer or operational information.