Step 3: Review auto_explain settings

There are a number of auto_explain settings that allow you to configure which queries will generate EXPLAIN plans. You can read the full Postgres documentation about these here.

Because Supabase doesn’t allow ALTER SYSTEM, these settings need to be applied per role, using ALTER ROLE ... SET, for every role your application connects as (the postgres role alone is not enough — queries only get an EXPLAIN plan logged if the connecting role has these settings applied). Run the following as the postgres user, once for each application role (replace your_app_role with the actual role name).

The right configuration will depend on your server and workload, but we’ve found the following is a good starting point:

ALTER ROLE "your_app_role" SET auto_explain.log_format TO 'json';
ALTER ROLE "your_app_role" SET auto_explain.log_min_duration TO '1000';

ALTER ROLE "your_app_role" SET auto_explain.log_analyze TO 'on';
ALTER ROLE "your_app_role" SET auto_explain.log_buffers TO 'on';
ALTER ROLE "your_app_role" SET auto_explain.log_timing TO 'off';
ALTER ROLE "your_app_role" SET auto_explain.log_triggers TO 'on';
ALTER ROLE "your_app_role" SET auto_explain.log_verbose TO 'on';
ALTER ROLE "your_app_role" SET auto_explain.log_nested_statements TO 'on';
ALTER ROLE "your_app_role" SET auto_explain.sample_rate TO '1';

If you connect through the Supavisor pooler with a role name like your_app_role.your-project-ref, apply the settings to the underlying role name (your_app_role), not the pooler-specific form.

These settings only take effect for new connections made after the ALTER ROLE statements run, so existing, long-lived connections (e.g. from a connection pool) will need to reconnect before they start producing EXPLAIN plans.