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

PlatformLog-based EXPLAINauto_explain
Amazon RDSYesYes (Recommended)
Azure DatabaseYesNo
Google Cloud SQLYesNo
Heroku PostgresYesNo
Self-managed VMYesYes (Recommended)
KubernetesNoNo
Other PaaSContact supportContact support

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:


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →