# How to Use pg_stat_statements to Find Slow Postgres Queries

> Learn how to enable and use pg_stat_statements to identify the slowest, most costly queries in your Postgres database — with practical SQL queries for production use.

- **Published:** 2025-12-21
- **Author:** Clixo
- **Reading time:** 5 min read
- **Tags:** postgres, query-performance, pg-stat-statements, monitoring, database
- **Canonical URL:** https://clixo.sh/blog/postgres-query-performance-with-pg-stat-statements

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:

```sql
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:

```sql
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.

```sql
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:

```sql
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.

```sql
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:

```sql
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.

```mermaid
flowchart TD
  A["Query pg_stat_statements (weekly or post-deploy)"] --> B{New query in top 10 by total_exec_time?}
  B -->|No| C[No action needed]
  B -->|Yes| D["Run EXPLAIN ANALYZE BUFFERS on that query"]
  D --> E{Root cause?}
  E -->|"Missing index"| F["Create index CONCURRENTLY"]
  E -->|"Low cache_hit_pct"| G["Increase shared_buffers or add covering index"]
  E -->|"High stddev — inconsistent latency"| H["Investigate lock contention or autovacuum"]
  F & G & H --> I["Re-run query, confirm improvement"]
  I --> A
```

## 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](https://clixo.sh/#contact) and we can take a look at your `pg_stat_statements` data together.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)