PostgreSQL Slow Query Log: Finding & Fixing Your Slowest Queries

2026-04-16

PostgreSQL's slow query log captures every statement that exceeds a configurable duration threshold. By setting log_min_duration_statement in postgresql.conf, you get a record of your slowest queries — their full SQL text, execution time, and when they ran — without installing any extensions or modifying application code.

The Problem

By default, PostgreSQL does not log slow queries. The log_min_duration_statement parameter is set to -1 (disabled), which means no queries are logged based on duration. This is a safe default — logging every query on a busy database would generate enormous log files — but it means slow queries go completely unnoticed unless a user complains or an application timeout fires.

The result is that performance problems accumulate silently. A query that takes 500ms today might have taken 50ms six months ago, but without slow query logging nobody has visibility into the regression. The query gradually degrades as the table grows, and by the time it crosses the pain threshold, the root cause is buried in months of schema changes and data growth.

Even teams that enable slow query logging often configure it poorly. Setting the threshold too high (e.g., 10 seconds) catches only catastrophic queries while missing the steady stream of 1-2 second queries that collectively dominate database load. Setting it too low (e.g., 1ms) floods the logs with noise, making it impossible to find meaningful signals. And raw PostgreSQL log files are difficult to analyze at scale — grep and awk can only take you so far when you have gigabytes of log output.

The deeper problem is that slow query logs show you individual query executions, not patterns. A query that runs once at 800ms is less important than a query that runs 10,000 times per hour at 50ms each (500 seconds of total load). Without aggregation across pg_stat_statements, you are optimizing outliers instead of the queries that actually consume the most database resources.

On managed databases (AWS RDS, Aurora, Azure, GCP Cloud SQL), the configuration differs from self-hosted PostgreSQL — parameters are set through parameter groups or console settings rather than postgresql.conf, and log access requires CloudWatch, Azure Monitor, or similar services rather than filesystem access.

How to Detect It

First, check whether slow query logging is enabled and what the current threshold is:

-- Check current slow query log 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 shows -1, slow query logging is disabled entirely. A value of 0 logs every statement (useful for debugging, too noisy for production). A typical production value is 250 to 1000 (milliseconds).

Use pg_stat_statements to find slow queries by aggregate impact, not just individual execution time:

-- Top queries by total execution time (cumulative load on the database)
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;

The total_exec_time column is the most actionable metric. A query averaging 5ms but called 1 million times (5,000 seconds total) deserves more attention than a query averaging 2 seconds but called 10 times (20 seconds total). The standard deviation (stddev_exec_time) reveals inconsistent performers — queries where some executions are fast and others are slow, often due to plan instability or parameter sensitivity.

Check whether auto_explain is available for automatic EXPLAIN plan capture:

-- Check if auto_explain is loaded (captures plans for slow queries automatically)
SELECT name, setting
FROM pg_settings
WHERE name LIKE 'auto_explain%';

-- If not loaded, check available extensions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name = 'auto_explain';

How myDBA.dev Shows It

myDBA.dev eliminates the need to manually parse slow query logs by collecting query performance data continuously and surfacing the most impactful queries automatically. The Queries page aggregates data from pg_stat_statements, ranking queries by total execution time, average duration, call frequency, and rows processed. Each query shows a performance trend sparkline, so you can see at a glance whether a query is degrading over time.

Queries page showing top queries ranked by total execution time with performance grades and trend sparklines

For queries that exceed configurable thresholds, myDBA.dev automatically captures EXPLAIN plans — either via auto_explain integration or its own plan collection. This means you get the query plan for every slow execution without having to reproduce the problem manually. Plan regression detection compares the current plan against historical plans and alerts when the planner switches to a worse strategy (e.g., dropping an index scan in favor of a sequential scan after a statistics update).

The Logs view provides a timeline of slow query events correlated with other database activity — lock waits, checkpoint spikes, vacuum operations. When a slow query coincides with a checkpoint or lock wait, that context is immediately visible instead of requiring you to cross-reference multiple log files.

Logs view showing slow query events correlated with database activity timeline

How to Fix It

Enable slow query logging with an appropriate threshold:

-- Set threshold to 250ms (a good starting point for most workloads)
ALTER SYSTEM SET log_min_duration_statement = '250ms';
SELECT pg_reload_conf();

-- Verify the change took effect
SHOW log_min_duration_statement;

For transactional workloads (web applications, APIs), 250ms is a reasonable starting threshold — it captures meaningfully slow queries without flooding the logs. For analytical workloads where multi-second queries are normal, set it higher (2-5 seconds). You can always lower the threshold later once you have processed the initial findings.

Configure useful log context so each logged query has enough information to diagnose:

-- Add useful context to each log line
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
-- %m = timestamp with milliseconds
-- %p = process ID
-- %q = nothing for non-backend processes (stops prefix for utility processes)
-- %u = user name
-- %d = database name

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

SELECT pg_reload_conf();

Enable auto_explain for automatic plan capture on slow queries:

