PostgreSQL Slow Query Log: How to Enable and Configure It

The PostgreSQL slow query log records the text and duration of any statement that runs longer than a configured threshold. You enable it by setting log_min_duration_statement to a millisecond value (for example 1000 for one second); -1, the default, disables it. Unlike pg_stat_statements, which aggregates timing across executions, the slow query log captures each slow statement individually at the moment it runs, which is what you need when chasing a specific spike rather than a long-term average.

When to Use the Slow Query Log

The slow query log answers a narrow question well: which individual statements crossed a latency line, and what were their exact texts and parameters. It writes one log entry per qualifying execution, so you see the actual query that ran at 14:32, not a rolled-up average. That makes it the right tool for investigating a reported slowdown, an incident postmortem, or a deploy that regressed a specific endpoint.

It is the wrong tool for finding your overall worst offenders by total time consumed. A query that takes 50 ms but runs 100,000 times a minute will never appear if your threshold is 200 ms, yet it may dominate your load. For that ranking, use `pg_stat_statements`, which aggregates calls, total time, and mean time per normalized query. The two are complementary: pg_stat_statements tells you where to look, the slow query log shows you each offending execution with its parameters.

One caution: log_statement = 'all' and a low duration threshold can flood the log on a busy system, adding I/O and disk pressure. Start with a conservative threshold and tighten it.

How to Enable Slow Query Logging

Set the parameters in postgresql.conf, or per session with ALTER SYSTEM / SET. The core setting is log_min_duration_statement. Logging destination changes (log_destination, logging_collector, log_filename) only apply on the relevant scope, but log_min_duration_statement and log_statement take effect with a reload - no restart needed.

# postgresql.conf - log any statement that runs 1 second or longer
log_min_duration_statement = 1000   # milliseconds; -1 disables, 0 logs everything
log_destination = 'jsonlog'         # stderr, csvlog, jsonlog (PG15+), or syslog
logging_collector = on              # required for file-based logs; restart to change
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1440             # minutes; 1440 = 24h (the default)
log_line_prefix = '%m [%p] %q%u@%d %Q '  # %Q prints query_id when computed
compute_query_id = on               # adds a stable query identifier to logs

Apply the changes without a restart where possible:

-- Persist to postgresql.auto.conf, then reload
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

The settings table below describes each parameter. Use a plain integer in milliseconds for log_min_duration_statement (the source value 100ms should be 100, or written with explicit units as '100ms'); log_rotation_age is measured in minutes, so the ambiguous 60 should be 60 minutes or written '60min'.

A reasonable starting point is log_min_duration_statement = 1000, compute_query_id = on, and log_line_prefix including %Q. Avoid log_statement = 'all' in production unless you are debugging, since it logs every statement irrespective of duration.

Reading the Slow Query Log Output

With log_destination = 'stderr' (or via the collector), a logged slow statement looks like this:

2026-06-01 14:32:07.512 UTC [48213] app@orders 3274927198 LOG:  duration: 1843.221 ms  statement: SELECT * FROM orders WHERE customer_id = 90412 ORDER BY created_at DESC;

The duration is wall-clock execution time in milliseconds. The query_id (3274927198 here, emitted because compute_query_id is on and %Q is in the prefix) is stable across executions of the same normalized statement, so you can join slow-log entries to a pg_stat_statements row. The jsonlog destination emits the same fields as structured JSON, which is easier to ship to a log pipeline and parse without regular expressions.

What the slow query log does not give you is the execution plan. Knowing a query took 1843 ms does not tell you whether it sequentially scanned a million rows or waited on a lock. To get the plan, you read it manually with `EXPLAIN (ANALYZE, BUFFERS)`, or you let auto_explain capture it automatically.

Capturing Plans Automatically with auto_explain

auto_explain is a contrib module that logs the execution plan of any statement exceeding its own duration threshold, removing the manual step of re-running EXPLAIN after the fact. It is the difference between "this query was slow" and "this query was slow because it did a sequential scan on a 40-million-row table." Load it via shared_preload_libraries (requires restart) or session_preload_libraries:

