Find the most expensive queries (pg_stat_statements: total and mean time)

Command

-- By total time: queries consuming the most server time
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_total,
       rows / NULLIF(calls, 0) AS rows_per_call,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- By mean time per call: individually slow queries
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       round(mean_plan_time::numeric, 2) AS mean_plan_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE calls >= 50
ORDER BY mean_exec_time DESC
LIMIT 20;

What it does

Lists the normalized queries that consume the most server time in the current database, with their share of the total; the second query lists the slowest ones per call.

When to use it

First stop when CPU/IO load rises or users report 'the database is slow'; to decide where tuning should start.

Caution

pg_stat_statements collects nothing until it is added to shared_preload_libraries, the server is restarted and CREATE EXTENSION is run in the database. Values are cumulative since the last reset. A stddev_ms that is high relative to mean_ms indicates that the plan or data distribution varies between calls. mean_plan_time is populated only with pg_stat_statements.track_planning = on (default off; enabling it adds overhead). Seeing other users' query texts requires superuser or the pg_read_all_stats role.