SpiritCode.blog

How to Debug Slow PostgreSQL Queries With pg_stat_statements

I finally stopped guessing which query was killing my database — here’s the exact workflow I use to find and fix slow PostgreSQL queries with pg_stat_statements.

Last updated: July 4, 2026

I still remember the Friday afternoon our checkout API started timing out under normal traffic. No deploys had gone out, no traffic spike, nothing obvious in the logs. I spent two hours staring at application code before I realized the problem wasn’t the app at all — it was a single PostgreSQL query that had quietly gotten 40x slower after a data migration. That day I finally set up pg_stat_statements properly, and I haven’t debugged a slow query blind since.

TL;DR

  • pg_stat_statements is a built-in PostgreSQL extension that tracks execution stats for every normalized query running on your server, no external tooling required.
  • You can find your worst offenders in under five minutes by sorting by total_exec_time, mean_exec_time, or calls.
  • Combine it with EXPLAIN (ANALYZE, BUFFERS) to go from “this query is slow” to “this is exactly why, and here’s the fix.”

Why pg_stat_statements Matters

Most teams find out about slow queries the hard way: a support ticket, a spike in p99 latency, or a database CPU graph that looks like a cliff. By the time someone opens a monitoring dashboard, the damage — timeouts, retries, sometimes a cascading outage — is already done.

pg_stat_statements flips this around. It’s a statistics-collector extension shipped with core PostgreSQL that aggregates every query executed against the server, tracking things like total execution time, number of calls, rows returned, and buffer hits. Instead of reacting to incidents, I use it to proactively scan for regressions every week, and it’s caught at least three production issues before they became outages for my team.

[SOURCE: https://www.postgresql.org/docs/current/pgstatstatements.html]

Prerequisites

Before you start, make sure you have:

  • PostgreSQL 13+ (I’ve tested this workflow on 13, 14, 15, and 16; behavior is nearly identical, though column names changed slightly in 13)
  • Superuser or rds_superuser access (for RDS/Aurora) to install extensions
  • shared_preload_libraries access — this requires a server restart, so plan for a maintenance window if you’re on a self-managed instance
  • Basic comfort reading EXPLAIN output

Important: Enabling pg_stat_statements requires a database restart because it needs to be loaded via shared_preload_libraries. You can’t just run CREATE EXTENSION and be done — I learned this the hard way when I tried to enable it on a production RDS instance during business hours and had to explain an unplanned failover to my team lead.

Step-by-Step Implementation

Step 1: Enable the extension

On self-managed PostgreSQL, edit postgresql.conf:

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

Then restart PostgreSQL:

sudo systemctl restart postgresql

On managed services like Amazon RDS or Aurora, you instead modify the parameter group and add pg_stat_statements to shared_preload_libraries, then reboot the instance from the console.

Step 2: Create the extension in your database

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify it’s active:

SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

Step 3: Find your slowest queries by total time

This is the query I run first, every time:

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

total_exec_time tells you which queries are consuming the most cumulative time on your server — not necessarily the slowest single execution, but the ones costing you the most overall. That distinction matters: a query that runs in 5ms but executes 2 million times a day can cost more than a report that runs once and takes 3 seconds.

Step 4: Sort by mean_exec_time for the “quietly slow” queries

SELECT
  query,
  calls,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 10;

I filter with calls > 50 here because a query that ran once and took 8 seconds (maybe a migration or an ad-hoc admin query) will otherwise pollute the top of your list and waste your time.

Step 5: Get the execution plan with EXPLAIN ANALYZE

Once I’ve identified a candidate, I pull the actual query text (pg_stat_statements normalizes literals into $1, $2, etc.) and run:

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

I’m specifically looking for Seq Scan on large tables, high Buffers: shared read counts (meaning data isn’t cached), and any row-count estimate that’s wildly off from the actual rows returned — that mismatch usually means stale statistics.

Step 6: Fix the common causes

In my experience, the fix falls into one of these buckets almost every time:

-- Missing index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status, created_at DESC);

