Log execution plans of slow queries with auto_explain

Command

-- 1) Per session: effective immediately, no restart (superuser)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;
SET auto_explain.log_timing = off;
SET auto_explain.log_nested_statements = on;

-- 2) Persistent: loaded by new sessions, a reload is enough.
--    The module must be LOADed in this session before ALTER SYSTEM accepts auto_explain.* parameters.
LOAD 'auto_explain';
ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '2s';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_timing = off;
ALTER SYSTEM SET auto_explain.sample_rate = 0.1;
SELECT pg_reload_conf();

What it does

Writes the execution plan of queries exceeding the threshold to the server log (with actual row counts and buffer data when log_analyze is on); captures the plan produced with the application's real parameters.

When to use it

When a query is slow only from the application or with specific parameters and cannot be reproduced with a manual EXPLAIN; to catch intermittent plan changes.

Caution

log_analyze = on adds instrumentation overhead to every sampled query, including those below the threshold; log_timing = off and sample_rate < 1 reduce it. log_min_duration = 0 floods the log in production. ALTER SYSTEM SET session_preload_libraries overwrites the existing value; check SHOW session_preload_libraries first. session_preload_libraries does not affect existing sessions; loading it in every process requires shared_preload_libraries plus a restart.