-- Load auto_explain (add to shared_preload_libraries for permanent use)
-- In postgresql.conf: shared_preload_libraries = 'pg_stat_statements, auto_explain'
-- Then set the threshold:
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
ALTER SYSTEM SET auto_explain.log_analyze = off;    -- on = actual times (adds overhead)
ALTER SYSTEM SET auto_explain.log_buffers = on;      -- show buffer usage
ALTER SYSTEM SET auto_explain.log_format = 'json';   -- structured output
ALTER SYSTEM SET auto_explain.log_nested_statements = on;  -- include function internals

SELECT pg_reload_conf();

Setting auto_explain.log_analyze = off logs the estimated plan without actually running ANALYZE, which avoids adding execution overhead. Set it to on only during targeted debugging sessions — running ANALYZE on every slow query doubles the execution cost.

On AWS RDS and Aurora, configure slow query logging via parameter groups:

# In the RDS/Aurora parameter group:
log_min_duration_statement = 250          # milliseconds
log_statement = none                       # avoid double-logging
shared_preload_libraries = pg_stat_statements,auto_explain
auto_explain.log_min_duration = 500

RDS logs are available in CloudWatch Logs. Enable "Publish to CloudWatch" in the RDS console to export PostgreSQL logs automatically. For Performance Insights (available on RDS and Aurora), enable it in the instance settings — it provides a dashboard similar to pg_stat_statements but integrated with the AWS console.

Enable pg_stat_statements if not already active:

-- Check if pg_stat_statements is loaded
SELECT * FROM pg_stat_statements LIMIT 1;

-- If you get an error, create the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Configure statement tracking limits
ALTER SYSTEM SET pg_stat_statements.max = 10000;  -- track up to 10K distinct queries
ALTER SYSTEM SET pg_stat_statements.track = 'all'; -- track all statements (including nested)
SELECT pg_reload_conf();

Parse and analyze logs with pgBadger for historical analysis:

# Generate an HTML report from PostgreSQL log files
pgbadger /var/log/postgresql/postgresql-*.log -o slow_query_report.html

# Generate a report for a specific date range
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 (stripping literal values), groups them by pattern, and shows total execution time, call frequency, and hourly distribution. It is an excellent complement to real-time monitoring for periodic deep-dive analysis.

How to Prevent It

Establish a slow query budget as part of your performance SLA. Define what "slow" means for your application — for a web API, queries over 100ms may be unacceptable; for a batch pipeline, 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 be triaged.

Monitor query performance trends, not just individual slow queries. A query that degrades from 10ms to 50ms over three months will not trigger a 250ms slow query threshold, but it represents a 5x regression that will eventually become a problem. Tools like myDBA.dev and pganalyze track mean execution time over time, making gradual regressions visible before they cross the pain threshold.

Include EXPLAIN ANALYZE in your code review process for new queries. Before merging any PR that adds or modifies a database query, run EXPLAIN ANALYZE against a production-sized dataset and verify the plan uses indexes appropriately. This catches the most common cause of slow queries — missing indexes on new query patterns — before they reach production.

Reset pg_stat_statements periodically (after major releases) to get a clean baseline. Cumulative statistics since the last reset make it hard to isolate the impact of recent changes. After deploying a significant feature, run SELECT pg_stat_statements_reset() and review the statistics a few hours later to see the new query patterns in isolation.

FAQ

How do I check slow query logs in PostgreSQL?

If log_min_duration_statement is enabled, slow queries are written to the PostgreSQL log file (location depends on your configuration — check log_directory and log_filename settings, typically /var/log/postgresql/). On RDS/Aurora, slow query logs appear in CloudWatch Logs. Use grep for quick searches: grep "duration:" /var/log/postgresql/postgresql-*.log | sort -t: -k4 -rn | head -20 to find the slowest queries. For comprehensive analysis, use pgBadger to generate an HTML report.

How do I enable slow query log in PostgreSQL on AWS RDS?

In the RDS console, modify your DB parameter group and set log_min_duration_statement to your desired threshold in milliseconds (e.g., 250). Apply the parameter group change to your instance (this does not require a reboot). Enable "Publish to CloudWatch" for the PostgreSQL log type to export logs to CloudWatch Logs. For richer analysis, also enable Performance Insights on the instance — it provides a built-in dashboard for query performance analysis without parsing log files.

How do I identify slow running queries in PostgreSQL?

Three complementary approaches: (1) Enable log_min_duration_statement to log individual slow executions to the PostgreSQL log. (2) Query pg_stat_statements to find queries with the highest total_exec_time or mean_exec_time — this shows aggregate impact, not just individual outliers. (3) Check pg_stat_activity for currently running queries: SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;. For root cause analysis, use EXPLAIN (ANALYZE, BUFFERS) on the specific query to see the execution plan.

How do I disable slow query log in PostgreSQL?

Set log_min_duration_statement to -1: ALTER SYSTEM SET log_min_duration_statement = '-1'; SELECT pg_reload_conf();. This stops all duration-based query logging immediately without a restart. To reduce logging volume without disabling it completely, raise the threshold: ALTER SYSTEM SET log_min_duration_statement = '5000'; logs only queries exceeding 5 seconds.