Step 1: Check for auto_explain

The recommended Automated EXPLAIN configuration for CloudNativePG (CNPG) 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 is already enabled

First, to check if auto_explain is already enabled, run the following using a superuser SQL prompt (for example, via kubectl cnpg psql my-cluster):

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

Enabling auto_explain requires a Postgres restart, since it changes shared_preload_libraries. Unlike most other platforms in these docs, CNPG restarts the cluster for you automatically by default: with primaryUpdateStrategy: unsupervised (the default), the operator restarts replicas first, then performs a switchover and restarts the former primary. No manual restart step is needed.

If this cluster uses primaryUpdateStrategy: supervised, you’ll need to approve the primary’s restart yourself with kubectl cnpg restart my-cluster after applying the parameter change.

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: