auto_explain
Provides a means for logging execution plans of slow statements automatically
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
auto_explain | - | STAT | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 6980 | auto_explain | No | Yes | Yes | No | No | No | - |
| Related | pg_show_plans pg_store_plans pg_stat_statements pg_hint_plan plprofiler pg_stat_monitor pg_qualstats pg_track_settings |
|---|
Version
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|
| - | - | - | - | - |
Install
Note: This is a built-in contrib extension of PostgreSQL
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;
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.