Collect Postgres EXPLAIN plans using log-based EXPLAIN

The log-based EXPLAIN mechanism is an alternative to the auto_explain module for EXPLAIN plan collection. We generally recommend using auto_explain to collect EXPLAIN plans automatically, but not all environments support the auto_explain module (bundled with Postgres), so we offer an alternative mechanism.

How log-based EXPLAIN works

This method relies on Postgres logging slow queries, based on the log_min_duration_statement setting.

For example, when we have configured log_min_duration_statement = 1000, we will get output like the following for all queries slower than 1,000 ms:

LOG: duration: 3205.800 ms execute a2: SELECT "servers".* FROM "servers" WHERE "servers"."id" = 1 LIMIT 2

When the pganalyze collector sees these events, it runs EXPLAIN automatically, so you get the EXPLAIN plan for each slow query, together with the log event.

Note that log-based EXPLAIN method has the following limitations:

• The collector only runs EXPLAIN, not EXPLAIN ANALYZE (to avoid any performance impact)
  • execution metrics, such as I/O time and Buffer counts are not captured
• The pganalyze monitoring user needs to have sufficient permissions to run EXPLAIN on the query
• Plans may sometimes be different from the executed plans (e.g., due to session-specific configuration settings)
• Plans for some queries may not be obtainable (e.g., due to use of temporary tables or search_path differences)

In spite of this, we find log-based Automated EXPLAIN provides valuable insights into database performance.

Setup

Follow the setup instructions here:

• Log-based Automated EXPLAIN (all other platforms)