Finding and Fixing Slow Queries in PostgreSQL

Reading a plan tells you why one query is slow. It doesn't tell you which queries to look at in the first place. In a real application with hundreds of query shapes, the slow one is rarely the one you'd guess. You need the database to tell you where the time actually goes, across the whole workload, over days. That's what pg_stat_statements does, and it's the first thing I turn on in any production Postgres.

This is part 15 of the PostgreSQL Masterclass, following query optimization.

Turn on pg_stat_statements

pg_stat_statements is a bundled extension that records execution statistics for every query, grouped by shape. It normalizes queries by replacing literal values with placeholders, so WHERE id = 1 and WHERE id = 2 count as one entry with a call count and total time.

Enable it by adding it to shared_preload_libraries (this needs a restart) and creating the extension:

-- in postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- then, after restart:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

From then on it accumulates statistics continuously. The overhead is small, and the visibility it gives is worth far more than it costs.

Find where the time goes

The most useful query ranks statements by total time, which is calls multiplied by mean time. This surfaces the queries that hurt most in aggregate, which is what matters. A query that takes 5ms but runs a million times a day is a bigger problem than one that takes 2 seconds and runs twice.

SELECT
  substring(query, 1, 80) AS query,
  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
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

That pct column tells you what share of total database time each query shape consumes. Very often the top 5 shapes account for most of the load, and fixing those moves the whole system. This is the single most valuable query in this article.

Sort by the symptom you have

Different problems want different orderings:

• Highest total_exec_time: the overall biggest time sinks. Start here.
• Highest mean_exec_time: the individually slowest queries, good for finding one-off monsters.
• Highest calls: the most frequent queries, where shaving a millisecond scales up. Often reveals N+1.
• Look at stddev_exec_time: a high standard deviation means the query is fast sometimes and slow others, which points at parameter-dependent plans or lock waits.

Once a query shape rises to the top, copy it, fill in representative values, and run EXPLAIN (ANALYZE, BUFFERS) on it. That connects this whole-workload view back to the single-query diagnosis from the previous articles.

Reset the baseline

The statistics accumulate since the last reset or restart, so they're a lifetime total. When you're actively tuning, reset the counters, let the workload run for a known window, then look. That gives you a clean picture of current behavior instead of history:

SELECT pg_stat_statements_reset();
-- let it run for an hour under normal load, then query again

Catch slow queries as they happen

pg_stat_statements is aggregate. Sometimes you also want a log of individual slow executions, with their actual parameters. log_min_duration_statement writes any query slower than a threshold to the Postgres log:

-- log every statement that takes longer than 1 second
ALTER SYSTEM SET log_min_duration_statement = '1000ms';
SELECT pg_reload_conf();

Set it high enough that it doesn't flood the log, then read those entries to catch the specific slow calls, including the exact values that triggered them. For even deeper analysis, auto_explain can log the plan of slow queries automatically, so you get the EXPLAIN output without reproducing the query by hand.

Find unused and missing indexes

The same statistics views help you clean up indexes. pg_stat_user_indexes shows how often each index has been scanned. Indexes with idx_scan = 0 over a long uptime are dead weight, taxing writes for no read benefit:

SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Before dropping one, confirm it isn't backing a constraint and hasn't just been unused since the last stats reset. On the other side, tables with high seq_scan counts in pg_stat_user_tables are candidates for a new index, since they're being read end-to-end often.

The workflow

Putting it together, finding and fixing slow queries across a real system looks like this:

1. Keep pg_stat_statements on in production always.
2. Rank query shapes by total time to find the ones that matter.
3. Run EXPLAIN (ANALYZE, BUFFERS) on the top offenders and apply the fixes from the previous articles.
4. Use log_min_duration_statement to catch specific slow executions with their real parameters.
5. Periodically drop indexes with zero scans and add indexes to tables with heavy sequential scans.
6. Reset stats when tuning so you measure the change, not the history.

The theme of this whole module is replacing intuition with measurement. pg_stat_statements is the top of that funnel: it points you at the queries worth your attention, so you spend your time fixing the 5 things that matter instead of guessing at 50 that don't.

That closes the indexing and performance module. Next, we move into transactions and concurrency, starting with what ACID actually guarantees and how PostgreSQL delivers it.