Where the database time goes: pg_stat_statements

2026-05-27 · #postgres

Before guessing which query is slow, ask the server. The extension ships with PostgreSQL but has to be preloaded, which needs a restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

Then sort by total time. The top of this list is usually not the slowest query but a moderately fast one called a million times.

SELECT calls,
       round(total_exec_time)        AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 80)               AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Queries are normalised, so literals are replaced with placeholders and identical shapes are counted together. To measure the effect of a fix, reset the counters with SELECT pg_stat_statements_reset(); and look again after a normal working hour.