Notice: We've updated our Privacy Policy, effective November 14, 2024.
Logo

Step 3: Review auto_explain settings

There are a number of auto_explain settings that allow you to configure which queries will generate EXPLAIN plans. You can read the full Postgres documentation about these here. The right configuration will depend on your server and workload, but we've found the following is a good starting point:

SettingRecommended
auto_explain.log_format
json
auto_explain.log_min_duration
1000
auto_explain.log_analyze
on
auto_explain.log_buffers
on
auto_explain.log_timing
off
auto_explain.log_triggers
on
auto_explain.log_verbose
on
auto_explain.log_nested_statements
on
auto_explain.sample_rate
1

Summary of required changes

ALTER SYSTEM SET auto_explain.log_format TO 'json';
ALTER SYSTEM SET auto_explain.log_min_duration TO '1000';

Summary of recommended changes

ALTER SYSTEM SET auto_explain.log_analyze TO 'on';
ALTER SYSTEM SET auto_explain.log_buffers TO 'on';
ALTER SYSTEM SET auto_explain.log_timing TO 'off';
ALTER SYSTEM SET auto_explain.log_triggers TO 'on';
ALTER SYSTEM SET auto_explain.log_verbose TO 'on';
ALTER SYSTEM SET auto_explain.log_nested_statements TO 'on';
ALTER SYSTEM SET auto_explain.sample_rate TO '1';

Run the above commands as a Postgres superuser. Then, to apply these settings, run

SELECT pg_reload_conf();
Continue to Step 4: Test and verify
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →