Skip to content

How to identify slow queries in PostgreSQL

Last updated

View as Markdown

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, 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.

pg_stat_statements is a contrib extension that records execution statistics for each normalized query. PostgreSQL must load it at server start:

  1. Add it to shared_preload_libraries in postgresql.conf:

    shared_preload_libraries = 'pg_stat_statements'
  2. Restart PostgreSQL.

  3. 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.

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. This is the most common culprit
High total_exec_time, low calls, high mean_exec_timeA heavy query: a report, a big aggregate, or a missing index
High rows / callsReturning 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_time list on a schedule, after each reset window, or weekly in a dashboard.
  • Watch for new entries with a fast-growing calls count. These are often a newly shipped N+1 or an un-indexed lookup.
  • Track the ratio of rows returned 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.