Set up automatic EXPLAIN plan collection

EXPLAIN plans are essential to understanding why exactly a query is slow. SQL is a declarative language, but to get query results, the Postgres executor performs a series of steps determined by the query planner. The EXPLAIN command gives you access to those query plans, allowing you to diagnose and optimize performance problems. The pganalyze Automated EXPLAIN feature gives you easy access to these query plans, and adds EXPLAIN insights to help you interpret the raw plan information.

Pre-requisites

• Automatic explain plan collection requires Log Insights to be set up successfully

Overview

pganalyze supports two different mechanisms for collecting EXPLAIN plans automatically:

• auto_explain
  Postgres collects EXPLAIN (or EXPLAIN ANALYZE) data as part of query processing, based on auto_explain.log_min_duration, reflecting the actual plan that was used
• Log-based EXPLAIN
  pganalyze collector runs EXPLAIN (without ANALYZE) on all queries logged based on log_min_duration_statement, after the query has completed

Generally we recommend utilizing auto_explain where available, as it provides higher data quality.

Log-based EXPLAIN is not guaranteed to show the same plan that was executed, and cannot show execution metrics like I/O timing or buffer usage.

Supported platforms

* Supabase only supports configuring auto_explain settings at the role level (rather than server-wide). Therefore, you will need to configure each application role that you want auto_explain output for individually. See the Supabase setup guide for details.

Neon does not currently provide a way to collect EXPLAIN plans (neither auto_explain nor log-based EXPLAIN are supported on Neon at this time).

We are constantly evaluating new platform to support - please reach out if you’re missing an integration, to help us prioritize.

Setup

See the following pages for details on how to install pganalyze Automated EXPLAIN:

• Amazon RDS & Amazon Aurora
• Azure Database for PostgreSQL
• Google Cloud SQL and AlloyDB
• Heroku Postgres
• Crunchy Bridge
• Aiven
• Supabase
• Self-Managed Server