# postgresql.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000   # ms; -1 disables, 0 logs all plans
auto_explain.log_analyze = on          # real row counts and per-node timing
auto_explain.log_buffers = on          # buffer hits/reads (needs log_analyze)
auto_explain.log_format = 'json'       # text, xml, json, or yaml
auto_explain.log_nested_statements = on  # plans inside functions

Set auto_explain.log_analyze = on with care: it instruments every qualifying query with per-node timing, which adds measurable overhead on hot paths. Many teams enable it only while investigating, or sample it. Read the resulting plans with the EXPLAIN execution plan guide to interpret scan types, join strategies, and the Buffers lines.

Configuring and reading these logs by hand works for a single incident, but correlating slow-log entries, pg_stat_statements aggregates, and auto_explain plans across a fleet of databases is tedious and usually happens after the slowdown is over. Pulse ingests these signals continuously, links each slow statement to its plan and its aggregate footprint by query_id, and surfaces the regression with a concrete recommendation - add this index, rewrite this join - for a human to approve. See database monitoring best practices for the wider set of signals worth tracking alongside the slow query log.

Frequently Asked Questions

Q: How do I enable the slow query log in PostgreSQL?
A: Set log_min_duration_statement to a millisecond threshold in postgresql.conf (for example 1000 for one second), then run SELECT pg_reload_conf();. No restart is needed for that parameter. To write to files rather than stderr, also set logging_collector = on, which does require a restart.

Q: What is the difference between log_statement and log_min_duration_statement?
A: log_statement logs statements by class (none, ddl, mod, all) regardless of how long they run, so all logs everything. log_min_duration_statement logs only statements that exceed a duration threshold. For a slow query log you want log_min_duration_statement; log_statement = 'all' produces far more volume.

Q: What is the difference between the slow query log and pg_stat_statements?
A: The slow query log records each individual execution that crosses a duration threshold, with the exact statement text and parameters. pg_stat_statements aggregates timing and call counts per normalized query. Use the slow query log to investigate a specific slow execution, and pg_stat_statements to rank your overall worst queries by total time.

Q: Does changing log_min_duration_statement require a restart?
A: No. log_min_duration_statement and log_statement take effect after a configuration reload (SELECT pg_reload_conf(); or SIGHUP). Only file-output settings like logging_collector require a server restart.

Q: How do I get the execution plan of a slow query automatically?
A: Load the auto_explain module and set auto_explain.log_min_duration to a threshold. It logs the execution plan of every statement that exceeds it. Enable auto_explain.log_analyze for real row counts and timing, though that adds runtime overhead.

Q: What units does log_min_duration_statement use?
A: Milliseconds when no unit is given, so 1000 means one second. You can also write explicit units such as '1s' or '250ms'. A value of -1 (the default) disables duration logging, and 0 logs every statement's duration.

Q: Why are no slow queries showing up in my log?
A: The most common reasons are that log_min_duration_statement is still -1, the threshold is higher than your queries' duration, the configuration was not reloaded, or logging_collector is off while you are looking at a log file. Confirm with SHOW log_min_duration_statement; and SHOW logging_collector;.

Related Reading

• Debugging Slow PostgreSQL Queries: Use pg_stat_statements to rank your worst queries by total time.
• EXPLAIN Execution Plans in PostgreSQL: Interpret the plans that auto_explain captures.
• EXPLAIN ANALYZE in PostgreSQL: Read actual timings and BUFFERS output for a single query.
• PostgreSQL Performance Tuning: Configuration and query-level levers for a faster database.
• Why a PostgreSQL Index Is Not Used: Diagnose the sequential scans a slow query log surfaces.
• Database Monitoring Best Practices: The wider signal set to track alongside slow query logging.
• MySQL Slow Query Log: The equivalent feature and configuration in MySQL.