The recommended Automated EXPLAIN configuration for self-managed systems is to use the Postgres auto_explain contrib module if available. This ensures accurate and detailed plan information, including runtime information (as gathered by EXPLAIN ANALYZE). With our recommended configuration (presented in a later step), performance overhead is minimal.

Check if auto_explain is available

To check if auto_explain is available, connect as a Postgres superuser and run the following command:

LOAD 'auto_explain';

If there is an error, please contact us to troubleshoot. Otherwise, you can proceed to enable the module.

Check if auto_explain already enabled

Next, to check if auto_explain is already enabled on your system, run the following query as a Postgres superuser:

SELECT setting, pending_restart FROM pg_settings WHERE name = 'shared_preload_libraries';

If auto_explain is included in the shared_preload_libraries value and pending_restart is false, the module is already enabled.

If auto_explain is not enabled

A Postgres restart will be required to enable the auto_explain functionality. If a restart is not acceptable for this server right now, you can instead try our alternate log-based EXPLAIN mechanism. You can switch to the auto_explain mechanism at any time in the future by turning off the enable_log_explain collector setting and following the auto_explain setup instructions here.

If auto_explain is already enabled

If auto_explain is already enabled, you can skip the next step and proceed to review our recommended auto_explain configuration settings: