PostgreSQL in Practice #3 The Performance Diagnostics Routine: Finding Slow Queries with pg_stat_statements

Infrastructure PostgreSQL Database
4 min read

Basics chapter 5’s EXPLAIN answers “why is this query slow.” But in production the question arrives one step earlier: “the database is slow — which query is the problem?” General bottleneck hunting when a whole server is slow was covered in the why servers get slow series; this chapter is the routine for drilling into the database layer with PostgreSQL’s own tools.

Right now: pg_stat_activity #

“What is happening in the database at this moment” is answered by the pg_stat_activity view. We already used it to count connection states last chapter.

Query pg_stat_activity 
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY running_for DESC;

Three things to read. Long-running queries (rows with large running_for), waiting queries (wait_event_type of Lock means a lock wait — practice #6’s territory), and the easy-to-miss idle in transaction: a session holding a transaction open while doing nothing. It may be sitting on locks, and it blocks VACUUM’s cleanup (practice #5 returns to this). The archetypal culprit is application code that opens a transaction and then waits on an external API.

Cumulative: pg_stat_statements #

Snapshots of the moment can’t catch “slow sometimes” problems. You need cumulative statistics, and the standard is the pg_stat_statements extension. It folds queries of the same shape (differing only in parameters) into one entry and keeps aggregating call counts and times.

Top 10 in pg_stat_statements 
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements', then restart
CREATE EXTENSION pg_stat_statements;

-- top 10 by total time
SELECT calls,
       round(total_exec_time::numeric / 1000, 1) AS total_sec,
       round(mean_exec_time::numeric, 2)         AS mean_ms,
       rows,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

It needs a restart, so turn it on ahead of time — enable it after the incident and that day’s data doesn’t exist. On managed services it can usually be enabled through the parameter group.

Top-by-total and top-by-mean are different culprits #

Different orderings expose different problems.

  • Top by total_exec_time: the queries consuming the most database resources. A 2ms-average query called hundreds of times per second can rank first. Start here to lower overall load. The fix is often not query tuning but “calling it less” (caching, fixing N+1 patterns).
  • Top by mean_exec_time: queries slow per execution. What users feel as “this screen is slow” is usually these. Queries surfaced here are what you carry to EXPLAIN ANALYZE — the link back to the basics series.

rows / calls (rows per call) is worth a scan too. A query returning tens of thousands of rows per call may not be a “database is slow” problem but a “fetching too much” problem.

Logs: log_min_duration_statement #

The third tool is the slow query log.

postgresql.conf 
log_min_duration_statement = 500   # log queries taking over 500ms

Where pg_stat_statements aggregates by shape, the log records individual executions with their actual parameters. Data-skew problems — “slow only for this one user” — are caught here. Setting it to 0 logs everything and costs real overhead, so a threshold around 500ms〜1s is typical.

Assembling the routine #

Diagnosis now becomes a sequence. ① During an incident, read the present with pg_stat_activity (long runners, lock waits, idle in transaction). ② For steady-state improvement, pull pg_stat_statements by both orderings, total and mean. ③ Take the culprit query into EXPLAIN ANALYZE and run chapter 4’s index checklist. ④ After the fix, confirm the pg_stat_statements numbers actually dropped (resetting with SELECT pg_stat_statements_reset(); makes the comparison clean). The next step — when an index doesn’t solve it — is the advanced indexing strategy of the next chapter.

Summary #

  • The diagnostic order is “which query” (this chapter) → “why slow” (EXPLAIN). Use the tools in that order.
  • The present is pg_stat_activity: long-running queries, Lock waits, and idle in transaction are the three reads.
  • The cumulative standard is pg_stat_statements. It needs a restart — enable it before the incident.
  • Top-by-total is the load culprit; top-by-mean is the user-experience culprit. Pull both.
  • The log (log_min_duration_statement) keeps actual parameters and catches data-skew problems. Next chapter: advanced index strategy.
X