Announcing Auto Explain for Heroku Postgres
Change effective on 27 April 2023
Heroku Postgres now allows customers to use the auto_explain module to log query execution plans without having to run
EXPLAIN by hand. This module is an advanced PostgreSQL feature so use with caution. Enabling
auto_explain can cause performance impacts and a significant increase in log volume.
Customers on Standard, Premium, Private, and Shield Heroku Postgres plans can enable
pg:settings in the Heroku CLI. This command loads the
auto_explain module for all future Heroku Postgres connections. Existing connections must be reestablished before
auto_explain logging occurs.
$ heroku pg:settings:auto-explain on -a example-app
auto_explain is enabled, customers can configure a minimum log duration in milliseconds with the
log_min_duration setting. Setting a log duration of
-1 disables all logging, while setting a log duration of
0 logs all executed queries.
$ heroku pg:settings:auto-explain:log-min-duration 200 -a example-app
You can use the
log-analyze setting to run
EXPLAIN ANALYZE on all queries regardless if they’re logged or not. This setting can have a significant performance impact on your database so use with caution.
$ heroku pg:settings:auto-explain:log-analyze on -a example-app
These additional settings provide more information in your logs:
log-buffers- This setting is equivalent to calling
EXPLAIN BUFFERSand can only be used with
log-nested-statements- Nested statements are included in the execution plan’s log.
log-triggers- Includes trigger execution statistics in the execution plan’s logs.
log-verbose- This setting is equivalent to calling
Visit the Dev Center for more information on the PGSettings article.