The recommended Automated EXPLAIN configuration for Google Cloud SQL and AlloyDB is to use the Postgres auto_explain contrib module. 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 user with the cloudsqlsuperuser / alloydbsuperuser role:

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: