patternsqlpostgresqlTip
pg_stat_statements reveals the actual slow queries in production
Viewed 0 times
pg_stat_statementsslow queryquery statisticstotal_exec_timequery profilingshared_preload_libraries
Problem
Query logs with log_min_duration_statement catch individual slow executions but miss queries that are individually fast yet collectively expensive due to high call frequency.
Solution
Enable and query pg_stat_statements:
-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Top 10 queries by total time:
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Top 10 queries by total time:
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Why
pg_stat_statements aggregates execution statistics across all calls since the last pg_stat_statements_reset(). This surfaces queries that are called millions of times per day even if each call takes under 1ms.
Gotchas
- Requires server restart to enable (shared_preload_libraries is not reloadable)
- Call pg_stat_statements_reset() after making index or query changes so metrics reflect the new state
- The queryid column joins to pg_stat_activity for in-flight query matching
- Track planning time separately with total_plan_time (PG 13+)
Code Snippets
Find queries with disproportionate planning overhead
-- Queries with high planning overhead:
SELECT query, calls,
round(mean_plan_time::numeric,2) AS mean_plan_ms,
round(mean_exec_time::numeric,2) AS mean_exec_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_plan_time DESC
LIMIT 10;Context
Diagnosing database performance problems in production without per-query profiling tools
Revisions (0)
No revisions yet.