When troubleshooting slow queries in PostgreSQL, one of the most powerful tools at your disposal is EXPLAIN (ANALYZE). It helps you understand how the query planner executes your SQL statement, including the cost estimates, join strategies, and execution times. However, manually running EXPLAIN ANALYZE for every query isn’t practical in a production environment. That’s where the auto_explain extension steps in.
auto_explain is a built-in PostgreSQL extension that automatically logs execution plans for queries that exceed a specified execution time threshold. This makes it an invaluable tool for detecting slow queries and understanding performance bottlenecks in real-time.
What is auto_explain?
auto_explain is an extension that logs the execution plan of slow SQL statements directly into PostgreSQL logs. Instead of manually analyzing queries one by one, it automatically captures plans for those that exceed a configurable duration, such as 200 ms.
It works transparently—you don’t have to modify your queries or applications. Once enabled, PostgreSQL automatically logs detailed information for slow-running queries, including buffer usage and actual execution times.
How to Enable auto_explain
Since auto_explain is a preload library, you need to modify the PostgreSQL configuration file (postgresql.conf) to enable it.
shared_preload_libraries = 'auto_explain'
Note: This change requires a PostgreSQL restart.
After enabling it, you can configure the behavior of auto_explain with the following parameters:
auto_explain.log_min_duration = '200ms' # Log queries taking longer than 200ms
auto_explain.log_analyze = on # Include actual execution time
auto_explain.log_buffers = on # Show buffer usage
auto_explain.log_verbose = on # Include verbose plan details
auto_explain.log_nested_statements = on # Log queries inside functions
You can also apply these settings dynamically for a specific session:
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = on;
Where to Find the Output
auto_explain outputs the query plan details into PostgreSQL’s server log (postgresql.log). You can configure the log location and rotation behavior with standard PostgreSQL logging parameters such as log_directory, log_filename, and logging_collector.
Here’s an example of a log entry:
LOG: duration: 284.722 ms plan:
Query Text: SELECT * FROM sale_order WHERE amount_total > 1000;
Seq Scan on sale_order (cost=0.00..58.00 rows=10 width=120) (actual time=0.030..284.719 rows=8 loops=1)
Filter: (amount_total > 1000)
Rows Removed by Filter: 15000
This shows both the planner’s expectations (cost) and the actual execution times, giving you a clear view of performance discrepancies.
Key Benefits
- Automated Query Analysis – Identify slow queries without manually running EXPLAIN.
- Production-Safe – Enables passive performance monitoring without changing application code.
- Detailed Insights – Captures execution plans, buffer usage, and nested statements.
Best Practices
- Use auto_explain.log_min_duration carefully – setting it too low (e.g., 0ms) can overwhelm logs.
- Enable it selectively in production (per session or specific users) to minimize overhead.
- Combine it with pg_stat_statements for deeper analysis of frequently executed queries.
- Regularly review the logs to identify and fix slow queries.
The auto_explain extension is an essential performance tuning feature in PostgreSQL. It automates query analysis by capturing execution plans for slow queries, allowing database administrators and developers to focus on optimization rather than manual diagnostics. Whether you're tuning complex joins, identifying missing indexes, or simply keeping an eye on production performance, auto_explain is a must-have tool in your PostgreSQL toolbox.