How to identify slow queries in PostgreSQL
Quick answer: Enable the
pg_stat_statementsextension and sort bytotal_exec_time(which equalscalls × mean_exec_time), not by average time. The query costing your database the most is usually a fast query that runs constantly: highcalls, lowmean_exec_time, and a large total. Start with the top of that list.
What makes a query “slow”, total time, not average
Section titled “What makes a query “slow”, total time, not average”There are two questions:
- “Which single query is slowest?” → sort by
mean_exec_time. - “Which query is costing my database the most?” → sort by
total_exec_time.
The second question is usually more useful. A report that takes 3 seconds but runs twice a day costs 6 seconds of database time. A 4-millisecond lookup that runs two million times a day costs over two hours. The 4ms query never shows up in a “slowest query” view, but it may be the actual problem.
total_exec_time = calls × mean_exec_time. Sorting by total time finds queries
with the largest aggregate cost.
How do I enable pg_stat_statements?
Section titled “How do I enable pg_stat_statements?”pg_stat_statements is a contrib extension that records execution statistics for
each normalized query. PostgreSQL must load it at server start:
-
Add it to
shared_preload_librariesinpostgresql.conf:shared_preload_libraries = 'pg_stat_statements' -
Restart PostgreSQL.
-
Create the extension in the database you want to inspect:
CREATE EXTENSION pg_stat_statements;
The extension then tracks up to pg_stat_statements.max statements (5,000 by default).
When the limit is reached, it evicts the least-executed statements.
How do I find the slow queries?
Section titled “How do I find the slow queries?”Rank by cumulative execution time:
SELECT calls, total_exec_time, mean_exec_time, rows, queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 20;On PostgreSQL 12 and earlier, the columns are total_time and mean_time.
PostgreSQL 13 renamed them to total_exec_time / mean_exec_time (and added
total_plan_time / mean_plan_time), because planning and execution are now
timed separately.
Read the top of the list this way:
| Pattern | What it means |
|---|---|
High total_exec_time, high calls, low mean_exec_time | A cheap query run constantly. This is the most common culprit |
High total_exec_time, low calls, high mean_exec_time | A heavy query: a report, a big aggregate, or a missing index |
High rows / calls | Returning more than the app uses, from a missing LIMIT or an over-broad SELECT. rows is the cumulative total across every execution, so divide by calls for the per-call figure |
The numbers are cumulative since the last reset, not point-in-time. To measure a specific window, reset first and let it run:
SELECT pg_stat_statements_reset();When called with no arguments, that wipes the statistics for every query, user,
and database on the instance, including statistics that someone else is
measuring. Scope it when you can: pg_stat_statements_reset(userid, dbid, queryid) resets only what you name.
Why cannot my AI coding agent just find these?
Section titled “Why cannot my AI coding agent just find these?”The signal exists only in production’s aggregate statistics, not in your
code. Your coding agent sees the query in the source:
User.objects.get(id=...) looks completely fine. It cannot see that this
particular line runs two million times a day and now dominates total
database time.
“Which query costs the most” is a runtime property of your real traffic. It
lives in pg_stat_statements, not in the repository. An agent that reasons
only over the code cannot rank queries by production impact, so it optimizes
what looks expensive, not what is expensive.
How do I keep slow queries from creeping back?
Section titled “How do I keep slow queries from creeping back?”- Review the top of the
total_exec_timelist on a schedule, after each reset window, or weekly in a dashboard. - Watch for new entries with a fast-growing
callscount. These are often a newly shipped N+1 or an un-indexed lookup. - Track the ratio of
rowsreturned to rows the application actually uses.
Should I sort pg_stat_statements by mean_exec_time or total_exec_time?
Sort by total_exec_time to find what to optimize. total_exec_time = calls × mean_exec_time, so it captures cheap-but-frequent queries that dominate real database load. mean_exec_time only tells you the average cost of one call, which can hide a huge aggregate.
How do I enable pg_stat_statements?
Add pg_stat_statements to shared_preload_libraries in postgresql.conf, restart PostgreSQL, then run CREATE EXTENSION pg_stat_statements in the database you want to inspect. It requires a restart because it loads at server start.
Why did the pg_stat_statements columns change name?
PostgreSQL 13 split timing into planning and execution: total_time/mean_time became total_exec_time/mean_exec_time, and total_plan_time/mean_plan_time were added. On PostgreSQL 12 and earlier, use total_time and mean_time.
Are the pg_stat_statements numbers point-in-time?
No. They are cumulative since the last statistics reset, and by default they survive a clean server restart (pg_stat_statements.save is on). Call pg_stat_statements_reset() to zero them, then measure over a known window.