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 auto_explain via 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

After 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 BUFFERS and can only be used with pg:settings:auto-explain:log-analyze turned on.
  • 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 EXPLAIN VERBOSE.

Visit the Dev Center for more information on the PGSettings article.