auto_explain
auto_explain
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_show_plans pg_store_plans pg_stat_statements pg_hint_plan plprofiler pg_stat_monitor pg_qualstats pg_track_settings |
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 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;Last updated on