Skip to main content

How to identify slow queries in PostgreSQL

Quick answer: Enable the pg_stat_statements extension and sort by total_exec_time (which equals calls × mean_exec_time), not by average time. The query costing your database the most is usually a fast query that runs constantly, high calls, low mean_exec_time, enormous total. Fix the top of that list for the biggest win.

What makes a query "slow", total time, not average​

There are two different questions, and they have different answers:

  • "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 one is what you actually want. 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 is invisible in any "slowest query" view and is very often the real problem.

total_exec_time = calls × mean_exec_time. Optimizing by total time targets the queries with the largest aggregate footprint, which is where the wins are.

How do I enable pg_stat_statements?​

pg_stat_statements is a contrib extension that records aggregated execution statistics for every normalized query. It must be loaded at server start:

  1. Add it to shared_preload_libraries in postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'

  1. Restart PostgreSQL (required, the library loads at startup).

  2. Create the extension in the database you want to inspect:

CREATE EXTENSION pg_stat_statements;

It then tracks up to pg_stat_statements.max statements (5,000 by default), evicting the least-executed when full.

How do I find the slow queries?​

Rank by cumulative execution time:

SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 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:

PatternWhat it means
High total_exec_time, high calls, low mean_exec_timeA cheap query run constantly, the most common real culprit
High total_exec_time, low calls, high mean_exec_timeA genuinely heavy query, a report, a big aggregate, a missing index
High rows / callsReturning more than the app uses, missing LIMIT or 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();

Called with no arguments, that wipes the statistics for every query, user, and database on the instance, including whatever anyone else is currently 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?​

Because the signal does not exist in your code, it only exists in production's aggregate statistics. Your coding agent (Claude Code, Cursor, Copilot) sees the query in the source: User.objects.get(id=...) looks completely fine. What it cannot see is that this particular line runs two million times a day and now dominates total database time.

"Which query costs the most" is an emergent, runtime property of your real traffic. It lives in pg_stat_statements, not in the repository. An agent reasoning only over the code has no way to rank queries by production impact, so it optimizes what looks expensive, not what is expensive.

How do I keep slow queries from creeping back?​

  • Review the top of the total_exec_time list on a schedule, after every reset window, or weekly in a dashboard.
  • Watch for new entries with a fast-growing calls count; those are usually a freshly shipped N+1 or an un-indexed lookup.
  • Track the ratio of rows returned to rows the application actually uses.

How DBGorilla helps​

DBGorilla connects to your database read-only and gives your AI coding agent (Claude Code, Cursor) the real pg_stat_statements data, so instead of guessing from the source, your agent can rank queries by total_exec_time, point at the one dominating production, and explain why. It reads and explains; it does not touch production. Get started free →

FAQ​

Should I sort pg_stat_statements by mean_exec_time or total_exec_time? Sort by total_exec_time to decide what to optimize. It equals calls × mean_exec_time, so it captures cheap-but-frequent queries that dominate real load. mean_exec_time only shows the average cost of a single call.

How do I enable pg_stat_statements? Add it to shared_preload_libraries, restart PostgreSQL, then run CREATE EXTENSION pg_stat_statements in the target database. The restart is required because the module loads at server start.

Why did the pg_stat_statements columns change name? PostgreSQL 13 separated planning from execution timing: total_time/mean_time became total_exec_time/mean_exec_time, with total_plan_time/mean_plan_time added. Use total_time/mean_time on 12 and earlier.

Are the pg_stat_statements numbers point-in-time? No. They are cumulative since the last reset or server start. Called with no arguments, pg_stat_statements_reset() wipes stats for every query, user, and database on the instance; scope it with userid/dbid/queryid if you only need a slice, then measure a known window.