Using pg_stat_statements to Find and Fix Slow Queries

The pg_stat_statements extension is one of the most powerful tools in PostgreSQL for identifying and optimizing slow queries. Unlike log-based approaches, it tracks cumulative statistics for all SQL statements executed, giving you a clear picture of where your database spends its time. This guide covers installation, configuration, and practical query optimization workflows.

Installing pg_stat_statements

The extension comes bundled with PostgreSQL but needs to be explicitly enabled. First, add it to the shared preload libraries:

# Edit postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# Additional recommended settings
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on

Restart PostgreSQL to load the shared library, then create the extension:

sudo systemctl restart postgresql

psql -U postgres -d yourdb
CREATE EXTENSION pg_stat_statements;

Understanding the View Columns

The pg_stat_statements view contains dozens of columns. The most important ones for performance analysis are:

• query — The normalized SQL statement (with constants replaced by parameters)
• calls — Total number of times the statement was executed
• total_exec_time — Cumulative execution time in milliseconds
• mean_exec_time — Average execution time per call
• rows — Total number of rows retrieved or affected
• shared_blks_hit / shared_blks_read — Buffer cache hits vs. disk reads
• plans — Number of times the statement was planned (PostgreSQL 13+)
• total_plan_time — Cumulative planning time

Finding the Slowest Queries

By Total Time (Biggest Overall Impact)

SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) AS pct,
    substr(query, 1, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This is your most valuable query. Statements at the top consume the most total database time — even a small optimization here yields significant gains.

By Average Execution Time (Slowest Individual Queries)

SELECT
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    calls,
    rows,
    substr(query, 1, 100) AS query_preview
FROM pg_stat_statements
WHERE calls > 10  -- Filter out rare one-off queries
ORDER BY mean_exec_time DESC
LIMIT 20;

By Cache Miss Ratio (I/O Bound Queries)

SELECT
    round(mean_exec_time::numeric, 2) AS avg_ms,
    shared_blks_hit,
    shared_blks_read,
    round(shared_blks_read::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS miss_pct,
    substr(query, 1, 100) AS query_preview
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 100
ORDER BY miss_pct DESC
LIMIT 20;

Analyzing a Specific Slow Query

Once you identify a problematic query from pg_stat_statements, use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to understand its execution plan:

EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING)
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
  AND o.status = 'pending';

Look for sequential scans on large tables, nested loops with high row counts, and sort operations that spill to disk.

Common Optimization Patterns

Add Missing Indexes

-- If pg_stat_statements shows a slow query filtering on status + date:
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC)
WHERE status IN ('pending', 'processing');

Fix N+1 Query Patterns

If you see the same parameterized query with an extremely high call count relative to other queries, it likely indicates an N+1 problem in your application. Batch these into a single query with IN or ANY clauses.

Tune work_mem for Sort-Heavy Queries

-- Check if sorts are spilling to disk
SELECT query, local_blks_written, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

-- Increase work_mem for sort-heavy sessions
SET work_mem = '256MB';  -- Session level only

Resetting Statistics

Reset statistics periodically (e.g., after a deployment) to see fresh metrics:

SELECT pg_stat_statements_reset();

Consider resetting after major schema changes, application deployments, or PostgreSQL version upgrades to get clean baseline measurements.

Automated Monitoring with pg_stat_statements

For continuous monitoring, create a snapshot system that captures statistics at regular intervals:

CREATE TABLE pgss_snapshots (
    snapshot_id SERIAL PRIMARY KEY,
    captured_at TIMESTAMPTZ DEFAULT NOW(),
    queryid BIGINT,
    query TEXT,
    calls BIGINT,
    total_exec_time DOUBLE PRECISION,
    mean_exec_time DOUBLE PRECISION,
    rows BIGINT
);

-- Run via cron every hour
INSERT INTO pgss_snapshots (queryid, query, calls, total_exec_time, mean_exec_time, rows)
SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
WHERE calls > 5;

This lets you compare query performance over time and detect regressions after deployments.

Best Practices

• Set pg_stat_statements.max to at least 10,000 for production workloads to avoid evicting important entries
• Enable track_planning in PostgreSQL 13+ to catch queries with expensive planning phases
• Focus on total time first (not average time) — a fast query called millions of times often matters more than a slow query called once
• Combine with auto_explain to automatically log execution plans for slow queries
• Export metrics to Prometheus using postgres_exporter for alerting on query performance degradation