PostgreSQL slow queries: causes, detection and fixes
9 min read
Why a PostgreSQL query becomes slow, how to detect it with pg_stat_statements and EXPLAIN ANALYZE, and how PWR automatically identifies it as soon as it drifts.
What exactly is a slow query?
There is no universal threshold: a 200 ms query can be perfectly normal for a nightly analytical job, and catastrophic for an endpoint called on every user click. What defines a slow query is the gap between its usual execution time and its observed execution time at a given moment — not a fixed absolute value.
Two complementary angles let you spot it: the time already elapsed for a query still running (useful for detecting a live blockage), and the average total execution time of a query once finished, aggregated over many calls (useful for spotting a gradual drift rather than an isolated incident).
The most common causes
A missing or unused index remains the most common cause: PostgreSQL then has to scan (seq scan) a large part of the table instead of directly targeting the rows sought. A sort (ORDER BY) on an unindexed column makes it even worse, especially combined with a filter on another column — exactly the scenario detailed in our 5-minute diagnosis case study.
Outdated planner statistics (autovacuum lagging behind or disabled) are a second frequent cause: PostgreSQL chooses its execution plan from cardinality estimates — if those estimates are wrong, it may choose a full scan where an index would have been much faster.
Then come: a lock held by another transaction (the query isn't slow, it's waiting), insufficient work_mem forcing a sort or hash join to spill onto temporary disk, and an explosion in the volume of data processed by a query that stayed fast as long as the table was small.
Detecting slow queries with pg_stat_statements
The pg_stat_statements extension aggregates, for every normalized query (literal values replaced with parameters), the number of calls, cumulative and average execution time, rows returned, and blocks read from cache or disk. It is the reference data source for identifying, over time, which queries cost your instance the most.
The query below ranks queries by decreasing average execution time — a good starting point for any slowness investigation.
SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;Reading an execution plan with EXPLAIN ANALYZE
Once the suspect query is identified, EXPLAIN (ANALYZE, BUFFERS) actually runs it and shows the plan PostgreSQL chose, along with the real time spent on each step (scan, sort, join) and the blocks read from cache or disk. It is the tool that confirms — or rules out — the missing-index hypothesis.
Signals to watch for in the output: a Seq Scan on a large table despite a selective filter being applied, a large gap between estimated rows (rows=) and actual rows returned (a sign of outdated statistics), and a sort or hash join that spills to disk ("external merge" or "Disk" in the output) for lack of sufficient work_mem.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pgbench_accounts
WHERE aid <= 1000
ORDER BY abalance;How PWR automatically identifies slow queries
Rather than waiting for a user to report a slowdown, PWR continuously monitors two levels: live, the Live Health Banner counts active queries whose elapsed time already exceeds 1, 5 or 10 seconds; over time, each snapshot captures the state of pg_stat_statements and lets you compare two points in time to precisely isolate which query started drifting.
The PWR report automatically ranks queries by execution time, CPU consumption and call volume, and offers a per-query history (Query History) showing its evolution snapshot after snapshot — enough to pinpoint exactly when a regression appeared, without having to manually replay EXPLAIN ANALYZE one by one.
Fixing a slow query: the short method
In order: confirm the cause with EXPLAIN ANALYZE, check whether a composite index tailored to the filter and the sort would resolve the full scan, make sure statistics are up to date (manual ANALYZE if autovacuum is lagging), then adjust work_mem if a sort or join spills to disk. Every change should be re-checked with a new execution plan to confirm the actual gain.
This is exactly the method applied in our concrete case study: a well-chosen composite index dropped CPU load from 32% to 8% within minutes.