pg_stat_statements

The pg_stat_statements extension records query-level performance data across all databases on a PostgreSQL instance. Every time a query runs, its statistics are aggregated by a normalized query fingerprint (literals are replaced with parameters). This makes it easy to identify which query patterns consume the most time, even across millions of executions.

Enabling pg_stat_statements

The extension ships with PostgreSQL but must be explicitly loaded. Add it to postgresql.conf and create the extension:

-- In postgresql.conf (requires restart)
shared_preload_libraries = 'pg_stat_statements'

-- After restarting, create the extension in the target database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify it is active:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Finding the slowest queries by mean execution time

SELECT
  LEFT(query, 80)   AS query_snippet,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

This surfaces queries with the highest average latency—candidates for index improvements or query rewrites.

Finding queries that consume the most total time

SELECT
  LEFT(query, 80)   AS query_snippet,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2)  AS mean_ms,
  ROUND((total_exec_time / SUM(total_exec_time) OVER ()) * 100, 1) AS pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Total time highlights the biggest contributors to overall database load—even fast queries can dominate if called millions of times.

Identifying cache-unfriendly queries

SELECT
  LEFT(query, 80)     AS query_snippet,
  calls,
  shared_blks_read    AS disk_reads,
  shared_blks_hit     AS cache_hits,
  ROUND(
    shared_blks_hit::numeric /
    NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 1
  ) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

A low cache_hit_pct means the query is reading data from disk frequently—consider adding an index or increasing shared_buffers.

Resetting statistics

-- Reset statistics for all queries (superuser only)
SELECT pg_stat_statements_reset();

-- Reset statistics for a single query by queryid (PostgreSQL 14+)
SELECT pg_stat_statements_reset(userid, dbid, queryid);

Reset statistics after a schema change or tuning effort so you get a clean baseline for the next measurement period.

Practical tips

• Set pg_stat_statements.track = all (in postgresql.conf) to also track queries inside stored procedures and functions, not just top-level statements.
• The query column normalizes literal values—WHERE id = 1 and WHERE id = 2 appear as the same fingerprint. Use queryid for programmatic lookups.
• Statistics persist across connections but reset on server restart unless pg_stat_statements.save = on (default: on).
• Combine with EXPLAIN (ANALYZE, BUFFERS) on the top offending queries to pinpoint exactly where time is spent.