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
- auto_explain.log_format
- json
- auto_explain.log_min_duration
- 1000
Summary of recommended changes
- 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
Heroku allows users to modify several of these settings, but not all of them.
Notably, you cannot update the auto_explain.log_format setting with Heroku
Postgres currently.
Text-format auto_explain output is supported on a best-effort basis, so you may
see errors for some EXPLAIN plans.
You can check out the Heroku documentation
for how to update these settings.
The following is an example of updating log_min_duration:
heroku pg:settings:auto-explain:log-min-duration [database-addon-name] 1000 -a [your-app-name]Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →