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();Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →