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'

  2. Restart PostgreSQL (required — the library loads at startup).

  3. 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 can't my AI coding agent just find these?​

Because the signal doesn't 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.