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.
- Automatic explain plan collection requires Log Insights to be set up successfully
pganalyze supports two different mechanisms for collecting EXPLAIN plans automatically:
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.
|Amazon RDS and Amazon Aurora||Yes||Yes (Recommended)|
|Azure Database for PostgreSQL||Yes||No|
|Google Cloud SQL and AlloyDB||Yes||Yes (Recommended)|
|Crunchy Bridge||Yes||Yes (Recommended)|
|Self-managed VM||Yes||Yes (Recommended)|
|Other PaaS||Contact support||Contact support|
We are constantly evaluating new platform to support - please reach out if you're missing an integration, to help us prioritize.
See the following pages for details on how to install pganalyze Automated EXPLAIN:
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →