PostgreSQL Slow Query Log: Finding & Fixing Your Slowest Queries

I recently helped a team debug a production outage where their API response times had tripled over three months. The culprit? A single query that degraded from 40ms to 1.2 seconds as the table grew from 2 million to 15 million rows. Nobody noticed because slow query logging was disabled -- PostgreSQL's default. They had zero visibility into query performance until users started complaining. This is the most preventable performance problem in PostgreSQL, and the fix takes one SQL statement.

Why PostgreSQL Doesn't Log Slow Queries by Default

The log_min_duration_statement parameter ships at -1 (disabled). This is a safe default -- logging every query on a busy database generates enormous log files -- but it means slow queries go unnoticed unless something breaks visibly.

The result is that performance problems accumulate silently. A query that took 50ms six months ago might take 500ms today, and without logging there is no trail to follow. The query degrades as the table grows, and by the time it crosses the pain threshold, the root cause is buried under months of changes.

Even teams that enable logging often configure it poorly:

• Threshold too high (10s): catches only catastrophes, misses the steady stream of 1-2 second queries that collectively dominate database load
• Threshold too low (1ms): floods logs with noise, making real signals impossible to find
• No aggregation: slow query logs show individual executions, but a 50ms query running 10,000 times/hour (500 seconds total load) matters more than a single 800ms execution

Detecting the Problem

Check your current configuration:

SELECT
    name,
    setting,
    unit,
    short_desc
FROM pg_settings
WHERE name IN (
    'log_min_duration_statement',
    'log_statement',
    'log_duration',
    'log_line_prefix',
    'auto_explain.log_min_duration'
)
ORDER BY name;

If log_min_duration_statement is -1, you have no slow query visibility.

Use pg_stat_statements to find queries ranked by total impact, not just individual duration:

SELECT
    substring(query, 1, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 1) AS total_time_ms,
    round(mean_exec_time::numeric, 1) AS avg_time_ms,
    round(max_exec_time::numeric, 1) AS max_time_ms,
    round(stddev_exec_time::numeric, 1) AS stddev_ms,
    rows
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not max_exec_time. A query averaging 5ms but called 1 million times (5,000 seconds total) deserves far more attention than a 2-second query called 10 times. The standard deviation column reveals plan instability -- queries that swing between fast and slow depending on parameters.

The Three-Layer Configuration

Effective slow query monitoring requires three complementary layers:

Layer 1: Slow Query Logging

-- 250ms threshold: good starting point for web/API workloads
ALTER SYSTEM SET log_min_duration_statement = '250ms';

-- Add context to each log line
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';

-- Log parameters for parameterized queries (PG 14+)
ALTER SYSTEM SET log_parameter_max_length_on_error = 1024;

SELECT pg_reload_conf();

For analytical workloads where multi-second queries are normal, start at 2-5 seconds.

Layer 2: auto_explain for Plan Capture

-- In postgresql.conf: shared_preload_libraries = 'pg_stat_statements, auto_explain'
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
ALTER SYSTEM SET auto_explain.log_analyze = off;    -- avoids doubling execution cost
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_format = 'json';
ALTER SYSTEM SET auto_explain.log_nested_statements = on;

SELECT pg_reload_conf();

log_min_duration_statement tells you which queries are slow. auto_explain tells you why -- it captures the execution plan automatically without needing to reproduce the problem.

Layer 3: pg_stat_statements for Aggregation

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SELECT pg_reload_conf();

This gives you cumulative query statistics -- total time, call count, mean and max duration, standard deviation, and rows processed. It is the foundation for identifying which queries consume the most database resources.

On AWS RDS/Aurora

Configure via parameter groups instead of postgresql.conf:

log_min_duration_statement = 250
log_statement = none
shared_preload_libraries = pg_stat_statements,auto_explain
auto_explain.log_min_duration = 500

Enable "Publish to CloudWatch" to export PostgreSQL logs. Performance Insights provides a built-in dashboard similar to pg_stat_statements.

Analyzing Logs at Scale

For periodic deep-dive analysis, pgBadger generates HTML reports from log files:

pgbadger /var/log/postgresql/postgresql-*.log -o slow_query_report.html

pgbadger --begin "2026-02-01 00:00:00" --end "2026-02-28 23:59:59" \
    /var/log/postgresql/postgresql-*.log -o february_report.html

pgBadger normalizes queries, groups them by pattern, and shows total execution time, frequency, and hourly distribution.

Building a Prevention Strategy

Define a slow query budget. For a web API, queries over 100ms may be unacceptable. For batch processing, 5 seconds may be fine. Set log_min_duration_statement to match your SLA threshold and treat every logged query as a performance bug to triage.

Monitor trends, not just thresholds. A query degrading from 10ms to 50ms over three months won't trigger a 250ms log threshold, but it's a 5x regression heading toward trouble.

EXPLAIN ANALYZE in code reviews. Before merging any PR that adds or modifies a database query, run EXPLAIN ANALYZE against production-sized data. This catches the most common cause of slow queries -- missing indexes on new query patterns -- before they ship.

Reset pg_stat_statements after releases. Run SELECT pg_stat_statements_reset() after significant deploys to isolate the impact of new code from historical noise.

---

Originally published at mydba.dev/blog/postgres-slow-query-log