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.
This method relies on Postgres logging slow queries, based on the
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
In spite of this, we find log-based Automated EXPLAIN provides valuable insights into database performance.
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 →