-- Stale statistics
ANALYZE orders;

-- Bloated table needing a vacuum
VACUUM (ANALYZE, VERBOSE) orders;

Pro Tip: Always use CREATE INDEX CONCURRENTLY in production. A regular CREATE INDEX takes an ACCESS EXCLUSIVE lock and will block writes to the table for the duration of the build — on a large table that can mean minutes of blocked traffic.

Step 7: Reset stats after each fix to measure impact

SELECT pg_stat_statements_reset();

I reset stats right after deploying an index or query change, then check mean_exec_time again 24 hours later. This gives me a clean before/after comparison instead of averaged-in historical noise.

Real-World Tips I Use in Production

  • I schedule a weekly cron job that dumps the top 20 queries by total_exec_time into a Slack channel — it’s caught regressions from ORM changes that nobody flagged in code review.
  • I always cross-reference pg_stat_statements with pg_stat_user_tables to check seq_scan counts on large tables; a high total_exec_time paired with high sequential scans is almost always a missing index.
  • On RDS, I set pg_stat_statements.max = 10000 and monitor pg_stat_statements_info.dealloc — if that number keeps climbing, older query stats are getting evicted before I can review them.

[SOURCE: https://github.com/postgres/postgres/tree/master/contrib/pg_stat_statements]

[INTERNAL LINK: related article]

Common Errors and How I Fixed Them

Error: pg_stat_statements must be loaded via shared_preload_libraries This happens when you run CREATE EXTENSION before adding it to shared_preload_libraries and restarting. Fix: update the config, restart the instance, then create the extension.

Error: query text shows up as <insufficient privilege> Non-superuser roles can’t see full query text by default in older versions. I granted the pg_read_all_stats role to my monitoring user to fix this without giving out full superuser access.

Gotcha I discovered the hard way: pg_stat_statements normalizes queries, which means two logically different queries with different literal values get merged into one row — great for aggregate analysis, but it means you sometimes need to run EXPLAIN with a representative set of parameters, not the exact ones you see in the stats table, since the literals aren’t stored.

FAQ

Q: How do I install pg_stat_statements on Amazon RDS? A: Add pg_stat_statements to the shared_preload_libraries parameter in your DB parameter group, reboot the instance, then run CREATE EXTENSION pg_stat_statements; inside your database.

Q: What’s the difference between total_exec_time and mean_exec_time in pg_stat_statements? A: total_exec_time is the cumulative time spent on all executions of a query, while mean_exec_time is the average time per execution — use total for overall server load and mean for spotting individually slow queries.

Q: Does pg_stat_statements slow down my PostgreSQL server? A: The overhead is generally under 5% in most workloads since it just increments in-memory counters, but very high query volume with track = all can add measurable overhead worth benchmarking.

Q: How often should I reset pg_stat_statements statistics? A: I reset stats after any significant schema or query change to get a clean measurement window, and otherwise let them accumulate for at least a week to capture real traffic patterns.

Q: Can pg_stat_statements show me the exact query parameters that were slow? A: No — it normalizes literals into placeholders like $1, so you’ll need to pair it with logging (log_min_duration_statement) if you need the exact values behind a specific slow execution.

Conclusion

pg_stat_statements turned query debugging from a guessing game into a repeatable process for my team: enable it, sort by total or mean execution time, confirm with EXPLAIN ANALYZE, and fix the root cause instead of throwing more compute at the problem.

About the Author

I’m a backend engineer with over 8 years of experience building and operating PostgreSQL-backed systems at scale, mostly on AWS RDS and Aurora. I spend a good chunk of my time on query optimization, schema design, and incident response for high-traffic e-commerce platforms. When I’m not knee-deep in EXPLAIN plans, I write about the PostgreSQL and DevOps lessons I wish someone had told me earlier.