Find Slow Queries With pg_stat_statements

When a PostgreSQL database feels slow, the hardest part is often knowing where to start. Application logs show slow pages, but not which of the hundreds of queries behind them consume the most time. The pg_stat_statements extension answers that question. It records execution statistics for every distinct statement the server runs, so you can rank queries by total time, call count or average duration and focus effort where it matters. This guide explains how to enable pg_stat_statements, which columns to read, how to turn its data into a tuning plan and how it fits with tools such as EXPLAIN and auto_explain.

What pg_stat_statements records

The PostgreSQL documentation describes the module as a means of tracking planning and execution statistics of all SQL statements executed by a server, made available through a view named pg_stat_statements with one row for each distinct combination of database, user, query ID and top-level flag (PostgreSQL: pg_stat_statements).

The documentation describes the queryid column as a hash code that identifies identical normalised queries, which means statements that differ only in constant values are grouped together. A query that looks up a customer by ID is counted as one entry however many different IDs it is called with. That grouping is what makes the view useful: it shows patterns, not individual executions.

Enable pg_stat_statements

Setup has two steps, and the first needs a restart:

1. Add pg_stat_statements to shared_preload_libraries in postgresql.conf. The documentation explains this is required because the module needs additional shared memory, so a server restart is needed to add or remove it.
2. Run CREATE EXTENSION pg_stat_statements in each database where you want to query the view.

Managed database services usually expose these settings through their own configuration screens or parameter groups; check your provider's documentation for the exact steps.

The columns that matter most

The view has many columns. A handful answer most questions:

• calls: the number of times the statement was executed.
• total_exec_time: total time spent executing the statement, in milliseconds.
• mean_exec_time: the average execution time, in milliseconds.
• rows: the total number of rows retrieved or affected.
• shared_blks_hit and shared_blks_read: shared buffer cache hits and blocks read, which hint at whether a query is served from memory or from disk.
• query: the text of a representative statement.

Total time and mean time tell different stories. A query with a high mean time is slow each time it runs. A query with a modest mean but a very high call count may consume more total time than anything else, and it may be a sign of an application making the same call in a loop.

Worked example: ranking queries by total time

The simplified, untested sketch below shows queries a team might run against the view: one to list the statements consuming the most total execution time, and one to list those with the highest average time among frequently called statements. Column names follow the current PostgreSQL documentation; check the documentation for your own version, since the view's columns can differ between versions.

-- Simplified, untested sketch
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 80) AS query_start
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

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

The threshold of 100 calls in the second query is a placeholder; choose a value that filters out one-off administrative statements in your environment.

Turn the list into a plan

Once you have a ranked list, work through it in order:

1. Take the top query by total time and find where the application calls it.
2. Run EXPLAIN, or EXPLAIN ANALYZE in a safe environment, to see the plan.
3. Decide on a fix: an index, a rewritten query, fewer calls through batching or caching, or a change to how much data is fetched.
4. Apply the fix, then compare the statement's statistics after a period of normal traffic.

Our guide to PostgreSQL indexes covers how to choose and add indexes safely when the plan shows a missing one.

Reset and compare

Statistics accumulate from the last reset, so old data can hide recent changes. The module provides a pg_stat_statements_reset function to clear statistics. A common practice is to reset after a release or a tuning change, wait for a representative period of traffic and then compare. Record the top statements before each reset so that you keep a history; resetting without saving the results throws away your baseline.

Pair it with auto_explain

pg_stat_statements tells you which statements are expensive, but not why a particular execution was slow. The auto_explain module complements it. The documentation describes auto_explain as a means of logging execution plans of slow statements automatically, without running EXPLAIN by hand, and notes that its default behaviour is to do nothing until you set auto_explain.log_min_duration (PostgreSQL: auto_explain). It also warns that there is a price in overhead, especially if you enable options such as logging actual run times.

Use auto_explain with a threshold high enough to capture only genuinely slow executions, and turn on heavier options only when investigating.

Look beyond the database

Some of the most expensive entries turn out to be application problems rather than database problems: the same query issued once per item in a list, data fetched and then discarded or reports run during peak hours. Fixing those often helps more than any index. Connection handling matters too; if many short connections are opened and closed, look at pooling. Our guide to PostgreSQL connection pooling explains how a pooler reduces that overhead.

Watch the view's limits

The view does not keep every statement forever. The module tracks a limited number of distinct statements, set by pg_stat_statements.max, and the documentation explains that entries for the least-executed statements are deallocated when more distinct statements than that are observed. If your application generates many distinct query shapes, for example by building SQL with varying numbers of parameters, important entries may be pushed out. Watch for this, and prefer query patterns that normalise well.

Also think about who can see the data. The documentation notes that, for security reasons, only superusers and roles with the privileges of pg_read_all_stats can see the SQL text and query ID of queries run by other users, so grant that role only to the people and tools that need it.

Make it a routine

Treat pg_stat_statements as a regular check rather than an emergency tool. A short weekly or monthly review of the top statements by total time and mean time, with notes on what changed, catches regressions early. Share the list with the developers who own the queries; they can often fix a problem in minutes once they see it.

Frequently Asked Questions

What is pg_stat_statements?

It is a PostgreSQL extension that records planning and execution statistics for all SQL statements the server runs, grouped by normalised query, and exposes them in a view.

Does enabling pg_stat_statements need a restart?

Yes. It must be added to shared_preload_libraries because it needs shared memory, and the documentation says a server restart is required to add or remove it.

Should I sort by total time or mean time?

Both. Total time shows where the server spends most effort overall. Mean time shows statements that are slow on each call. Review both lists.

How is it different from auto_explain?

pg_stat_statements aggregates statistics per statement. auto_explain logs the execution plans of individual slow statements, which helps explain why they were slow.

Find and fix your slowest queries

We analyse query statistics, trace expensive statements back to application code and fix them safely. See our custom app development services, or talk to us about PostgreSQL performance.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.