pg_stat_statements: find the queries that hurt

If you are debugging Postgres performance by running EXPLAIN ANALYZE on queries your team suspects are slow, you are diagnosing the wrong set. The right set is whatever pg_stat_statements tells you is expensive in aggregate across all the queries your application actually runs. That set does not overlap cleanly with engineering intuition, and it is the only unbiased source of truth for what Postgres is actually spending time on.

What pg_stat_statements does

pg_stat_statements is a standard extension, distributed with Postgres, that maintains a running tally for every normalized query executed on the server. Normalization strips literals: SELECT * FROM users WHERE id = 123 and SELECT * FROM users WHERE id = 456 are the same statement, counted together. For each distinct statement, the extension records:

• calls: total number of executions since last reset.
• total_exec_time: cumulative execution time across all calls.
• mean_exec_time, min_exec_time, max_exec_time, stddev_exec_time: distribution of execution time.
• rows: total rows returned across all calls.
• shared_blks_hit, shared_blks_read: buffer cache hits vs. disk reads.

From those columns, almost every interesting performance question has an answer. The shape of the question determines which column to sort on.

Setup

On Supabase

Enabled by default on every project. Nothing to install, nothing to configure. Verify with SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';. The extension lives in the extensions schema; queries below reference it as pg_stat_statements because it is on the default search path.

On self-hosted Postgres

Two steps. First, add the library to shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Restart Postgres for the change to take effect. Then create the extension in the database you want to measure:

CREATE EXTENSION pg_stat_statements;

The extension is per-database. If you have multiple databases, create it in each.

On other managed providers

RDS, Cloud SQL, Neon, Azure Database for Postgres: all support pg_stat_statements and most enable it by default or behind a one-click toggle in the console. Check the provider's parameter group for shared_preload_libraries.

The two queries you will actually run

There are many ways to slice pg_stat_statements. Two cover 90 percent of real-world use. Keep them bookmarked.

Total Pain, the query

SELECT
  substring(query, 1, 100) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

The top row is almost always one of: an analytics-style aggregate run by a dashboard, a background job that churns through a large set, or a hot read path like a user session lookup. The top row does not tell you what is wrong. It tells you where your database spends its time. The next question is why.

Frustration Finder, the query

SELECT
  substring(query, 1, 100) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows / GREATEST(calls, 1) AS mean_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

Filtering on calls > 100 removes one-off queries that ran once during a migration and took a second. The useful result is a query that runs many times and still averages seconds per call. That is the signature of a missing index, a bad plan, or a query waiting on locks it shouldn't need.

Reading the output

A typical pg_stat_statements result looks like this (truncated):

               query                |  calls  | total_ms  | mean_ms |  rows
------------------------------------+---------+-----------+---------+--------
 SELECT ... FROM orders JOIN cust.. | 184,382 | 1,284,110 |    6.96 |    1.0
 SELECT * FROM pg_stat_statements   |     218 |   172,040 |  789.17 |  215.0
 UPDATE sessions SET last_seen_at.. |  92,110 |   112,220 |    1.22 |    1.0
 SELECT * FROM products WHERE sto.. |   1,204 |    98,400 |   81.72 |  502.0
 SELECT name FROM users WHERE id.. |2,147,883 |    66,040 |    0.03 |    1.0

Four patterns to read from a table like this.

Row 1 is the biggest total cost, a join query running hundreds of thousands of times a day. The mean is 7 ms, which is fine per call. The fix, if one is needed, is to reduce how often the endpoint that calls it runs, or to cache.

Row 2 is the extension itself. Your diagnostic queries show up in their own output. Ignore.

Row 4 is an 82 ms query running 1,204 times. The total is small but the mean is bad. This is where EXPLAIN ANALYZE earns its keep. An 82 ms query on 500 rows usually means a missing index or a filter that defeats the indexes that exist.

Row 5 is the N+1 signature. Two million calls, 0.03 ms each. Individually invisible. Collectively, the fifth-largest total time in the table. The fix is a join in the calling code, not an index.

Resetting counters

pg_stat_statements_reset() zeroes all the counts. Useful as a before-and-after around a deploy: reset, let an hour of production traffic run, snapshot, and you have exactly what the new version of the application does. By default only superusers can run it; grant EXECUTE on the function to allow other roles. (pg_read_all_stats grants read access to other users' query text, not reset.)

SELECT pg_stat_statements_reset();

The attribution problem

pg_stat_statements tells you which queries are expensive. It does not tell you which deploy introduced the expense. A slow query that was fine last week and pages you today is only recognizable as a regression if you have a baseline from before the change. Without a before-and-after snapshot tied to a Git SHA, you are guessing.

Two approaches to solve this. One is manual: reset the extension at deploy time, capture a snapshot one hour later, and diff. This works for a small team with a slow deploy cadence. The other is to run an agent that watches the counters continuously, takes snapshots at deploy boundaries it detects from the database itself, and attributes changes to the surrounding commit window. That is where Datapace fits: a context layer and security gateway for running AI operations on production databases, with DBAs in control. In this workflow the specific thing it does is surface call-count and total-time deltas with a commit range, not interpret the query's purpose. The query is still yours to read.

Whichever approach you use, the important part is that the baseline exists. Without one, pg_stat_statements is a snapshot of the present, not a comparison across time.

Closing note

The argument for pg_stat_statements is not that it is a sophisticated tool. It is not. It is a set of counters, incremented every time a query runs, totaled and averaged when you ask. The reason it matters is that it is the only view of your database that is not biased by which query someone decided to inspect. Start there. Pick the top row. Then run EXPLAIN ANALYZE BUFFERS on it. If you want the before-and-after baseline handled for you, see how Datapace attributes regressions to deploys.

Sources

1. PostgreSQL documentation, pg_stat_statements
2. Supabase documentation, pg_stat_statements