log_min_duration_statement Not Configured

Scenario

A DBA is asked to identify which queries are causing performance complaints. pg_stat_statements shows aggregate statistics but no plan details, and slow queries were never logged. log_min_duration_statement = -1 (the default — logging disabled). No slow query log exists. The fix is to set it to 1000ms to capture queries taking over one second, and optionally load auto_explain to capture execution plans for slow queries without running EXPLAIN manually.

How to Identify

Conditions:

• SHOW log_min_duration_statement returns -1
• PostgreSQL log contains no slow query entries despite performance complaints
• pg_stat_statements.total_exec_time / calls shows high averages but no log evidence
• DBA cannot reproduce the slow query manually (it was a production burst)
• No auto_explain loaded — plans are unavailable for past slow queries

Analysis Steps

-- Check current slow query logging setting (-1 = off):
SHOW log_min_duration_statement;

-- Check all logging-related settings together:
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'log_min_duration_statement',
    'log_duration',
    'log_statement',
    'logging_collector',
    'log_directory',
    'log_filename'
)
ORDER BY name;
-- context='superuser' → ALTER SYSTEM + reload; no restart needed

-- Check pg_stat_statements for candidates > 1 second average:
SELECT
    left(query, 100)                                    AS query,
    calls,
    round((total_exec_time / calls)::numeric, 1)        AS avg_ms,
    round(max_exec_time::numeric, 0)                    AS max_ms
FROM pg_stat_statements
WHERE total_exec_time / calls > 1000
ORDER BY avg_ms DESC
LIMIT 20;
-- These are the queries that should have been logged but weren't

-- Verify auto_explain is loaded (module, not extension):
SHOW shared_preload_libraries;

Pitfalls

• Setting log_min_duration_statement = 0 logs every query including sub-millisecond ones. This floods the log, fills disk, and can itself cause performance degradation from log I/O.
• log_duration = on logs the duration of all statements unconditionally — it is not the same as log_min_duration_statement. Do not use log_duration for selective slow query capture.
• auto_explain.log_min_duration is a separate setting from log_min_duration_statement. Setting one does not configure the other. Both must be set independently.
• log_min_duration_statement is reload-safe — it takes effect after SELECT pg_reload_conf(). No restart is needed.
• auto_explain must be added to shared_preload_libraries, which requires a restart. Plan logging is not available immediately if auto_explain was not pre-loaded.
• auto_explain.log_nested_statements = off by default — plans inside PL/pgSQL functions are not captured unless explicitly enabled.

Resolution Approach

Set log_min_duration_statement = 1000 to capture queries exceeding one second. Reload configuration (no restart needed). For plan capture, add auto_explain to shared_preload_libraries and set auto_explain.log_min_duration = 1000. Restart once for auto_explain to activate. Monitor log volume — increase threshold if log floods.