auto_explain

The auto_explain module provides a way to automatically log the execution plans of slow statements, without the need to manually run EXPLAIN. This is useful for tracking unoptimized queries in large applications.

This module does not provide any SQL-accessible functions. To use it, simply load it into the server. You can load it into an individual session:

LOAD 'auto_explain';

(You must be a superuser to do this.) A more typical usage is to preload it into some or all sessions by including auto_explain in the session_preload_libraries or shared_preload_libraries parameter in postgresql.conf. You can then track unexpectedly slow queries regardless of when they occur. Of course, this comes at the cost of some additional overhead.

1. Configuration Parameters

There are several configuration parameters that control the behavior of auto_explain. Note that the default behavior is to do nothing, so you must set at least auto_explain.log_min_duration if you want any results.

auto_explain.log_min_duration (integer)

auto_explain.log_min_duration is the minimum statement execution time (in milliseconds) that will cause the statement's plan to be logged. Setting this parameter to 0 logs all plans, while setting it to -1 (the default) disables plan logging.

For example, if you set it to 250ms, all statements that run for 250ms or longer will be logged.

Only superusers can change this setting.

auto_explain.log_analyze (boolean)

When an execution plan is logged, auto_explain.log_analyze causes EXPLAIN ANALYZE output (rather than just EXPLAIN output) to be printed. This parameter is off by default. Only superusers can change this setting.

Note: When this parameter is on, it causes per-plan-node timing for all executed statements, regardless of whether they run long enough to be logged. This may have a severely negative impact on performance.

auto_explain.log_buffers (boolean)

When an execution plan is logged, auto_explain.log_buffers controls whether buffer usage statistics are printed; it is equivalent to EXPLAIN's BUFFERS option. This parameter has no effect unless auto_explain.log_analyze is also set. This parameter is off by default. Only superusers can change this setting.

auto_explain.log_wal (boolean)

auto_explain.log_wal controls whether WAL usage statistics are printed when an execution plan is logged; it is equivalent to EXPLAIN's WAL option. This parameter has no effect unless auto_explain.log_analyze is enabled. This parameter is off by default. Only superusers can change this setting.

auto_explain.log_timing (boolean)

When an execution plan is logged, auto_explain.log_timing controls whether per-node timing information is printed; it is equivalent to EXPLAIN's TIMING option. The overhead of repeatedly reading the system clock may significantly slow down queries on some systems, so disabling this parameter can be helpful when only actual row counts are needed rather than exact timings. This parameter only takes effect when auto_explain.log_analyze is also enabled. This parameter is on by default. Only superusers can change this setting.

auto_explain.log_triggers (boolean)

When an execution plan is logged, auto_explain.log_triggers causes trigger execution statistics to be included. This parameter only takes effect when auto_explain.log_analyze is also enabled. This parameter is off by default. Only superusers can change this setting.

auto_explain.log_verbose (boolean)

When an execution plan is logged, auto_explain.log_verbose controls whether verbose details are printed; it is equivalent to EXPLAIN's VERBOSE option. This parameter is off by default. Only superusers can change this setting.

auto_explain.log_settings (boolean)

auto_explain.log_settings controls whether information about modified configuration options is printed when an execution plan is logged. The output only includes options that affect query planning and whose values differ from the built-in defaults. This parameter is off by default. Only superusers can change this setting.

auto_explain.log_format (enum)

auto_explain.log_format selects the EXPLAIN output format to use. Allowed values are text, xml, json, and yaml. The default is text. Only superusers can change this setting.

auto_explain.log_level (enum)

auto_explain.log_level selects the log level at which auto_explain will log query plans. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, and LOG.

The default is LOG. Only superusers can change this setting.

auto_explain.log_nested_statements (boolean)

auto_explain.log_nested_statements causes nested statements (statements executed within a function) to be considered for logging. When it is off, only top-level query plans are logged. This parameter is off by default. Only superusers can change this setting.

auto_explain.sample_rate (real)

auto_explain.sample_rate causes auto_explain to only explain a fraction of the statements in each session. The default value is 1, meaning all queries are explained. In the case of nested statements, either all statements are explained or none are. Only superusers can change this setting.

In typical usage, these parameters are set in postgresql.conf, though superusers can change them in their own sessions at any time. Typical usage might be:

postgresql.conf

session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '3s'

4.2. Example

test=## SET auto_explain.log_min_duration = 0;

SET

test=## SET auto_explain.log_analyze = true;

SET

test=## SELECT count(*)

test-## FROM pg_class, pg_index

test-## WHERE oid = indrelid AND indisunique;

count

-------

161

(1 row)