Find Slow PostgreSQL Queries with pg_stat_statements

When a PostgreSQL database feels slow, the first instinct is to look for the slowest query. That is usually the wrong list. The statement that hurts the system most is often a fast query that runs thousands of times, not a slow report that runs twice a day. pg_stat_statements answers the right question: which normalized statements consume the most database time, and why.

Every number on this page comes from a lab on PostgreSQL 17.11 with generated data, not from a client system. The absolute timings will differ on your hardware; the method and the shape of the result carry over.

Enable it and check it is collecting

pg_stat_statements needs the library loaded at server start and the extension created in each database you query it from:

SHOW shared_preload_libraries;          -- must include pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SHOW pg_stat_statements.track;          -- 'top' by default
SHOW track_io_timing;                   -- 'on' adds read and write times

On Amazon RDS and Aurora PostgreSQL, shared_preload_libraries and track_io_timing are set in a DB parameter group. The default groups cannot be edited, so use a custom parameter group (a cluster parameter group on Aurora); changing shared_preload_libraries needs a reboot. Check the current value with SHOW before planning a change. track_io_timing adds timing calls on every read; measure its overhead on your platform before leaving it on permanently.

Rank by total time, not by the slowest call

The lab ran three statements against a 2-million-row orders table and a 200,000-row customers table: a customer order lookup with no supporting index, run 200 times; a monthly revenue report, run twice; and a primary-key lookup, run 20,000 times. Ranked by mean execution time, the report looks like the problem:

The order lookup used 85.6% of all execution time, six times more than the report. On a production system the gap is usually larger, because the frequent statement is on a request path. This query produces the useful ranking:

SELECT left(query, 80)                                   AS query,
       calls,
       round(total_exec_time::numeric)                   AS total_ms,
       round(mean_exec_time::numeric, 2)                 AS mean_ms,
       round((100 * total_exec_time
              / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
       rows,
       shared_blks_hit + shared_blks_read                AS shared_blocks,
       round(shared_blk_read_time::numeric)              AS read_ms,
       temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

On PostgreSQL 16 and earlier, the read-time column is blk_read_time instead of shared_blk_read_time. Statements are normalized: WHERE customer_id = 4242 and WHERE customer_id = 17 are one entry with $1 in the text, which is what makes the totals meaningful.

Read the resource columns before guessing the fix

Time tells you where to look; the block and temp columns tell you what kind of problem it is.

• Many shared blocks per call points at a scan doing more work than the result needs. The order lookup touched about 15,600 8 kB buffers per call to return at most 20 rows. That is the signature of a missing or unusable index.
• shared_blks_read and read time show how much of that work missed the buffer cache. High reads with low read time usually means the operating system cache absorbed them; high read time means storage latency is part of the cost.
• temp_blks_written means a sort or hash spilled to disk. The report wrote about 15,400 temp blocks per run. Raising work_mem to 64 MB for the session did not remove the spill: the sort of 2 million rows still used a 61 MB external merge. Large spills need a query or data-model change, not just a bigger memory setting. A separate lab on work_mem measured how little removing a spill can save.
• rows divided by calls shows how many rows each call returns. A high block count for a small row count is the clearest index signal.

Measure a window, not the lifetime counters

The counters accumulate from the last reset, often weeks earlier, so a recent regression is diluted by old history. Resetting with pg_stat_statements_reset() on production throws away the evidence other people may need. Take a snapshot instead, wait through the problem period, and diff:

-- 1. Before the window
CREATE TABLE pss_snapshot AS
SELECT now() AS captured_at, userid, dbid, toplevel, queryid,
       calls, total_exec_time, rows,
       shared_blks_hit, shared_blks_read, temp_blks_written
FROM pg_stat_statements;

-- 2. After the window
SELECT left(s.query, 80) AS query,
       s.calls - coalesce(p.calls, 0) AS calls,
       round((s.total_exec_time - coalesce(p.total_exec_time, 0))::numeric, 1) AS exec_ms,
       (s.shared_blks_hit + s.shared_blks_read)
         - coalesce(p.shared_blks_hit + p.shared_blks_read, 0) AS blocks,
       s.temp_blks_written - coalesce(p.temp_blks_written, 0) AS temp_blocks
FROM pg_stat_statements AS s
LEFT JOIN pss_snapshot AS p
  ON p.userid = s.userid AND p.dbid = s.dbid
 AND p.toplevel = s.toplevel AND p.queryid = s.queryid
WHERE s.calls > coalesce(p.calls, 0)
ORDER BY exec_ms DESC
LIMIT 20;

Store the snapshot table in a schema your team controls, or export it with the read-only evidence collector. Also check SELECT dealloc, stats_reset FROM pg_stat_statements_info;: a rising dealloc means more distinct statements than pg_stat_statements.max (5,000 by default), so rarely-run entries are being evicted and the totals are incomplete.

Confirm the cause with the plan

pg_stat_statements does not store plans. Take the top statement, substitute a representative parameter, and run it on a replica or a production-like copy:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

In the lab this showed a parallel sequential scan in which each of three processes discarded about 666,662 rows, to find 14 matching rows in total, reading 15,596 buffers in 23.4 ms. EXPLAIN ANALYZE executes the statement, so never run it on a write without wrapping it in a transaction you roll back. For larger plans, paste the output into PlanReader, which runs in the browser and flags estimate errors, spills and expensive scans.

Make the change and prove it

The fix for this statement was a composite index, built without blocking writes:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

Treat even a simple index as a production change:

• Risk: extra write cost on every insert and update of the indexed columns, extra storage, and a build that reads the whole table. CREATE INDEX CONCURRENTLY avoids blocking writes but takes longer and can leave an INVALID index if it fails.
• Rollback: DROP INDEX CONCURRENTLY orders_customer_created_idx;
• Validation: repeat the snapshot diff over a comparable period and compare the statement's total time, blocks per call and the write latency of the table's insert path.

What pg_stat_statements cannot tell you

It shows where execution time goes, not why a session waited to start. Lock waits, connection-pool queues and network time sit outside it. Pair it with wait events from pg_stat_activity, or with Database Insights on RDS and Aurora, and with the deploy and migration history for the same window. That correlation is the difference between a plausible index suggestion and a change you can defend. See the high-CPU problem guide for the wait-event side, and the sample report for how ranked findings are written up. For the next steps after ranking (capturing the real plan with auto_explain and measuring it with EXPLAIN ANALYZE), see how to profile slow PostgreSQL queries.