Join us for our webinar on June 18th, 9:30am Pacific Time: Optimizing slow queries with EXPLAIN to fix bad query plans - register now.

Step 1: Check for auto_explain

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 already enabled

First, to check if auto_explain is already enabled on your system, run the following query as a server admin user:

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.

Continue to Step 2: Enable auto_explain

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:

Skip to Step 3: Review auto_explain settings

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →