Skip to content
Tutorials Center
My learning

Topics

Lesson 64 of 90 ยท Performance Tuning

Finding Slow Queries with pg_stat_statements

Find the queries that cost the most: enable pg_stat_statements, rank by total time, add auto_explain and log_min_duration_statement, and read logs with pgBadger.

You cannot tune what you cannot find. A busy PostgreSQL server runs thousands of different statements, and the one that annoys your users is rarely the one you would guess. The query that takes ten seconds once an hour matters less than the one that takes three milliseconds but runs two million times a day.

PostgreSQL ships with the tools to find both kinds. The pg_stat_statements extension keeps running totals for every distinct statement; auto_explain logs the plans of slow executions; log_min_duration_statement writes slow statements to the server log; and pgBadger turns those logs into reports. This lesson shows how to set them up and, more importantly, how to read their numbers.

All server features here are shown as read-only code, because they need a real PostgreSQL server and extensions. The live examples build a small table that looks like pg_stat_statements output and analyze it with ordinary SQL, so you can practice the reading skills that matter.

Enabling pg_stat_statements

The extension must be loaded when the server starts, so it needs a configuration change and a restart:

TEXT
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top        # top-level statements (use 'all' to include nested ones)
pg_stat_statements.max   = 5000       # distinct statements to remember

After the restart, create the extension in each database where you want to query the view:

SQL
CREATE EXTENSION pg_stat_statements;

The extension needs compute_query_id to be auto or on (the default auto turns on when the module is loaded). It then tracks statements by a normalized fingerprint: literal values are replaced by $1, $2 and so on, so WHERE id = 7 and WHERE id = 912 count as one statement. Managed services such as Amazon RDS, Google Cloud SQL and Azure often have it enabled or one parameter-group change away.

The counters are cumulative since the last reset. To measure a specific period, reset before an experiment and read afterwards:

SQL
SELECT pg_stat_statements_reset();

Add a snapshot job (or a monitoring tool that does it) if you need history, since the view itself only holds the totals.

The columns that matter

pg_stat_statements is a view with one row per statement (per user and database). The columns you will use most:

Column Meaning
queryid Fingerprint of the normalized statement
query The normalized text (long texts are truncated to track_activity_query_size)
calls How many times it ran
total_exec_time Total milliseconds spent executing (total_time before PostgreSQL 13)
mean_exec_time, min_exec_time, max_exec_time, stddev_exec_time Per-call timing statistics
rows Total rows returned or affected
shared_blks_hit, shared_blks_read Buffers found in cache versus read from the OS or disk
temp_blks_written Blocks written to temporary files (spills)
wal_bytes WAL generated, useful for write-heavy statements

Planning time is tracked separately with pg_stat_statements.track_planning = on (it adds overhead, so it is off by default).

Four ways to rank queries

Total time Where the server really spends its day.
Mean time What a single user waits for.
Calls Cheap queries run too often.
Blocks read Statements that hit the disk.

Start with total time, then check the others.

1 / 2

Finding the top queries

The standard first query ranks statements by total time and shows each one's share of all execution time:

SQL
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 0) 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,
  left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ranking by total time answers "what should I tune to reduce server load?". Ranking by mean time answers "what is the slowest experience for a single call?". They often disagree, and you need both. To see how, here is a small table with the same shape and ordinary SQL doing the ranking:

SQL Ranking statements by total time

Press Run (or Ctrl + Enter) to see the result here.

The count(*) (3) and the report query (6) take 1.2 s and 15 s per call and run rarely, yet together they account for about 55 percent of all time. The orders lookup (2) takes under a millisecond per call and still uses 17 percent of the total because it runs 300,000 times. The highest mean belongs to query 6, but the highest total belongs to query 3 (480,000 ms, 34 percent); ranking by either number alone would hide part of the picture.

Reading the numbers well

Raw ranking is a starting point. Some practical readings:

  • High total, high calls, low mean: a hot, cheap query. Cutting calls (caching, batching, fixing an N+1 loop from Query Tuning Patterns) usually helps more than micro-tuning it.
  • High mean, low calls: a heavy report or batch job. Look at its plan; consider running it on a replica or off-peak.
  • High stddev_exec_time relative to the mean: inconsistent performance, often caused by a plan that flips between good and bad, lock waits, or cache misses. Compare min_exec_time and max_exec_time.
  • Poor cache ratio: shared_blks_read large compared with shared_blks_hit means the statement reads from disk a lot, which points at missing indexes or a working set larger than memory.
  • temp_blks_written above zero: spills from sorts or hashes; see Reading Slow Plans.

The cache ratio is plain arithmetic on two columns. Compute it for a set of statements:

SQL Cache hit ratio per statement

Press Run (or Ctrl + Enter) to see the result here.

Query 3 finds only 20 percent of its blocks in cache and reads 8 million blocks, a candidate for an index or a smaller scan. Query 1 is almost entirely cached (100 percent after rounding). Query 6 hits the cache only 55.6 percent of the time and also spills (temp_blks_written is 120,000). A system-wide hit rate above 99 percent is typical for OLTP workloads, but judge each hot statement on its own.

Logging slow statements

pg_stat_statements summarizes; the server log shows individual executions, with actual parameter values. The simplest setting is a duration threshold:

TEXT
# postgresql.conf
log_min_duration_statement = 500ms     # log every statement slower than this (0 logs all, -1 disables)
log_line_prefix = '%t [%p] %u@%d app=%a '

Set the threshold with care: 0 logs everything and can fill disks and slow the server, while a very low value on a busy system produces a flood. You can also set it per role or database with ALTER ROLE report_user SET log_min_duration_statement = '5s'. Variants such as log_min_duration_sample with log_statement_sample_rate log only a fraction of fast-ish statements, and log_lock_waits = on reports sessions that waited for locks longer than deadlock_timeout.

A log line contains the duration and the statement:

TEXT
2025-03-01 10:15:42 UTC [4121] shop@shop app=api LOG:  duration: 1843.221 ms  statement:
    SELECT ... FROM orders WHERE customer_id = 8812 ORDER BY created_at DESC

Because logs contain real literal values (including personal data), restrict access to them like any other sensitive data store.

auto_explain: plans for the slow ones

The log tells you a statement was slow, but not why. auto_explain writes the execution plan of slow statements to the log automatically, so you can read what happened at the moment it happened, without trying to reproduce the problem:

TEXT
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on          # include actual rows and times (adds timing overhead)
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on   # include statements inside functions

log_analyze = on has a cost, because it turns on per-node timing for every statement, so measure the overhead on a busy system or use auto_explain.sample_rate to explain only some statements. You can also load the module for a single session while investigating: LOAD 'auto_explain'; SET auto_explain.log_min_duration = 0;.

pgBadger: turning logs into reports

pgBadger is a free command-line log analyzer. You point it at your PostgreSQL logs and it produces an HTML report with the slowest queries, the most frequent ones, errors, lock waits, connection patterns and temporary file usage. It parses what the server logs, so the report is only as good as your logging settings: log the connections, checkpoints, temporary files (log_temp_files = 0), autovacuum activity (log_autovacuum_min_duration = 0) and lock waits, and pick a log_line_prefix that pgBadger understands (its documentation lists the supported ones). Reports are most useful to compare "before" and "after" a change, or to see what happened during an incident.

A solid routine for a production system: keep pg_stat_statements on permanently, log statements slower than a threshold you can afford to read, enable auto_explain with a sensible threshold and sampling, and look at the top queries by total time every week. Most performance problems announce themselves long before they cause an outage.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must you do before CREATE EXTENSION pg_stat_statements can collect data?

    The module has to be loaded at server start, so it needs shared_preload_libraries and a restart. CREATE EXTENSION then creates the view in a database.
  2. 2. A statement runs 2,000,000 times a day at 0.4 ms each. Which ranking shows it as important?

    Its mean is tiny but calls times mean gives 800 seconds a day. Total time ranks the real server load.
  3. 3. Why does pg_stat_statements show WHERE id = $1 instead of the real value?

    Normalization replaces constants with placeholders, so all executions of the same query shape aggregate under one queryid.
  4. 4. What does auto_explain add that log_min_duration_statement does not?

    log_min_duration_statement logs that a statement was slow. auto_explain logs the plan as well, so you can see why.

Summary

  • pg_stat_statements needs shared_preload_libraries and a restart, then CREATE EXTENSION in each database.
  • It keeps cumulative counters per normalized statement: calls, total and mean time, rows, buffer hits and reads, temp blocks.
  • Rank by total time for server load, by mean time for per-call pain, and check calls, stddev_exec_time and cache ratios.
  • pg_stat_statements_reset() starts a clean measurement period.
  • log_min_duration_statement records individual slow statements; logs contain real values and need protection.
  • auto_explain logs plans of slow statements; use a threshold and sampling to control overhead.
  • pgBadger summarizes logs into reports, provided your logging settings capture enough.

Next: Configuration Tuning.