intro
Your API latency graph spikes every afternoon. You know it's the database, but which query? The slow trace in your APM tool points at one endpoint, and that endpoint fires six queries. The application logs tell you when things were slow, but you’re seaching for what PostgreSQL actually spent its time on. pgstatstatements answers that question. It records execution stats for every query the server runs, so you can rank them by the time they truly cost and start with the worst offender.
This walks through enabling the extension, finding the queries that hurt, reading the numbers correctly, and turning a flagged query into a fix you can measure.
Turn on pg_stat_statements
pg_stat_statements ships with PostgreSQL, but it isn't active by default. It can be enabled via the configuration file. To enable it, edit postgresql.conf and specify how many statements to track at any given time (pg_stat_statements.max):
1
shared_preload_libraries = 'pg_stat_statements'
2
pg_stat_statements.max = 10000
3
pg_stat_statements.track = top
Restart PostgreSQL, then create the pg_stat_statements extension in the database you want to inspect. This needs a superuser or a role with CREATE privilege on the database:
1
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
After creating the extension, you might want to confirm it’s actually working in conjunction with your database by issuing a query like so:
1
SELECT count(*) FROM pg_stat_statements;
If you see a number bigger than zero, you should assume the extension is doing its job. The full list of configuration knobs lives in the PostgreSQL pg_stat_statements docs.
Rank queries by total time, not by the slowest single run
To find any queries that took a long time to execute, you might want to rank them by cumulative time first:
1
SELECT
2
query,
3
calls,
4
total_exec_time,
5
mean_exec_time,
6
rows
7
FROM pg_stat_statements
8
ORDER BY total_exec_time DESC
9
LIMIT 10;
You might want to start from this query because it will show you the top 10 queries where your database actually spends the most of its time.
One version note. PostgreSQL 13 renamed the timing columns. On 13 and later, use total_exec_time and mean_exec_time. On 12 and earlier the columns are total_time and mean_time. If the query above errors with "column does not exist", you're on an older release, so drop the _exec.
Read the columns that matter
total_exec_time and calls give you the query execution cost for the database. The rest tell you why a query is slow. Read the following columns:
Cache behavior hides across those last two columns, so compute the hit ratio directly:
1
SELECT
2
query,
3
calls,
4
round(total_exec_time::numeric, 1) AS total_ms,
5
shared_blks_hit,
6
shared_blks_read,
7
round(
8
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0),
9
1
10
) AS cache_hit_pct
11
FROM pg_stat_statements
12
ORDER BY total_exec_time DESC
13
LIMIT 10;
Here, a query near the top with a low cache_hit_pct is reading from disk on most calls since it skips most of the query cache and a fix would include adding a missing index or a query touching less data.
From a flagged query to a fix
Say the top row is this, with a high mean time and a low cache hit ratio:
1
SELECT * FROM orders WHERE customer_id = $1 AND status = $2
To know why such a query is expensive, run it through EXPLAIN with real values and ask for the plan plus the buffer counts:
1
EXPLAIN (ANALYZE, BUFFERS)
2
SELECT * FROM orders WHERE customer_id = 1234 AND status = 'shipped';
If the plan shows a sequential scan over a large table, PostgreSQL is reading every row to find the matches:
1
Seq Scan on orders (cost=0.00..18540.00 rows=120 width=84)
2
(actual time=0.021..95.432 rows=118 loops=1)
3
Filter: ((customer_id = 1234) AND (status = 'shipped'::text))
4
**Rows Removed by Filter: 999882**
5
Buffers: shared read=8540
Read into the Rows Removed by Filter column: the query reads a million rows to return 118. To fix such a query, you would need to add an index that matches the filter:
1
CREATE INDEX idx_orders_customer_status
2
ON orders (customer_id, status);
Run the same EXPLAIN again. The sequential scan becomes an index scan, the disk reads turn into cache hits, and actual time drops from 95ms to under 1ms:
1
Index Scan using idx_orders_customer_status on orders
2
(actual time=0.038..0.211 rows=118 loops=1)
3
Buffers: shared hit=122
Column order in a composite index matters here, and our guide to the PostgreSQL index mechanism covers how to pick it. If parsing nested plan text gets old, DbVisualizer’s explain plan viewer renders the tree with per-node timing and row counts, which makes the expensive node easier to spot. For a deeper read on the plan output itself, see using the EXPLAIN plan in PostgreSQL.
Reset the stats to measure your change
pg_stat_statements accumulates statements from the last reset. If you’re measuring anything, do reset the counters, let normal traffic run, then re-check the ranking:
1
SELECT pg_stat_statements_reset();
Run your top-10 query again a few hours later. The query you tuned should have fallen down the list, or its mean_exec_time should have dropped. That before-and-after is the proof your change worked.
What pg_stat_statements won't tell you
pg_stat_statements tracks execution statistics for all SQL queries running within PostgreSQL. That means that:
Start with the cumulative-time ranking on your busiest database. The top five rows almost always include one query you didn't know was expensive: that’s your starting point.
Practical use cases
Some practical use cases of pg_stat_statements include:
Frequently asked questions
Does pg_stat_statements slow down my database?
No. The overhead coming with pg_stat_statements is small and most production systems run it full time. Each statement does a little extra bookkeeping, and the cost grows with the number of distinct query shapes. That count is capped by pg_stat_statements.max, so raise that limit rather than turning tracking down.
Do the statistics survive a restart?
Yes. PostgreSQL saves the stats to disk on shutdown and reloads them on start. They reset only when you call pg_stat_statements_reset() or remove the stats file.
Why don't the times provided by pg_stat_statements match with my logs or application?
pg_stat_statements measures time that your SQL queries have spent “moving” inside PostgreSQL. It doesn't include network round trips, connection pool waits, or the time your app spends processing the result, hence the mismatch.
Can I use it on Amazon RDS or other managed Postgres?
Yes. Most managed providers either preload it or let you enable it through a parameter group. Check the parameter group for shared_preload_libraries before assuming you need to change anything.
Can I change the amount of queries I track with pg_stat_statements?
Yes. Change the amount of queries monitored by fiddling with the value in pg_stat_statements.max (default: 5000.)
