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:

Setting	Recommended
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 →