auto_explain — Automatic Slow-Query Plan Capture

If Query Advisor is the page you spent the most time looking at when evaluating MonPG, auto_explain is the extension that determines whether that page is useful or empty. Without auto_explain, all we have is aggregate stats — calls, mean time, total time. We can tell you which queries are slow but not why. With auto_explain, every query that crosses your duration threshold logs its full plan, and we parse it to find the bottleneck node.

What good captures look like

The settings we recommend, and why:

shared_preload_libraries = '...,auto_explain'  # in addition to pg_stat_statements
auto_explain.log_min_duration = 5000           # capture queries slower than 5s
auto_explain.log_analyze = on                  # include actual row counts + timing
auto_explain.log_buffers = on                  # buffer hit/read stats per node
auto_explain.log_format = json                 # parsable
auto_explain.log_nested_statements = on        # function bodies + triggers

The 5-second threshold is what we suggest as a starting point. It's high enough that you won't drown in plans, low enough to catch meaningful regressions. Once you've been running for a week and have a feel for your normal slow-query distribution, dial it down. Some teams run at 1000ms; a few brave ones run at 100ms.

log_format = json matters because non-JSON formats need post-processing to be useful. JSON is what our parser expects.

Installing

auto_explain is SPL-bound and restart-bound. We won't do this for you, but here's how it goes per provider.

On AWS RDS or Aurora: edit your Parameter Group, add auto_explain to shared_preload_libraries (it's already a comma-separated list — append, don't replace), set the four auto_explain.log_* parameters above, save, then reboot the instance from the RDS console. The reboot is required for SPL changes to take effect; the auto_explain parameters themselves are reload-only after that.

On Azure Flex: same shape. Server parameters blade → add auto_explain to shared_preload_libraries → set the auto_explain parameters → save → restart the server.

On Cloud SQL: easier. Set the flag cloudsql.enable_auto_explain=on. Cloud SQL handles SPL for you. The instance restarts automatically. Then set the four auto_explain.log_* parameters as additional flags.

On Heroku and Aiven: not available. Heroku doesn't expose SPL at all. Aiven's catalog doesn't include auto_explain.

On self-hosted: edit postgresql.conf, restart, done.

What changes after install

Slow queries in your workload start producing plan capture lines in your Postgres log. MonPG's collector reads them, normalizes the JSON, identifies the dominant cost node (Seq Scan / Hash Join spilling to disk / Sort with insufficient work_mem / etc.), and surfaces it on Query Advisor with a recommendation. The first plans usually show up within a few minutes, depending on how often your slow queries actually run.

If Query Advisor stays empty after a day, your log_min_duration is probably set too high for your workload — nothing is crossing it. Try lowering it.