pg_stat_statements

pg_stat_statements tracks normalized query text with call count, total time, rows, and I/O. It answers which queries consume the most database time, not which single run was slowest. Enable on every production PostgreSQL 18.4 cluster and reset stats after major releases for clean baselines.

Recipe

-- Enable (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
 
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Top queries by total time
SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

When to reach for this:

• Latency regression without obvious infra cause
• Index tuning sprints prioritizing high total_exec_time
• Post-deploy verification comparing query fingerprints

Working Example

Find regressions and tie stats to EXPLAIN.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Heavy hitters last 24h of cumulative stats (since reset)
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_db_time,
       shared_blks_read,
       shared_blks_hit,
       left(query, 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 15;
 
-- Reset after deploy baseline (superuser)
-- SELECT pg_stat_statements_reset();
 
-- Example EXPLAIN on hot query text from app logs matching queryid
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = $1
  AND o.created_at > now() - interval '30 days';

What this demonstrates:

• total_exec_time ranks workload impact better than single slow log lines
• queryid correlates ORM queries with planner stats
• Buffer read/hit ratio hints cache vs I/O bound
• Pair with EXPLAIN (ANALYZE, BUFFERS) on representative parameters

Deep Dive

Key Columns

Configuration

SHOW shared_preload_libraries;
SHOW pg_stat_statements.max;
SHOW pg_stat_statements.track;  -- top, all, none

Use track = all inside PL/pgSQL-heavy apps if you need nested statements.

Managed Postgres

RDS and Cloud SQL offer pg_stat_statements as allowlisted extension. Parameter group may require shared_preload_libraries reboot.

Gotchas

• Not in shared_preload_libraries - extension creates but stats empty. Fix: add to preload, restart once.
• Chasing mean time only - rare 5s query with 10 calls vs 50ms with 1M calls. Fix: sort by total_exec_time first.
• Literal-heavy queries - millions of fingerprints without normalization from apps. Fix: parameterized queries; ORM prepared statements.
• Resetting stats during incident - destroys evidence. Fix: snapshot top queries to ticket before reset.
• Security: query text visible - may contain PII literals. Fix: restrict pg_read_all_stats; redact in exports.
• PgBouncer + transaction pooling - stats aggregate on server correctly but app session mapping is opaque. Fix: use application_name tags per service.

Alternatives

FAQs

Related

• Monitoring Basics - observability overview
• EXPLAIN Basics - plan reading
• postgres_exporter & Grafana - metrics export
• SLO Dashboards - latency budgets
• Monitoring Best Practices - operational checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.