Find Your Worst Postgres Query in 15 Minutes with pg_stat_statements

If your app has a slow endpoint and you're staring at application logs trying to guess which query is the culprit, stop. Postgres already tracked every query it ran, how long each one took, and how often - you just haven't asked it yet.

pg_stat_statements is a built-in extension that turns "something feels slow" into "this exact query, called 40,000 times a day, is burning 60% of your database's CPU." Fifteen minutes from now you'll have a ranked list of your worst offenders and a fix for the top one.

Step 1: Turn it on (2 minutes)

pg_stat_statements ships with Postgres but isn't loaded by default. It needs to be in shared_preload_libraries, which means a config change and a restart - this is the one part of this technique you can't do without a brief window of downtime or a failover if you're on a managed HA setup.

Check if it's already loaded:

SHOW shared_preload_libraries;

If you don't see pg_stat_statements in the output, add it:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Restart Postgres, then create the extension in the database you care about:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

If you're on a managed provider (RDS, Cloud SQL, Vultr Managed Databases), this is usually a checkbox in a "shared preload libraries" or "extensions" panel rather than a config file edit - the SQL step is the same either way.

One caveat that trips people up: query text with literal values gets normalized into placeholders ($1, $2) automatically. Depending on your Postgres version and pg_stat_statements.track_utility setting, normalization behavior can vary slightly - don't worry about it, the ranking logic below works the same regardless.

Step 2: Find the worst offender (3 minutes)

The extension exposes a view called pg_stat_statements. The two columns that matter most are total_exec_time (how much cumulative time this query has cost the database) and mean_exec_time (how long a single call takes on average). They answer different questions, and conflating them is the most common mistake people make here.

To find what's costing you the most in aggregate - the query worth fixing first for overall database load:

SELECT round(total_exec_time::numeric, 2) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_of_total,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

That pct_of_total column is the one to watch. It's common to find a single query responsible for 30-50% of total database time - not because it's slow per call, but because something is calling it far more often than it needs to (a classic N+1 pattern, a missing cache, a loop that should be a batch query).

To find your worst tail-latency offenders - queries that are individually slow and likely to trip timeout thresholds or make a specific page feel broken:

SELECT round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       calls,
       query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;

The calls > 10 filter matters - without it you'll get a one-off migration query or an analyst's ad-hoc SELECT * polluting the top of your list. You want repeated production traffic, not noise.

Step 3: Confirm and fix (8 minutes)

Take the query text from whichever list surfaced your real problem, substitute realistic values for the $1/$2 placeholders, and run it through EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 48213
  AND status = 'pending'
ORDER BY created_at DESC;

Look for two things in the output:

• a Seq Scan on a table with more than a few thousand rows
• a high Buffers: shared read count relative to shared hit (that's disk I/O, not cache - expensive)

If you see a sequential scan on a filtered, frequently-run query, that's almost always