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

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
• Self-Managed Server