Find Slow PostgreSQL Queries with pg_stat_statements

pg_stat_statements is a PostgreSQL extension that records how long every statement you run takes, grouped by a normalised form of the query. Enable it once and you can answer "which query is actually loading this server?" with a single SQL statement instead of guessing.

This guide walks through installing the extension, preloading it, and reading the output to find the queries worth optimising.

Before you start

• A Linux VPS running PostgreSQL 12 or newer on Ubuntu 22.04/24.04, Debian 12, AlmaLinux 8/9 or Rocky 8/9.
• Shell access as root or a sudo user, plus psql access as a PostgreSQL superuser (usually the postgres account).
• The contrib package for your PostgreSQL major version — it contains the extension.
• A short maintenance window: preloading the library requires a restart of the PostgreSQL service.

Step 1 — Install the contrib package

On Debian and Ubuntu the package is not version-specific:

sudo apt update
sudo apt install postgresql-contrib

On RHEL-family systems the name includes the major version:

sudo dnf install postgresql16-contrib

Replace 16 with your installed major version — check it with psql --version. If you installed PostgreSQL from the PGDG repository, install the matching contrib package from the same repository.

Step 2 — Preload the library

pg_stat_statements has to be loaded when the server starts. The cleanest way is ALTER SYSTEM, which writes the setting to postgresql.auto.conf:

sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"

If other libraries are already listed, include them all in the same comma-separated value, otherwise you will silently drop them:

sudo -u postgres psql -c "SHOW shared_preload_libraries;"

Two optional settings are worth adding at the same time:

sudo -u postgres psql -c "ALTER SYSTEM SET pg_stat_statements.max = 10000;"
sudo -u postgres psql -c "ALTER SYSTEM SET pg_stat_statements.track = 'top';"

pg_stat_statements.max is how many distinct statements are tracked; once the limit is reached, the least-used entries are evicted. Setting track = 'all' also records statements executed inside functions and procedures, at a small extra memory cost.

Step 3 — Restart PostgreSQL

sudo systemctl restart postgresql

On RHEL-family systems with PGDG packages the unit may be named postgresql-16.service. Confirm with systemctl status postgresql.

Step 4 — Create the extension

The library is loaded server-wide, but the view must be created in each database you want to inspect:

sudo -u postgres psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Repeat for every application database. The view only shows statements executed in the database you are currently connected to.

Step 5 — Find the slow queries

Connect to the database and start with the biggest total consumers:

SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  left(query, 100) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

On PostgreSQL 12 and older the columns are named total_time, mean_time and stddev_time; from PostgreSQL 13 they are total_exec_time, mean_exec_time and stddev_exec_time.

total_exec_time is the total time spent in that normalised statement, which is the best signal for "what is keeping the CPU busy". mean_exec_time is the average per call, which is what you want for a query that is slow but rare. Sort by it separately:

SELECT calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       left(query, 100) AS query
FROM pg_stat_statements
WHERE calls > 20
ORDER BY mean_exec_time DESC
LIMIT 20;

A few more columns are worth reading:

• shared_blks_read and shared_blks_hit — a high read-to-hit ratio means the query is going to disk.
• temp_blks_written — anything above zero means sorts or hashes are spilling to temporary files; review work_mem.
• wal_bytes (PostgreSQL 13 and newer) — heavy writers show up here.
• rows compared with calls — a query returning a huge number of rows per call is often missing a LIMIT or using a poor join.

The query column is normalised: literals are replaced with $1, $2 and so on, so similar statements collapse into one row. Use queryid when you need to refer to a specific statement.

On PostgreSQL 14 and newer you can also see when the counters were last reset and whether entries were evicted:

SELECT * FROM pg_stat_statements_info;

To start a clean measurement window:

SELECT pg_stat_statements_reset();

Step 6 — Act on the results

Take the top statement and run it through EXPLAIN (ANALYZE, BUFFERS) with realistic parameter values. Look for sequential scans on large tables, nested loops with high row counts, and sorts that spill to disk. The usual fixes are an index on the filter or join column, rewriting a correlated subquery as a join, or adding a LIMIT where the application does not need every row.

Check it works

Run a query you can recognise, then search for it:

SELECT count(*) FROM pg_class;
SELECT calls, left(query, 60) FROM pg_stat_statements WHERE query LIKE '%pg_class%';

If calls increased by one, tracking is working. If the view returns nothing at all, work through the section below.

Troubleshooting

• ERROR: relation "pg_stat_statements" does not exist — the extension is not created in the database you are connected to. Run CREATE EXTENSION pg_stat_statements; in that database.
• ERROR: pg_stat_statements must be loaded via shared_preload_libraries — the library was not preloaded, or PostgreSQL was not restarted afterwards. Check SHOW shared_preload_libraries; and restart the service.
• Counters reset after a restart — that is expected: the statistics live in shared memory. If older queries vanish while the server is still running, pg_stat_statements.max is too small and entries are being evicted. Raise it and restart. The dealloc column in pg_stat_statements_info (PostgreSQL 14 and newer) shows the eviction count.
• Query text shows <insufficient privilege> — you are not a superuser and lack the pg_read_all_stats role. Grant it with GRANT pg_read_all_stats TO deploy;.
• A slow query never appears in the list — it may be running inside a function or procedure. Set pg_stat_statements.track = 'all' and restart.

Next steps

• Diagnosing slow database performance and query optimization with EXPLAIN
• psql cheat sheet: essential PostgreSQL commands
• Back up and restore PostgreSQL with pg_dump and pg_restore