NEW: Paste your query and schema data and we give you the best index. Launch pganalyze Index Advisor

Step 2: Enable auto_explain

To enable auto_explain, you will need to add the auto_explain module to the shared_preload_libraries configuration setting.

For example, if the current value is pg_stat_statements, you can set the new value to pg_stat_statements, auto_explain:

ALTER SYSTEM SET shared_preload_libraries TO pg_stat_statements, auto_explain;

Since changes to shared_preload_libraries only take effect at server start, after you apply these settings, you will need to restart Postgres:

sudo systemctl restart postgresql

After the reboot completes, verify that shared_preload_libraries now includes auto_explain and that the setting change does not require a restart (pending_restart should be f):

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

If auto_explain is enabled and no longer requires a restart, you can proceed to review our recommended auto_explain configuration settings:

Proceed 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 →