auto_explain

Provides a means for logging execution plans of slow statements automatically

auto_explain : Provides a means for logging execution plans of slow statements automatically

Overview

ID	Extension	Package	Version	Category	License	Language
6980	auto_explain	auto_explain	`-`	STAT	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--sL---	No	Yes	Yes	No	no	no

Relationships
See Also	pg_stat_plans pg_show_plans explain_ui pg_stat_statements pg_store_plans pg_overexplain pg_qualstats online_advisor pg_stat_monitor pg_profile

Packages

PG18	PG17	PG16	PG15	PG14
-	-	-	-	-

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'auto_explain';

This extension does not need CREATE EXTENSION DDL command

Usage

auto_explain: automatically log slow query plans

auto_explain automatically logs execution plans of slow statements, eliminating the need to manually run EXPLAIN. Plans are sent to the PostgreSQL log.

Quick Start

-- Load per-session
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';
SET auto_explain.log_analyze = true;

Or in postgresql.conf for all sessions:

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'

Configuration Parameters

Parameter	Default	Description
`auto_explain.log_min_duration`	`-1`	Minimum duration to log (ms). `0` = all, `-1` = disabled
`auto_explain.log_analyze`	`off`	Use `EXPLAIN ANALYZE` (includes actual timing)
`auto_explain.log_buffers`	`off`	Include buffer usage statistics
`auto_explain.log_wal`	`off`	Include WAL usage statistics
`auto_explain.log_timing`	`on`	Include per-node timing (disable to reduce overhead)
`auto_explain.log_triggers`	`off`	Include trigger execution statistics
`auto_explain.log_verbose`	`off`	Include verbose output
`auto_explain.log_settings`	`off`	Log modified planner-relevant settings
`auto_explain.log_format`	`text`	Format: `text`, `xml`, `json`, `yaml`
`auto_explain.log_level`	`LOG`	Log level for output
`auto_explain.log_nested_statements`	`off`	Log plans for statements inside functions
`auto_explain.log_parameter_max_length`	`-1`	Parameter logging: `-1` = full, `0` = none
`auto_explain.sample_rate`	`1`	Fraction of statements to explain (0.0 to 1.0)

Example Log Output

LOG:  duration: 3.651 ms  plan:
  Query Text: SELECT count(*) FROM pg_class, pg_index
              WHERE oid = indrelid AND indisunique;
  Aggregate  (cost=16.79..16.80 rows=1 width=0)
             (actual time=3.626..3.627 rows=1 loops=1)
    ->  Hash Join  (cost=4.17..16.55 rows=92 width=0)
                   (actual time=3.349..3.594 rows=92 loops=1)

Performance Tip

When using log_analyze, disable log_timing if you only need row counts:

SET auto_explain.log_analyze = true;
SET auto_explain.log_timing = off;