To use the advanced
auto_explain extension in PostgreSQL 16, you should first load it into the server. This can be done by adding
auto_explain to either
session_preload_libraries or
shared_preload_libraries in the
postgresql.conf file. This setup allows you to track slow queries as they occur.
The
auto_explain module has several configurable parameters:
auto_explain.log_min_duration: Sets the minimum execution time for a statement to have its plan logged.auto_explain.log_analyze: Enables the logging of EXPLAIN ANALYZE output.auto_explain.log_buffers and auto_explain.log_wal: Control the logging of buffer and WAL usage statistics.auto_explain.log_timing: Toggles the logging of per-node timing information.auto_explain.log_triggers: Includes trigger execution statistics in logs.auto_explain.log_verbose: Enables verbose output.auto_explain.log_settings: Logs information about modified configuration options.auto_explain.log_format: Sets the output format (text, xml, json, yaml).auto_explain.log_level: Determines the log level for the query plan.auto_explain.log_nested_statements: Controls whether nested statements are logged.auto_explain.sample_rate: Sets the fraction of statements to explain in each session.
For example, to log every query with its execution plan, you can set
auto_explain.log_min_duration to
0 and enable
auto_explain.log_analyze.
Remember, enabling some of these features, especially
auto_explain.log_analyze, can impact performance due to the overhead of collecting detailed statistics.
For detailed information and examples, please refer to the PostgreSQL 16 documentation on
auto_explain.
