How to Use pg_stat_statements to Find Slow Postgres Queries

Most Postgres performance problems are caused by a small number of queries that run frequently or take a long time. The hard part is identifying which queries those are. Application-level logging can help, but it does not capture queries that come from background jobs, ORM internals, or third-party libraries. pg_stat_statements solves this by tracking query statistics directly in the database, for every query, regardless of where it originated.

What Is pg_stat_statements?

pg_stat_statements is a Postgres extension that records cumulative execution statistics for every unique query plan. It tracks total calls, total and average execution time, row counts, cache hit rates, and more — all aggregated per normalized query (parameter values are replaced with placeholders so WHERE id = 1 and WHERE id = 2 count as the same query).

It is one of the most useful tools in Postgres and is available on all major managed Postgres providers.

Enabling pg_stat_statements

Add it to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart Postgres (a reload is not sufficient for shared_preload_libraries), then create the extension in your database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On managed Postgres services (RDS, Cloud SQL, Supabase, Neon), the extension is usually pre-installed. Check whether it is already enabled:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Finding the Slowest Queries by Total Time

Total execution time is the most actionable metric for most applications. A query that takes 1 second and runs 100 times per minute contributes 100 seconds of database time per minute. A query that takes 10 seconds but runs once a day is far less important.

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) AS pct_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This returns the top 20 queries by total CPU time consumed, along with the percentage of total database time each one represents. Start with the highest total_ms rows — these are the ones worth optimizing first.

Finding Queries with the Worst Average Latency

Some queries are slow every time they run. These affect user-facing response times directly:

SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

The calls > 50 filter removes one-off queries that skew results. stddev_ms reveals whether latency is consistent or spiky — high standard deviation means the query sometimes runs fast and sometimes runs slow, which usually points to cache miss patterns or lock contention.

Identifying Cache Efficiency

Postgres stores frequently accessed data in its shared buffer cache. Queries that constantly miss the cache and read from disk are slower and put more load on storage.

SELECT
  query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(
    shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0) * 100,
    2
  ) AS cache_hit_pct
FROM pg_stat_statements
WHERE calls > 100
ORDER BY cache_hit_pct ASC
LIMIT 20;

A cache_hit_pct below 90% for a frequently run query is worth investigating. The fix is usually one of: increase shared_buffers, add a covering index to reduce heap fetches, or rethink the query access pattern.

Resetting Statistics

pg_stat_statements accumulates statistics since the last reset. After a major schema change, deployment, or investigation, reset to start with a clean baseline:

SELECT pg_stat_statements_reset();

On production systems, reset after major events and monitor for a defined period (one hour, one day) to get representative data for a specific load pattern.

Setting Up a pg_stat_statements Monitoring Workflow

A practical workflow for ongoing performance monitoring:

1. Weekly slow query review. Run the total-time query above weekly. Flag any query that appears in the top 10 and was not there last week.

2. Post-deployment check. After every significant deployment, run both the total-time and average-latency queries and compare to the pre-deployment baseline. New slow queries after a deployment often indicate a missing index on a new query pattern.

3. Alert on query time regression. Export pg_stat_statements data to your monitoring system via a scheduled script and alert when mean_exec_time for a known query rises more than a threshold above its rolling average.

4. Correlate with EXPLAIN ANALYZE. When pg_stat_statements identifies a slow query, run EXPLAIN (ANALYZE, BUFFERS) on it to understand the query plan and identify the specific bottleneck.

Common Findings and What They Mean

A query that calls LIKE '%search%' — a leading wildcard prevents index use. This is a full sequential scan. Fix: use full-text search with tsvector and a GIN index, or a search service.

A query with very high shared_blks_read — the working set for this query does not fit in shared_buffers. Options: increase shared_buffers, add a covering index to reduce data fetched, or cache results at the application layer.

A query that appears in the top 10 by calls but has low individual latency — may still be worth optimizing if the aggregate load is significant. Consider caching at the application layer for stable, frequently-read data.

A query with high stddev_exec_time — inconsistent performance. Look for lock contention (queries that sometimes wait behind a write), autovacuum interference, or cache cold-start patterns.

If your Postgres instance is showing performance problems and you are not sure where to start, Clixo can audit your query patterns and help you prioritize the fixes with the highest impact. Start a build and we can take a look at your pg_stat_statements data together.