Join us for our webinar on June 18th, 9:30am Pacific Time: Optimizing slow queries with EXPLAIN to fix bad query plans - register now.

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:


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